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+。
最佳化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語句也可以從此方法中受益。
語句類似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+。
最佳化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語句也可以從此方法中受益。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30633755/viewspace-2127710/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle update操作的優化一例Oracle優化
- SQL SERVER的UPDATE操作比ORACLE快得多SQLServerOracle
- MySQL 常用的UPDATE操作MySql
- For Update操作分析——不同Oracle版本之間的差異研究Oracle
- MybatisPlus中的update操作MyBatis
- Oracle效能最佳化之最佳化排序操作（轉）Oracle排序
- MongoDB更新（update）操作MongoDB
- UPDATE操作和UNDO
- Oracle資料庫中Insert、Update、Delete操作速度Oracle資料庫delete
- Oracle中的for update 和 for update nowaitOracleAI
- 限制訪問表的FOR UPDATE操作
- Update操作對索引的影響索引
- MySQL的SQL語句最佳化一例MySql
- InnoDB update操作流程圖流程圖
- no_index最佳化sql一例IndexSQL
- ActiveRecord發update請求的原子操作
- 使用select監視update的操作
- oracle中update的細節Oracle
- 認識oracle的update更新Oracle
- Mysql update誤操作恢復MySql
- oracle select for updateOracle
- Oracle中select ... for update的用法Oracle
- （轉）資料庫oracle for update of和for update區別資料庫Oracle
- 記錄一次慘痛的“update”操作
- Oracle 恢復一例Oracle
- 充值平臺資料庫最佳化一例資料庫
- win8.1update快捷鍵操作大全
- MySQL update資料時InnoDB內部的操作流程MySql
- MySQL之資料的insert-delete-update操作MySqldelete
- Oracle效能診斷一例Oracle
- oracle SQL調整一例OracleSQL
- Oracle 日誌管理一例Oracle
- oracle update left join查詢Oracle
- oracle檢視可以update嗎Oracle
- oracle update語句的幾點寫法Oracle
- Oracle vs PostgreSQL，研發注意事項（3）- 事務回滾之UPDATE操作解析OracleSQL
- Merge into: Oracle中用一條SQL語句直接進行Insert/Update的操作(R1)OracleSQL
- JQuery 操作 radio 被坑一例jQuery