oracle update操作的優化實例分析

131次閱讀

共計 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;

# 查看測試表的大小，大概 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 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完