AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

通过使用hint unnest调优sql语句

2015-02-06 979
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 生产环境中有一条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调优
hint SQL
调优SQL
SQL hint
SQL语句
jeanron100
目录
相关文章
游客kufrkwrbkmpsa
|
SQL 运维 监控
SQL查询太慢？实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章，上一篇《高效查询秘诀，解码YashanDB优化器分组查询优化手段》中，我们揭秘了YashanDB分组查询优化秘诀，本文将通过一个案例，助你快速上手YashanDB慢日志功能，精准定位“慢SQL”后进行优化。
游客kufrkwrbkmpsa
339 2
游客kufrkwrbkmpsa
|
SQL
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
游客kufrkwrbkmpsa
270 2
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
青云交（Java大数据AI云原生Python）
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计：MySQL 数据库 SQL 语句调优方法详解（2-1）
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划，如使用 EXPLAIN 命令及理解关键指标；优化查询语句结构，包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识，如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章，强调 SQL 语句调优重要性。为提升数据库性能提供实用方法，适合数据库管理员和开发人员。
青云交（Java大数据AI云原生Python）
477 11
青云交（Java大数据AI云原生Python）
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计：MySQL 数据库 SQL 语句调优的进阶策略与实际案例（2-2）
本文延续前篇，深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用，介绍多种索引类型及避免索引失效等；调整数据库参数，如缓冲池、连接数和日志参数；还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章，强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导，确保数据库高效运行。
青云交（Java大数据AI云原生Python）
441 5
1639437117631681
|
SQL
【YashanDB 知识库】使用 leading hint 调整 SQL 执行计划后报错 YAS-04522 invalid hint leading
在 YashanDB 的所有版本中，使用 leading hint 调整 SQL 执行计划时可能出现“YAS-04522 invalid hint leading”错误，导致 SQL 无法正常执行。原因是 YashanDB 优化器的 Bug。解决方法为避免使用 leading hint。可通过创建测试表 a、b、c 并执行特定 SQL 语句来验证问题是否存在。
1639437117631681
300 2
威哥爱编程
|
SQL Java 数据库连接
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句？
大家好，我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库，可将 SQL 解析为 Java 对象树，支持多种 SQL 类型（如 `SELECT`、`INSERT` 等）。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后，可以方便地在 Java 代码中使用。
威哥爱编程
4946 11
程序员小海绵
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
程序员小海绵
2271 15
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
何雨晨
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
566 1
余二五
|
SQL 索引
SQL调优之收集统计信息，重建索引 .
余二五
1676 0
异步社区
|
SQL Oracle 关系型数据库
SQL调优技巧：统计信息（文末福利）
统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL的执行计划就会跑偏，SQL也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。
异步社区
2415 0

热门文章

最新文章

  • 1
    30 条 SQL 语句性能优化策略，建议收藏！
  • 2
    Apache Flink 漫谈系列(08) - SQL概览
  • 3
    防止 SQL 注入 · OpenResty
  • 4
    一起谈.NET技术，使用LINQ to SQL更新数据库（中）：几种解决方案
  • 5
    SQL Server 索引和视图
  • 6
    SQL数据库高级查询命令（5）
  • 7
    SQL Server常用的SQL语句<转>
  • 8
    关于Server Sql 2008触发器的使用
  • 9
    【SQL】靠谱的TRIM函数，附赠过程一枚
  • 10
    Entity SQL Language 四 OrderBy/部分查询结果
  • 1
    SQL语言小结
    445
  • 2
    （SQL）SQL语言中的查询语句整理
    435
  • 3
    为什么这些 SQL 语句逻辑相同，性能却差异巨大？
    348
  • 4
    SQL日志优化策略：提升数据库日志记录效率
    423
  • 5
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
    1486
  • 6
    阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
    1578
  • 7
    SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
    732
  • 8
    MCP与PolarDB集成技术分析：降低SQL门槛与简化数据可视化流程的机制解析
    766
  • 9
    阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
    1073
  • 10
    生成更智能，调试更轻松，SLS SQL Copilot 焕新登场！
    767

    • 相关课程

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

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • 一小时快速掌握SQL语法
    • 下一篇
    小红书笔记详情API深度解析与实战指南（2025年最新版）