通过使用hint unnest调优sql语句

最新推荐文章于 2023-07-03 11:18:23 发布

congbao6525

最新推荐文章于 2023-07-03 11:18:23 发布

阅读量139

点赞数

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

Session	APPC (20015:7013)
SQL ID	74pzzzjddkyd4
SQL Execution ID	16777242
Execution Started	2/2/2015 10:52
First Refresh Time	2/2/2015 10:52
Last Refresh Time	2/3/2015 0:05
Duration	47669s
Module/Action	bfi@ccbdbpr1 (TNS V1-V3)/-
Service	XXXXX
Program	bfi@xxx (TNS V1-V3)

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得到一个相对比较准确的资源使用情况。

Buffer Gets		IO Requests		Database Time		Wait Activity
.	96M	.	21M	.	48518s	.	100%

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

Id		Operation	Name	Estimated	Cost	Execs	Rows	IO Requests		CPU Activity
				Rows
.	0	SELECT STATEMENT	.	.	.	1	.			.
->	1	. SORT AGGREGATE	.	1	.	1	0			.

->	2	.. FILTER	.	.	.	1	403K			.

->	3	... NESTED LOOPS	.	.	.	1	562K			.

->	4	.... NESTED LOOPS	.	1682	45038	1	562K			.

->	5	..... PARTITION RANGE ITERATOR	.	1682	44869	1	562K			.

.	6	...... TABLE ACCESS FULL	BL1_CYCLE_CUSTOMERS	1682	44869	1	562K	7736 (<0.1%)

.	7	..... INDEX UNIQUE SCAN	BL1_CUSTOMER_PK	1	1	949K	562K	.	673K (3.3%)	###

->	8	.... TABLE ACCESS BY INDEX ROWID	BL1_CUSTOMER	1	1	990K	562K	.	11M (52%)	.	73%
												->	9	... NESTED LOOPS	.	.	.	562K	403K			.

->	10	.... NESTED LOOPS	.	1	74	562K	6M			.

->	11	..... PARTITION RANGE ITERATOR	.	1	37	562K	497K			.

.	12	...... TABLE ACCESS BY LOCAL INDEX ROWID	BL1_CYC_PAYER_POP	1	37	562K	497K	.	774K (3.8%)	.	0.99%

.	13	....... INDEX RANGE SCAN	BL1_CYC_PAYER_POP_1IX	3	36	562K	4M	.	864K (4.2%)	.	1.90%
												->	14	..... PARTITION RANGE ITERATOR	.	14	36	497K	6M			.

.	15	...... INDEX RANGE SCAN	BL1_DOCUMENT_1IX	14	36	497K	6M	.	4M (20%)	.	21%
												.	16	.... TABLE ACCESS BY LOCAL INDEX ROWID	BL1_DOCUMENT	1	37	6M	403K	.	3M (15%)	.	1.20%

可以通过禁用子查询解嵌套来做为一种调优思路，优先从子查询中先输出数据来。
而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个小时以内。性能的提升还是相当的可观的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1429117/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1429117/