通过使用hint unnest调优sql语句(r4笔记第38天)

jeanron100

发布于 2018-03-15 15:12:59

8570

发布于 2018-03-15 15:12:59

生产环境中有一条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			.
->	1	. SORT AGGREGATE	.	1	.	1	0			.
->	2	.. FILTER	.	.	.	1	403K			.
->	2	.. FILTER	.	.	.	1	403K			.
->	3	... NESTED LOOPS	.	.	.	1	562K			.
->	3	... NESTED LOOPS	.	.	.	1	562K			.
->	4	.... NESTED LOOPS	.	1682	45038	1	562K			.
->	4	.... NESTED LOOPS	.	1682	45038	1	562K			.
->	5	..... PARTITION RANGE ITERATOR	.	1682	44869	1	562K			.
->	5	..... PARTITION RANGE ITERATOR	.	1682	44869	1	562K			.
.	6	...... TABLE ACCESS FULL	BL1_CYCLE_CUSTOMERS	1682	44869	1	562K	7736 (<0.1%)
.	6	...... TABLE ACCESS FULL	BL1_CYCLE_CUSTOMERS	1682	44869	1	562K
.	7	..... INDEX UNIQUE SCAN	BL1_CUSTOMER_PK	1	1	949K	562K	.	673K (3.3%)	###
.	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%
->	8	.... TABLE ACCESS BY INDEX ROWID	BL1_CUSTOMER	1	1	990K	562K	.	11M (52%)	.	73%
->	9	... NESTED LOOPS	.	.	.	562K	403K			.
->	9	... NESTED LOOPS	.	.	.	562K	403K			.
->	10	.... NESTED LOOPS	.	1	74	562K	6M			.
->	10	.... NESTED LOOPS	.	1	74	562K	6M			.
->	11	..... PARTITION RANGE ITERATOR	.	1	37	562K	497K			.
->	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%
.	12	...... TABLE ACCESS BY LOCAL INDEX ROWID	BL1_CYC_PAYER_POP	1	37	562K	497K	.	774K (3.8%)
.	13	....... INDEX RANGE SCAN	BL1_CYC_PAYER_POP_1IX	3	36	562K	4M	.	864K (4.2%)	.	1.90%
.	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			.
->	14	..... PARTITION RANGE ITERATOR	.	14	36	497K	6M			.
.	15	...... INDEX RANGE SCAN	BL1_DOCUMENT_1IX	14	36	497K	6M	.	4M (20%)	.	21%
.	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%
.	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个小时以内。性能的提升还是相当的可观的。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2015-02-06，如有侵权请联系 cloudcommunity@tencent.com 删除

其他

本文分享自杨建荣的学习笔记微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

其他

登录后参与评论

0 条评论

热度

通过使用hint unnest调优sql语句(r4笔记第38天)

通过使用hint unnest调优sql语句(r4笔记第38天)

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐