我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
在Oracle数据库中，SQL优化的工作其实一直困扰着初级DBA，特别是对AP环境的SQL优化。相信很多小伙伴在看着上百行的执行计划时就直接懵圈了，心中跑过千匹马。马过后，还是得想象怎么优化SQL语句。此时怎么能快速的定位执行计划慢在哪一步呢？有人会说SQL Monitor啊，确实是的。Oracle SQL Monitor它是Oracle数据库提供的一种实时 SQL 性能监控功能，主要用于跟踪和分析正在执行或刚刚执行完毕的复杂SQL语句的执行情况。它依赖于 Oracle 的实时监控基础设施（Real-Time SQL Monitoring），当SQL语句满足一定条件（如执行时间超过5秒）时，会自动触发生成监控数据。
但是在OLTP环境中SQL执行的时间基本是小于5秒，无法触发SQL Monitor功能的，那这个时候是否有什么技巧让我们快速的知道SQL慢在执行计划那一步呢？
其实在Oracle 11G中除了引入SQL Monitor外，在ASH中也新增了下面几列跟执行计划相关的列：
SQL_PLAN_LINE_ID
SQL_PLAN_HASH_VALUE
我们可以利用这两列的信息与DBMS_XPLAN的结果做一个关联，这样就可以统计出执行计划中每一步在ASH中出现的次数，次数越多也就意外着出现的次数越多，也就意味着执行时间越久，那么也就越慢。下面看看脚本执行的结果：
脚本的执行结果
@plan_ash_by_sqlid.sql
Session altered.
Enter value for sqlid: ft8vdgx7hy07g
关键内容的显示
****************************************************************************************
PLAN STAT FROM ASH
****************************************************************************************
SQL_ID ft8vdgx7hy07g, child number 0
-------------------------------------
INSERT INTO CUX.CUX_SALE_DAY_DATA_TEMP SELECT
FND_GLOBAL.CONC_REQUEST_ID, OOH.ORG_ID, -MMT.PRIMARY_QUANTITY
PRIMARY_QUANTITY, MMT.TRANSACTION_DATE, FFVV.FLEX_VALUE,
FFVV.FLEX_VALUE_ID, FFVV.DESCRIPTION FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL, MTL_MATERIAL_TRANSACTIONS MMT,
MTL_ITEM_CATEGORIES MIC, MTL_CATEGORY_SETS_V MCS, MTL_CATEGORIES MC,
FND_FLEX_VALUES_VL FFVV WHERE 1 = 1 AND OOH.ORG_ID = OOL.ORG_ID AND
OOH.HEADER_ID = OOL.HEADER_ID AND MIC.INVENTORY_ITEM_ID =
OOL.INVENTORY_ITEM_ID AND MMT.ORGANIZATION_ID = MIC.ORGANIZATION_ID AND
MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID AND MMT.TRANSACTION_TYPE_ID IN
('15', '33') AND CUX_1_OM_DAILY_SALES_REP.GET_CAT_FLAG(MC.CATEGORY_ID,
MC.STRUCTURE_ID, :B4 , FFVV.FLEX_VALUE_ID) = 'Y' AND MCS.STRUCTURE_ID =
101 AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID AND MIC.CATEGORY_ID =
MC.CATEGORY_ID AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID AND
FFVV.FLEX_VALUE_SET_ID = (SELECT FLEX_VALUE_SET_ID FROM
FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME =
Plan hash value: 2064863187
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1010 (100)| |CPU(9)(.23%) |
| |CPU(1)(.03%) |
| 1 | LOAD TABLE CONVENTIONAL | CUX_SALE_DAY_DATA_TEMP | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 388 | 1010 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 388 | 1010 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 378 | 1008 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 353 | 1008 (1)| 00:00:01 |CPU(1)(.03%) |
| 7 | NESTED LOOPS | | 1 | 327 | 1003 (1)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 301 | 1003 (1)| 00:00:01 |
| 9 | NESTED LOOPS | | 5 | 1395 | 364 (1)| 00:00:01 |
|* 10 | HASH JOIN | | 1 | 259 | 37 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 233 | 21 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 233 | 21 (0)| 00:00:01 |
| 13 | MERGE JOIN CARTESIAN | | 1 | 203 | 20 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 147 | 18 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 147 | 18 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 104 | 16 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1 | 1 | 34 | 0 (0)| |
|* 20 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 40 | 15 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | FND_FLEX_VALUES_N2 | 109 | | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUE_SETS | 1 | 44 | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | FND_FLEX_VALUE_SETS_U2 | 1 | | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | FND_FLEX_VALUES_TL_U1 | 1 | | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES_TL | 1 | 43 | 2 (0)| 00:00:01 |
| 26 | BUFFER SORT | | 1 | 56 | 18 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 56 | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | MTL_CATEGORY_SETS_B_N1 | 1 | | 0 (0)| |
|* 29 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B | 1 | 30 | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED| MTL_CATEGORIES_B | 514 | 13364 | 16 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | MTL__CATEGORIES_B_N2 | 514 | | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ITEM_CATEGORIES | 161 | 3220 | 326 (0)| 00:00:01 |CPU(5)(.13%) |
| |cell single block physica(21)(.55%) |
|* 33 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_N3 | 724 | | 5 (0)| 00:00:01 |CPU(3)(.08%) |
| |cell single block physica(10)(.26%) |
|* 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_LINES_ALL | 1 | 22 | 623 (1)| 00:00:01 |CPU(621)(16.19%) |
| |gc current block 2-way(3)(.08%) |
| |cell single block physica(637)(16.61%|
| |gc cr block 2-way(1)(.03%) |
| |latch: gc element(1)(.03%) |
| |cell single block read re(5)(.13%) |
| |cell list of blocks physi(62)(1.62%) |
| |gc current block 3-way(1)(.03%) |
|* 35 | INDEX RANGE SCAN | OE_ORDER_LINES_N3 | 699 | | 4 (0)| 00:00:01 |cell single block physica(2167)(56.51|
| |CPU(144)(3.75%) |
| |cell single block read re(1)(.03%) |
| |gc cr disk read(108)(2.82%) |
| |latch: gc element(1)(.03%) |
| |gc cr block 2-way(8)(.21%) |
| |gc current grant busy(5)(.13%) |
|* 36 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | 26 | 0 (0)| |
|* 37 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_MATERIAL_TRANSACTIONS | 1 | 26 | 5 (0)| 00:00:01 |cell single block physica(2)(.05%) |
| |cell list of blocks physi(7)(.18%) |
| |CPU(3)(.08%) |
|* 38 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N27 | 2 | | 3 (0)| 00:00:01 |CPU(3)(.08%) |
| |cell single block physica(4)(.1%) |
|* 39 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 25 | 0 (0)| |
|* 40 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 10 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B2+1>=:B3-1)
10 - access("STRUCTURE_ID"="STRUCTURE_ID")
filter("CUX_1_OM_DAILY_SALES_REP"."GET_CAT_FLAG"("CATEGORY_ID","STRUCTURE_ID",:B4,"FLEX_VALUE_ID")='Y')
18 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND
"ZD_EDITION_NAME"='V_20151118_1137')
19 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
20 - filter(("HIERARCHY_LEVEL"=:B4 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20190522_0926'))
21 - access("FLEX_VALUE_SET_ID"=)
23 - access("FLEX_VALUE_SET_NAME"='NHLH_ITEM_CATAGORY_VS' AND "ZD_EDITION_NAME"='V_20190522_0926')
24 - access("FLEX_VALUE_ID"="FLEX_VALUE_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20190522_0926')
27 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20190522_0926')
filter(("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20190522_0926'))
28 - access("STRUCTURE_ID"=101)
29 - filter(("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20151118_1137'))
30 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='V_20151118_1137')
31 - access("STRUCTURE_ID"=101)
33 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID")
34 - filter("OOL"."ORG_ID"=:B1)
35 - access("MIC"."INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID")
36 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
37 - filter(("ORGANIZATION_ID"="MIC"."ORGANIZATION_ID" AND INTERNAL_FUNCTION("TRANSACTION_TYPE_ID") AND
TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))>=:B3-1 AND TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))<=:B2+1))
38 - access("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID")
filter("TRX_SOURCE_LINE_ID" IS NOT NULL)
39 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
40 - access("OOH"."HEADER_ID"="OOL"."HEADER_ID")
41 - filter("OOH"."ORG_ID"=:B1)
PL/SQL procedure successfully completed.
这里根据下面等待事件及出现的次数和百分比，次数越多、百分比越大，就意味着越慢，是不是通过这种方式就可以一眼知道执行计划慢在哪一步了，
CPU(5)(.13%) |
cell single block physica(21)(.55%) |
CPU(3)(.08%) |
cell single block physica(10)(.26%) |
CPU(621)(16.19%) |
gc current block 2-way(3)(.08%) |
cell single block physica(637)(16.61%|
gc cr block 2-way(1)(.03%) |
latch: gc element(1)(.03%) |
cell single block read re(5)(.13%) |
cell list of blocks physi(62)(1.62%) |
gc current block 3-way(1)(.03%) |
cell single block physica(2167)(56.51|
CPU(144)(3.75%) |
cell single block read re(1)(.03%) |
gc cr disk read(108)(2.82%) |
latch: gc element(1)(.03%) |
gc cr block 2-way(8)(.21%) |
gc current grant busy(5)(.13%) |
cell single block physica(2)(.05%) |
cell list of blocks physi(7)(.18%) |
CPU(3)(.08%) |
CPU(3)(.08%) |
cell single block physica(4)(.1%) |
所以这个就是所谓的“傻子”都可以一眼知道执行计划慢在什么地方。
其实Oracle中优化SQL语句真的变得越来越简单了，只要合理利用Oracle现在提供的功能或者简单做一些组合，就可以将优化的效率提到几倍。
脚本的获取
还是老方法，脚本已经同步到群里面，需要加群请直接加我微信：18081072613.
——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
Oracle DBA必备脚本：傻子都可以一眼就定位SQL慢在什么地方：等您坐沙发呢！