| 一条高CPU语句的优化 | DBA之路：OoNiceDream |

一条高CPU语句的优化

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/12/high-cpu-sql.html

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

— The End —

关键字: SQL优化 | 性能优化

上一篇 TX Enqueue in Mode 6的解决

大学糗事之——如何开电脑下一篇

喜欢DBARoad的文章，那就通过 RSS Feed 功能订阅阅读吧！

随机文章

已经有4 个评论

1楼
01月 7th, 2009 at 00:17 jiminy 说:
能不能把您的抓取高CPU语句脚本贴一下呢?非常感谢！

Reply
2楼
01月 7th, 2009 at 15:47 OoNiceDream 说:
我这是HP平台的，应该还是好写的。就是取出TOP的结果，找出CPU使用超过８０％，再根据PID到V$PROCESS、V$SESSION中找到SQL_HASH_VALUE。最后找出SQL。

Reply
3楼
07月 20th, 2009 at 21:21 Freedba 说:
你把柱状图删除了,并且把列的distinct值设置得很大.
暂时解决了CPU的问题,不过对于列上值比较大的那个变量,下次要跟着走INDEX RANGE SCAN了,不见得好哦.
长期考虑,最好还是语句修改一下,对于列上值比较多的那个,用常量而不是bind.你觉得呢

Reply
OoNiceDream Reply:
07月 21st, 2009 at 09:36
嗯，这也是个问题，主要还是看访问的频率，当时的情况也记不清了，应该是走INDEX RANGE SCAN也不会太慢，而且这个系统用的是套装软件，语句还不好改。

Reply

公告牌:

当前位置: DBARoad > SQL优化 | 性能优化, 本站推荐 > 文章正文