Shulou(Shulou.com)05/31 Report--

This article mainly explains "the optimization example analysis of oracle update operation". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "oracle update operation optimization example analysis" it!

The customer has a large number of redolog logs per hour, which is caused by a update statement in conjunction with AWR and LOGMINER checks. This statement is executed about 80 times per hour, which not only produces a large number of redo logs, but also has a high logical read.

The statement is similar to update tb_test_log set object_id=1 where owner='SYS', which changes the object_id in the records satisfying the condition owner='SYS' to 1 according to a certain frequency of the table tb_test_log, and the records that meet the conditions account for about half of the whole table. But in fact, at the time of each update, most of the records that meet the condition owner='SYS' already have 1. 5 object_id.

The following attempts to optimize:

DB Version:12.1.0.2.0

OS:centos 6.6

# create a test table

Create table tb_test_log tablespace users as select * from dba_objects

Insert into tb_test_log select * from tb_test_log

Commit

Insert into tb_test_log select * from tb_test_log

Commit

Insert into tb_test_log select * from tb_test_log

Commit

# check the size of the test table, about 100MB

Select bytes from dba_segments where segment_name=upper ('tb_test_log')

/ *

BYTES

109051904

, /

# about 46% of the records met the criteria owner='SYS'

Select count (decode (owner,'SYS',1,null)) / count (1) from tb_test_log

/ *

0.461732733062479

, /

# SQL before optimization

Update tb_test_log set object_id=1 where owner='SYS'

# create a new session statistics record table for subsequent redo log and logical read calculation

Declare

V_count number

Begin

Select count (1) into v_count from dba_tables where table_name='T_STAT_TEMP'

If v_count=1 then

Execute immediate 'truncate table tact statue temp'

Else

Execute immediate 'create table t_stat_temp (snap_date date,name varchar2, value int)'

End if

End

Session 1:

# View session ID of session 1

Select sid from v$mystat where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.