公众号：杨建荣的学习笔记 · 作者：r4笔记第38天 · 发布：2015-02-06 22:25:03 · 原文链接

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了，竟然达到了13个小时，而且还没有执行完。

sql语句如下：
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = ‘N’ OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT 1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = ‘BL’
AND (PAYER.UNDO_REQ_TYPE = ‘N’ OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN (‘Y’, ‘E’))

查看执行计划没有发现很严重的资源消耗。但是实际的执行情况怎么和执行计划相差甚远。预计8分钟，实际上十多个小时还没有执行完。
Plan hash value: 3506320481
——————————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
——————————————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 24 | 42048 (1)| 00:08:25 | | |
| 1 | SORT AGGREGATE | | 1 | 24 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 24 | 42046 (1)| 00:08:25 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 6 | TABLE ACCESS FULL | BL1_CYCLE_CUSTOMERS | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 7 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_CYC_PAYER_POP | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 13 | INDEX RANGE SCAN | BL1_CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | 171 | 171 |
| 14 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 15 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 |
——————————————————————————————————————————–
这个时候可以通过sql monitor得到一个相对比较准确的资源使用情况。

一看IO请求达21M次，约等于160.9G左右的数据量。
从sql语句的执行计划可以看出，语句可以分为两大部分，一部分是exist字句上面的部分，两个大表做了关联，得到了相关的customer_no然后在exists字句中继续关联。
大量的IO请求都消耗在BL1_CUSTOMER,其实这个表实际上数据量近千万，还没有80G多G，但是发送的IO请求累计的数据量却已经超过了80G，占到了整个IO请求数的一半以上。消耗的CPU资源也在73%以上

可以通过禁用子查询解嵌套来做为一种调优思路，优先从子查询中先输出数据来。
而BL1_CYCLE_PAYER_POP表作为一个重要的关联表。子查询中的条件AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO和外部查询相关联。
可以优先查询这个表，考虑到执行的频率和性能，添加了并行hint。
这样sql语句就变为
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = ‘N’ OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT /*+unnest full(payer) parallel(payer 4)*/1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = ‘BL’
AND (PAYER.UNDO_REQ_TYPE = ‘N’ OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN (‘Y’, ‘E’))
优化后的执行计划如下：
Plan hash value: 227985194
———————————————————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
———————————————————————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 37 | 13688 (1)| 00:02:45 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 37 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 37 | | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 37 | | | | | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 6 | NESTED LOOPS | | 1 | 37 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 28 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 8 | VIEW | VW_SQ_1 | 1 | 13 | 13686 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 9 | HASH UNIQUE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 1 | 52 | | | | | Q1,00 | P->P | HASH |
| 12 | HASH UNIQUE | | 1 | 52 | | | | | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | | | | | | | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 52 | 13686 (1)| 00:02:45 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | BL1_CYC_PAYER_POP | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWP | |
| 17 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 18 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
| 20 | PARTITION RANGE SINGLE | | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 21 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_CYCLE_CUSTOMERS | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 22 | INDEX RANGE SCAN | BL1_CYCLE_CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 23 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 24 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
———————————————————————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
16 – filter(“PAYER”.”FORMAT_EXT_DATE” IS NULL AND “PAYER”.”CYCLE_SEQ_NO”=4105 AND “PAYER”.”PERIOD_KEY”=61 AND (“PAYER”.”UNDO_REQ_TYPE”=’N’ OR
“PAYER”.”UNDO_REQ_TYPE” IS NULL) AND “PAYER”.”DB_STATUS”=’BL’ AND “PAYER”.”CYCLE_SEQ_RUN”=0)
18 – access(“PAYER”.”BA_NO”=”DOC”.”BA_NO”)
19 – filter(“DOC”.”CYCLE_SEQ_NO”=4105 AND “DOC”.”PERIOD_KEY”=61 AND “DOC”.”CYCLE_SEQ_RUN”=0 AND (“DOC”.”DOC_PRODUCE_IND”=’E’ OR
“DOC”.”DOC_PRODUCE_IND”=’Y’))
21 – filter(“CYC_CUST”.”UNDO_REQ_TYPE”=’N’ OR “CYC_CUST”.”UNDO_REQ_TYPE” IS NULL)
22 – access(“ITEM_0″=”CYC_CUST”.”CUSTOMER_NO” AND “CYC_CUST”.”CYCLE_SEQ_NO”=4105 AND “CYC_CUST”.”PERIOD_KEY”=61)
23 – access(“CYC_CUST”.”CUSTOMER_NO”=”CUST”.”CUSTOMER_ID”)

最后得到的反馈是，原本执行近20个小时的查询，在添加这个Hint之后，执行时间缩短到了1个小时以内。性能的提升还是相当的可观的。