这两天抓取高CPU SQL的脚本频繁发出一个占用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
|
执行计划如下：
[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"
|
乍看这个语句，问题也不是很大，主要的消耗是在索引TABLE2_F1上的INDEX FULL SCAN。
索引TABLE2_F1是建在表USER.TABLE2(T2表)的NAME列上。
其实也比较好理解，当有排序操作时，优化器比较偏向使用INDEX FULL SCAN，因为INDEX FULL SCAN返回的数据是有序的，这样就避免了排序
查看表USER.TABLE2(T2表)，发现列PAR_ID是有索引的(TABLE2_M1)。
在PL/SQL工具中，查看下执行计划，发现走的是TABLE2_M1索引(INDEX RANGE SCAN),但多了一个排序的操作(SORT ORDER BY )
因为列PAR_ID采用的是绑定变量，决定试试代入不同的变量值，查看其执行计划是否有不同。
通过select PAR_ID,count(*) from USER.TABLE2 group by PAR_ID;
选取了两个有代表性的值，一个值较多，一个值较少。
在TOAD中运行语句，并通过加无效的HINT来区分（使SQL硬解析，避免bind peeking的影响），另开一个会话，抓取执行计划：
发现PAR_ID值较多的语句，走的是索引TABLE2_F1，执行计划如下：
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，执行计划如下：
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的原因引起执行计划的不同。
考虑到SQL频繁占用CPU较高，尝试删除列PAR_ID的柱状图：
exec dbms_stats.set_column_stats('USER','TABLE2','PAR_ID',DISTCNT=>11674);
|
检查发现数据库发现，SQL已走索引TABLE2_M1，执行计划如下：
[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"
|
经过一段时间的观察，该语句占用CPU高的现像消失，脚本未再抓到该高CPU语句。
|
