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

生产环境中有一条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个小时以内。性能的提升还是相当的可观的。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-02-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

3182
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

753
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

3676
来自专栏杨建荣的学习笔记

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

2247
来自专栏SAP最佳业务实践

SAP S/4 HANA新变化-FI数据模型

With the installation of SAP Simple Finance, on-premise edition totals and appli...

4447
来自专栏杨建荣的学习笔记

关于sql_profile中的绑定变量(r4笔记第57天)

使用sql_profile来调优一些紧急的性能sql可以起到立竿见影的效果,如果sql语句本身结构就很清晰,简单,略作修改就能得到调优后的sql语句。 但是如果...

3706
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28411
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

3045
来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3735
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

4124

扫码关注云+社区

领取腾讯云代金券