這兩天抓取高CPU SQL的腳本頻繁發(fā)出一個(gè)占用CPU較高的語句，語句如下：
SELECT T2.CONFLICT_ID, T2.LAST_UPD, T2.CREATED, T2.LAST_UPD_BY, T2.CREATED_BY, T2.MODIFICATION_NUM, T2.ROW_ID, T1.X_ATTRIB_03, T1.ROW_ID, T1.MODIFICATION_NUM, T1.CREATED_BY, T1.LAST_UPD_BY, T1.CREATED, T1.LAST_UPD, T1.CONFLICT_ID, T1.PAR_ROW_ID FROM USER.TABLE1 T1, USER.TABLE2 T2 WHERE T2.ROW_ID = T1.PAR_ROW_ID AND (T2.TYPE = 'TFile' AND T1.X_ATTRIB_11 = 'Y') AND (T2.PAR_ID = :1) ORDER BY T2.PAR_ID, T2.NAME |
執(zhí)行計(jì)劃如下：
[Execution Plan Information] ----------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ----------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 628327222.1 ---- | | | 13 | |001[001]TABLE ACCESS BY INDEX ROWID |TABLE1 | 1 | 76 | 2 | |002[002] NESTED LOOPS | | 10 | 1K| 13 | |003[003] TABLE ACCESS BY INDEX ROWID |TABLE2 | 491 | 46K| 12 | |004[004] INDEX FULL SCAN |TABLE2_F1 | 23K| | 302 | |005[003] INDEX RANGE SCAN |TABLE1_U1 | 1 | | 2 | ----------------------------------------------------------------------------- [Predicate Information] -------------------------------------------------------------------------------- 1 filter:"T1"."X_ATTRIB_11"='Y' 3 filter:"T2"."TYPE"='TFile' AND "T2"."PAR_ID"=:1 5 access:"T2"."ROW_ID"="T1"."PAR_ROW_ID" |
乍看這個(gè)語句，問題也不是很大，主要的消耗是在索引TABLE2_F1上的INDEX FULL SCAN。
索引TABLE2_F1是建在表USER.TABLE2(T2表)的NAME列上。
其實(shí)也比較好理解，當(dāng)有排序操作時(shí)，優(yōu)化器比較偏向使用INDEX FULL SCAN，因?yàn)镮NDEX FULL SCAN返回的數(shù)據(jù)是有序的，這樣就避免了排序
查看表USER.TABLE2(T2表)，發(fā)現(xiàn)列PAR_ID是有索引的(TABLE2_M1)。
在PL/SQL工具中，查看下執(zhí)行計(jì)劃，發(fā)現(xiàn)走的是TABLE2_M1索引(INDEX RANGE SCAN),但多了一個(gè)排序的操作(SORT ORDER BY )
因?yàn)榱蠵AR_ID采用的是綁定變量，決定試試代入不同的變量值，查看其執(zhí)行計(jì)劃是否有不同。
通過select PAR_ID,count(*) from USER.TABLE2 group by PAR_ID;
選取了兩個(gè)有代表性的值，一個(gè)值較多，一個(gè)值較少。
在TOAD中運(yùn)行語句，并通過加無效的HINT來區(qū)分（使SQL硬解析，避免bind peeking的影響），另開一個(gè)會(huì)話，抓取執(zhí)行計(jì)劃：
發(fā)現(xiàn)PAR_ID值較多的語句，走的是索引TABLE2_F1，執(zhí)行計(jì)劃如下：
HASH_VALUE VERSION_COUNT EXECUTIONS USERS_EXECUTING DISK_READS BUFFER_GETS ROWS_PROCESSED CPU_TIME ELAPSED_TIME ---------- ------------- ---------- --------------- ---------- ----------- -------------- ---------- ------------ 4211970596 1 1 0 0 77718 0 4150000 4070344 SQL_TEXT ----------------------------------------------------------------- SELECT /*+ value1 */ T2.CONFLICT_ID, T2.LAST_UPD, T2.CREATED, T2.LAST_UPD_BY, T2.CREATED_BY, T2.MODIFICATION_NUM, T2.ROW_ID, T1.X_ATTRIB_03, T1.ROW_ID, T1.MODIFICATION_NUM, T1.CREATED_BY, T1.LAST_UPD_BY, T1.CREATED, T1.LAST_UPD, T1.CONFLICT_ID, T1.PAR_ROW_ID FROM USER.TABLE1 T1, USER.TABLE2 T2 WHERE T2.ROW_ID = T1.PAR_ROW_ID AND (T2.TYPE = 'TFile' AND T1.X_ATTRIB_11 = 'Y') AND (T2.PAR_ID = :1) ORDER BY T2.PAR_ID, T2.NAME [Execution Plan Information] ---------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ---------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 4211970596.0 ---- | | | 2134 | |001[001]TABLE ACCESS BY INDEX ROWID |TABLE1 | 1 | 74 | 2 | |002[002] NESTED LOOPS | | 42K| 7M| 2134 | |003[003] TABLE ACCESS BY INDEX ROWID |TABLE2 | 42K| 3M| 852 | |004[004] INDEX FULL SCAN |TABLE2_F1 | 1M| | 19872 | |005[003] INDEX RANGE SCAN |TABLE1_U1 | 1 | | 2 | ---------------------------------------------------------------------------- [Predicate Information] -------------------------------------------------------------------------------- 1 filter:"T1"."X_ATTRIB_11"='Y' 3 filter:"T2"."TYPE"='TFile' AND "T2"."PAR_ID"=:1 5 access:"T2"."ROW_ID"="T1"."PAR_ROW_ID" sys@CMPR> |
PAR_ID值較少的語句，走的是索引TABLE2_M1，執(zhí)行計(jì)劃如下：
HASH_VALUE VERSION_COUNT EXECUTIONS USERS_EXECUTING DISK_READS BUFFER_GETS ROWS_PROCESSED CPU_TIME ELAPSED_TIME ---------- ------------- ---------- --------------- ---------- ----------- -------------- ---------- ------------ 245223696 1 1 0 2 6 0 10000 27223 SQL_TEXT ------------------------------------------------------------------ SELECT /*+ value2 */ T2.CONFLICT_ID, T2.LAST_UPD, T2.CREATED, T2.LAST_UPD_BY, T2.CREATED_BY, T2.MODIFICATION_NUM, T2.ROW_ID, T1.X_ATTRIB_03, T1.ROW_ID, T1.MODIFICATION_NUM, T1.CREATED_BY, T1.LAST_UPD_BY, T1.CREATED, T1.LAST_UPD, T1.CONFLICT_ID, T1.PAR_ROW_ID FROM USER.TABLE1 T1, USER.TABLE2 T2 WHERE T2.ROW_ID = T1.PAR_ROW_ID AND (T2.TYPE = 'TFile' AND T1.X_ATTRIB_11 = 'Y') AND (T2.PAR_ID = :1) ORDER BY T2.PAR_ID, T2.NAME [Execution Plan Information] ---------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ---------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 245223696.0 ---- | | | 53 | |001[001]SORT ORDER BY | | 817 | 137K| 53 | |002[002] TABLE ACCESS BY INDEX ROWID |TABLE1 | 1 | 74 | 2 | |003[003] NESTED LOOPS | | 817 | 137K| 27 | |004[004] TABLE ACCESS BY INDEX ROWID |TABLE2 | 817 | 78K| 3 | |005[005] INDEX RANGE SCAN |TABLE2_M1 | 1429 | | 12 | |006[004] INDEX RANGE SCAN |TABLE1_U1 | 1 | | 2 | ---------------------------------------------------------------------------- [Predicate Information] -------------------------------------------------------------------------------- 2 filter:"T1"."X_ATTRIB_11"='Y' 4 filter:"T2"."TYPE"='TFile' 5 access:"T2"."PAR_ID"=:1 6 access:"T2"."ROW_ID"="T1"."PAR_ROW_ID" |
這樣看來，是由于bind peeking的原因引起執(zhí)行計(jì)劃的不同。
考慮到SQL頻繁占用CPU較高，嘗試刪除列PAR_ID的柱狀圖：
exec dbms_stats.set_column_stats('USER','TABLE2','PAR_ID',DISTCNT=>11674); |
檢查發(fā)現(xiàn)數(shù)據(jù)庫發(fā)現(xiàn)，SQL已走索引TABLE2_M1，執(zhí)行計(jì)劃如下：
[Execution Plan Information] ---------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ---------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 628327222.0 ---- | | | 11 | |001[001]SORT ORDER BY | | 81 | 13K| 11 | |002[002] TABLE ACCESS BY INDEX ROWID |TABLE1 | 1 | 74 | 2 | |003[003] NESTED LOOPS | | 81 | 13K| 4 | |004[004] TABLE ACCESS BY INDEX ROWID |TABLE2 | 81 | 7K| 2 | |005[005] INDEX RANGE SCAN |TABLE2_M1 | 142 | | 3 | |006[004] INDEX RANGE SCAN |TABLE1_U1 | 1 | | 2 | ---------------------------------------------------------------------------- [Predicate Information] -------------------------------------------------------------------------------- 2 filter:"T1"."X_ATTRIB_11"='Y' 4 filter:"T2"."TYPE"='TFile' 5 access:"T2"."PAR_ID"=:1 6 access:"T2"."ROW_ID"="T1"."PAR_ROW_ID" |
經(jīng)過一段時(shí)間的觀察，該語句占用CPU高的現(xiàn)像消失，腳本未再抓到該高CPU語句。
聯(lián)系客服
