备案控制台
探索云世界
开发者社区 数据库 文章 正文

通过使用hint unnest调优sql语句

2015-02-06 799
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了，竟然达到了13个小时，而且还没有执行完。 Session APPC (20015:7013) SQL I...
生产环境中有一条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 (      
       
. 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个小时以内。性能的提升还是相当的可观的。
文章标签：
SQL
索引
关键词：
SQL调优
SQL hint
SQL语句
调优SQL语句
jeanron100
目录
相关文章
Sereinn
|
1月前
|
SQL 存储 弹性计算
GaussDB SQL调优：建立合适的索引
GaussDB SQL调优：建立合适的索引
Sereinn
12 0
冷色系456
|
7月前
|
SQL 监控
OBCP第四章 SQL调优-SQL执行性能监控
OBCP第四章 SQL调优-SQL执行性能监控
冷色系456
59 0
冷色系456
|
7月前
|
SQL 关系型数据库 MySQL
OBCP第四章 SQL调优-Hint
OBCP第四章 SQL调优-Hint
冷色系456
198 0
冷色系456
|
7月前
|
SQL 索引 OceanBase
OBCP第四章 SQL调优-局部索引与全局索引
OBCP第四章 SQL调优-局部索引与全局索引
冷色系456
79 0
冷色系456
|
7月前
|
SQL 存储 关系型数据库
OBCP第四章 SQL调优-分区
OBCP第四章 SQL调优-分区
冷色系456
155 0
xiaohua616
|
4天前
|
SQL XML 前端开发
sql 性能优化基于explain调优（二）
sql 性能优化基于explain调优（二）
xiaohua616
10 0
六月的雨在钉钉
|
3月前
|
SQL 存储 数据库
达梦(DM) SQL调优
【1月更文挑战第2天】达梦(DM) SQL调优
六月的雨在钉钉
132 0
bjcuug
|
5月前
|
SQL 关系型数据库 OLTP
PostgreSQL技术大讲堂 - 第31讲：SQL调优技巧
PostgreSQL从小白到专家，系列技术大讲堂 - 第31讲：SQL调优技巧
bjcuug
573 3
冷色系456
|
7月前
|
SQL 关系型数据库 MySQL
OBCP第四章 SQL调优-索引
OBCP第四章 SQL调优-索引
冷色系456
55 0
ftw2fzqaoykua
|
7月前
|
SQL 关系型数据库 MySQL
RDS MySQL的SQL问题诊断与调优
本教程为您介绍如何通过控制台在RDS MySQL上创建账号和数据库，通过DMS进行MySQL常见的表创建、索引创建等操作。
ftw2fzqaoykua
104 0

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 3
    【软件设计师备考 专题 】数据库语言（SQL）
  • 4
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 5
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 6
    SQL常见面试题总结2
  • 7
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 8
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 9
    SQL，Group By 真扎心，原来是这样
  • 10
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 1
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 2
    sql 性能优化基于explain调优（二）
    10
  • 3
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 4
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    11
  • 5
    Oracle的PL/SQL表达式：数据的魔法公式
    13
  • 6
    Oracle的PL/SQL定义变量和常量：数据的稳定与灵动
    13
  • 7
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
    43
  • 8
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
    41
  • 9
    如何在 Python 中进行 Web 应用程序的安全性管理，例如防止 SQL 注入？
    11
  • 10
    IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
    13

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）