Oracle Update操作的优化一例

110次阅读

共计 5305 个字符，预计需要花费 14 分钟才能阅读完成。

客户的每小时 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;

# 查看测试表的大小，大概 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+。

优化 1：
根据 SQL 逻辑，增加过滤条件 object_id!=1，原语句逻辑不变。
会话 1：
# 会话 1 执行优化 1 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and object_id!=1;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 827112，session logical reads 消耗 22835
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655153236 655980348 827112
session logical reads 4030918 4053753 22835
*/

会话 1：
# 会话 1 执行优化 1 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and object_id!=1;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 340，session logical reads 消耗 12413
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655980348 655980688 340
session logical reads 4053753 4066166 12413
*/

会话 1：
# 会话 1 执行优化 1 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and object_id!=1;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 340，session logical reads 消耗 12413
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655980688 655981028 340
session logical reads 4066166 4078579 12413
*/
小结：优化 1，每次基本上不更新表中数据，重做日志产生量大概是 300+, 逻辑读大概是 10000+。

优化 2：
根据 SQL 逻辑，增加过滤条件 decode(object_id,1,null,’1′)=’1’，并增加索引 tb_test_log(owner,decode(object_id,1,null,’1′))，原语句逻辑不变。
会话 3：
# 新建索引
create index idx_tb_test_log_01 on tb_test_log(owner,decode(object_id,1,null,’1′)) tablespace users;

会话 1：
# 会话 1 执行优化 2 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and decode(object_id,1,null,’1′)=’1′;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 384，session logical reads 消耗 11214
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655981028 655981412 384
session logical reads 4078579 4089793 11214
*/

会话 1：
# 会话 1 执行优化 2 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and decode(object_id,1,null,’1′)=’1′;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 384，session logical reads 消耗 6
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655981412 655981796 384
session logical reads 4089793 4089799 6
*/

会话 1：
# 会话 1 执行优化 2 的更新语句
update tb_test_log set object_id=1 where owner=’SYS’ and decode(object_id,1,null,’1′)=’1′;
commit;
会话 2：
# 会话 1 此次执行更新语句后，redo size 产生 384，session logical reads 消耗 5
/*
NAME BEGIN_VALUE END_VALUE DIFF
redo size 655981796 655982180 384
session logical reads 4089799 4089804 5
*/
小结：优化 2，每次基本上不更新表中数据，重做日志产生量大概是 300+, 逻辑读大概是 5 +。

总结：
1. 根据 SQL 逻辑，增加过滤条件 object_id!=1，原语句逻辑不变，大幅度降低了重做日志的产生量。
2. 根据 SQL 逻辑，增加过滤条件 decode(object_id,1,null,’1′)=’1’，并增加索引 tb_test_log(owner,decode(object_id,1,null,’1′))，原语句逻辑不变，大幅度降低了重做日志的产生量和逻辑读。
3. 类似问题的 DELETE 语句也可以从此方法中受益。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/136636.htm

正文完

星哥说事-微信公众号