oracle update操作的优化实例分析

56次阅读
没有评论

共计 5074 个字符,预计需要花费 13 分钟才能阅读完成。

本篇内容主要讲解“oracle update 操作的优化实例分析”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让丸趣 TV 小编来带大家学习“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
/*
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 +。

到此,相信大家对“oracle update 操作的优化实例分析”有了更深的了解,不妨来实际操作一番吧!这里是丸趣 TV 网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计5074字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)