一条高CPU语句的优化

一条高CPU语句的优化

这两天抓取高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语句。