Oracle Update操作的优化一例

客户的每小时redolog日志量大,配合AWR和LOGMINER检查发现是由一条update语句引起。这条语句大概每小时执行80次左右,不仅产生了大量的重做日志,而且逻辑读也很高。

语句类似update tb_test_log set object_id=1 where owner='SYS',是对表tb_test_log按一定的频率,把满足条件owner='SYS'的记录中的object_id修改为1,而且满足条件的记录占了整个表的一半左右。但实际上在每次更新时,满足条件owner='SYS'的记录中绝大部分object_id已经是1.

以下尝试优化:
DB Version:12.1.0.2.0
OS:CentOS 6.6

#建测试表
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;

#查看测试表的大小,大概100MB
select bytes from dba_segments where segment_name=upper('tb_test_log');
/*
BYTES
109051904
*/

#满足条件owner='SYS'的记录大概占了46%
select count(decode(owner,'SYS',1,null))/count(1) from tb_test_log;
/*
0.461732733062479
*/

#优化前SQL
update tb_test_log set object_id=1 where owner='SYS';

#新建会话统计数据记录表,用于后面的重做日志和逻辑读的计算
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 t_stat_temp';
  else
    execute immediate 'create table t_stat_temp(snap_date date,name varchar2(100),value int)';
  end if;
end;


会话1:
#查看会话1的会话ID
select sid from v$mystat where rownum<=1;
/*
SID
35
*/

会话2:
#插入会话1当前的重做日志和逻辑读的统计数据
insert into t_stat_temp
select sysdate,a.name,b.value
from v$statname a,v$sesstat b
where a.statistic#=b.statistic# and b.sid=35
and a.name in ('redo size','session logical reads');
commit;
#DIFF是会话1产生的重做日志和逻辑读的量
select name,min(value) begin_value,max(value) end_value,max(value)-min(value) diff
from (select * from t_stat_temp order by snap_date desc)
where rownum<=4
group by name;
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    736    736    0
session logical reads    1463    1463    0
*/

#后续会话2都是执行上面相同的插入和查询语句,省略语句,只显示查询结果

会话1:
#会话1执行优化前的更新语句
update tb_test_log set object_id=1 where owner='SYS';
commit;
会话2:
#会话1此次执行更新语句后,redo size产生168611404,session logical reads消耗1057915
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    736    168612140    168611404
session logical reads    1463    1059378    1057915
*/

会话1:
#会话1执行优化前的更新语句
update tb_test_log set object_id=1 where owner='SYS';
commit;
会话2:
#会话1此次执行更新语句后,redo size产生108994644,session logical reads消耗718610
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    168612140    277606784    108994644
session logical reads    1059378    1777988    718610
*/

会话1:
#会话1执行优化前的更新语句
update tb_test_log set object_id=1 where owner='SYS';
commit;
会话2:
#会话1此次执行更新语句后,redo size产生112071424,session logical reads消耗731397
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    277606784    389678208    112071424
session logical reads    1777988    2509385    731397
*/

会话1:
#会话1执行优化前的更新语句
update tb_test_log set object_id=1 where owner='SYS';
commit;
会话2:
#会话1此次执行更新语句后,redo size产生131894432,session logical reads消耗759343
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    389678208    521572640    131894432
session logical reads    2509385    3268728    759343
*/

会话1:
#会话1执行优化前的更新语句
update tb_test_log set object_id=1 where owner='SYS';
commit;
会话2:
#会话1此次执行更新语句后,redo size产生133580596,session logical reads消耗762190
/*
NAME    BEGIN_VALUE    END_VALUE    DIFF
redo size    521572640    655153236    133580596
session logical reads    3268728    4030918    762190
*/
小结:优化前,每次更新表中46%左右的数据,重做日志产生量大概是100MB+,逻辑读大概是700000+。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/da43bc7607f3980d5f4f38d2d96c8777.html