当前位置: 开发笔记 > 数据库 > 正文

统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟

作者：小勺年 | 来源：互联网 | 2018-06-06 09:02

一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。优化前：SELECT20131023,A2.ORG_ID,COUNT(DISTINCTNLSSORT(CASEA2.RES_TYPEWHENDPTHENA2.RES_CODEEND,nls_sortBINARY)),COUNT(DISTINCTNLSSORT(CASE

一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。优化前： SELECT 20131023, A2.ORG_ID, COUNT(DISTINCT NLSSORT(CASE A2.RES_TYPE WHEN DP THEN A2.RES_CODE END, nls_sort=BINARY)), COUNT(DISTINCT NLSSORT(CASE

一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。

优化前：

SELECT 20131023,
       "A2"."ORG_ID",
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;DP&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;BOX&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;ONU&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;OBD&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;001&＃39; AND "A2"."RES_TYPE" = &＃39;DP&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;002&＃39; AND "A2"."RES_TYPE" = &＃39;BOX&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0011&＃39; AND "A2"."RES_TYPE" = &＃39;ONU&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0022&＃39; AND "A2"."RES_TYPE" = &＃39;OBD&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;))
  FROM "CRM_SZ"."AAA" "A2",
       "CRM_SZ"."BBB" "A1"
 WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
 GROUP BY "A2"."ORG_ID"

执行计划：
Plan hash value: 2627707252
 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |  1065 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY      |                 |     1 |  1065 |     3  (34)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER|                 |     1 |  1065 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| AAA             |     1 |   539 |     2   (0)| 00:00:01 |
|*  4 |    INDEX FULL SCAN  | IX_MO_CON_VALUE |     1 |   526 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A1"."RES_ID"(+)="A2"."RES_CODE")
       filter("A1"."RES_ID"(+)="A2"."RES_CODE")

cbo估算错了，rows全是1，导致走nl
手工count了一把：
select count(*) from "CRM_SZ"."AAA" ;--1365564
select count(*) from "CRM_SZ"."BBB";--119949
走nl那岂不是sb啦。

第一次优化后：

SELECT/*+use_hash(A1,A2) swap_join_inputs(A1)*/20131023,
       "A2"."ORG_ID",
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;DP&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;BOX&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;ONU&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;OBD&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;001&＃39; AND "A2"."RES_TYPE" = &＃39;DP&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;002&＃39; AND "A2"."RES_TYPE" = &＃39;BOX&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0011&＃39; AND "A2"."RES_TYPE" = &＃39;ONU&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0022&＃39; AND "A2"."RES_TYPE" = &＃39;OBD&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;))
  FROM "CRM_SZ"."AAA"        "A2",
       "CRM_SZ"."BBB" "A1"
 WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
 GROUP BY "A2"."ORG_ID"

63 rows selected.

Elapsed: 00:00:47.64

Execution Plan
----------------------------------------------------------
Plan hash value: 3074972763

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |  1065 |     4  (50)| 00:00:01 |
|   1 |  SORT GROUP BY         |                 |     1 |  1065 |     4  (50)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT OUTER|                 |     1 |  1065 |     3  (34)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | IX_MO_CON_VALUE |     1 |   526 |     0   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | AAA             |     1 |   539 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A1"."RES_ID"(+)="A2"."RES_CODE")

Statistics
----------------------------------------------------------
       1065  recursive calls
          3  db block gets
      13375  consistent gets
      16369  physical reads
          0  redo size
       4862  bytes sent via SQL*Net to client
        791  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
         12  sorts (memory)
          1  sorts (disk)
         63  rows processed

第二次优化后：

SELECT/*+use_hash(A1,A2) full(A1) full(A2) parallel(A1,5) parallel(A2,5) swap_join_inputs(A1)*/20131023,
       "A2"."ORG_ID",
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;DP&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;BOX&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;ONU&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN &＃39;OBD&＃39; THEN
                        "A2"."RES_CODE"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;001&＃39; AND "A2"."RES_TYPE" = &＃39;DP&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;002&＃39; AND "A2"."RES_TYPE" = &＃39;BOX&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0011&＃39; AND "A2"."RES_TYPE" = &＃39;ONU&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;)),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = &＃39;0022&＃39; AND "A2"."RES_TYPE" = &＃39;OBD&＃39;) THEN
                        "A1"."RES_ID"
                     END,
                     &＃39;nls_sort=&＃39;&＃39;BINARY&＃39;&＃39;&＃39;))
  FROM "CRM_SZ"."AAA"        "A2",
       "CRM_SZ"."BBB" "A1"
 WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
 GROUP BY "A2"."ORG_ID";

63 rows selected.

Elapsed: 00:00:10.21

Execution Plan
----------------------------------------------------------
Plan hash value: 4044842257

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |  1065 |     6  (34)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |                       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10004              |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,04 | P->S | QC (RAND)  |
|   3 |    SORT GROUP BY               |                       |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                 |                       |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH              | :TQ10003              |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,03 | P->P | HASH       |
|   6 |       SORT GROUP BY            |                       |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE              |                       |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH           | :TQ10002              |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   9 |          SORT GROUP BY         |                       |     1 |  1065 |     6  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|* 10 |           HASH JOIN RIGHT OUTER|                       |     1 |  1065 |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |            PX RECEIVE          |                       |     1 |   526 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  12 |             PX SEND HASH       | :TQ10000              |     1 |   526 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  13 |              PX BLOCK ITERATOR |                       |     1 |   526 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  14 |               TABLE ACCESS FULL| BBB                   |     1 |   526 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  15 |            PX RECEIVE          |                       |     1 |   539 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  16 |             PX SEND HASH       | :TQ10001              |     1 |   539 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  17 |              PX BLOCK ITERATOR |                       |     1 |   539 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  18 |               TABLE ACCESS FULL| AAA                   |     1 |   539 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - access("A1"."RES_ID"(+)="A2"."RES_CODE")


Statistics
----------------------------------------------------------
        585  recursive calls
          4  db block gets
      14267  consistent gets
      13126  physical reads
        808  redo size
       4888  bytes sent via SQL*Net to client
        840  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
         63  rows processed

优化前，执行计划走了nl，1个多小时查不出结果
第一次优化后，首次00:00:47.64可以出结果
第二次优化后，首次00:00:10.21出结果，重复执行（有缓存）的情况下3s出结果。

推荐阅读

sql
利用SAS生成动态SQL查询

本文介绍如何使用SAS根据输入数据集自动生成并执行SQL查询，其中CASE语句依据另一个数据集中的观测值动态调整。 ... [详细]

蜡笔小新 2024-12-22 15:47:35
sql
SQL数据库面试题解析

本文深入探讨了SQL数据库中常见的面试问题，包括如何获取自增字段的当前值、防止SQL注入的方法、游标的作用与使用、索引的形式及其优缺点，以及事务和存储过程的概念。通过详细的解答和示例，帮助读者更好地理解和应对这些技术问题。 ... [详细]

蜡笔小新 2024-12-22 14:43:35
sql
PostgreSQL 最新动态 —— 2022年4月6日

了解 PostgreSQL 社区的最新进展和技术分享 ... [详细]

蜡笔小新 2024-12-21 14:54:10
sql
MySQL中Bin Log与Redo Log的区别及应用场景

本文详细介绍了MySQL数据库中的Bin Log和Redo Log，阐述了它们在日志记录机制、应用场景以及数据恢复方面的区别。通过对比分析，帮助读者更好地理解这两种日志文件的作用和特性。 ... [详细]

蜡笔小新 2024-12-21 13:50:51
sql
SP框架数据操作指南

本章详细介绍SP框架中的数据操作方法，包括数据查找、记录查询、新增、删除、更新、计数及字段增减等核心功能。通过具体示例和详细解析，帮助开发者更好地理解和使用这些方法。 ... [详细]

蜡笔小新 2024-12-21 13:27:32
sql
SQL Server 登录验证模式的修改方法：混合验证模式详解

在安装 SQL Server 时，选择混合验证模式可以提供更高的灵活性和管理便利性。如果您已经安装了 SQL Server 并使用单一的 Windows 身份验证模式，可以通过以下步骤将其更改为混合验证模式。 ... [详细]

蜡笔小新 2024-12-21 11:32:52
sql
SQL Server 2012 客户端 SQL 格式化工具与方法

本文介绍了在 SQL Server 2012 客户端中格式化 SQL 查询语句的多种方法，包括内置功能和第三方工具，帮助用户提高代码可读性和维护性。 ... [详细]

蜡笔小新 2024-12-21 10:59:16
sql
SQL Server 中 INSERT 语句无法插入数据的问题分析与解决

本文探讨了在 SQL Server 中使用 JDBC 插入数据时遇到的问题。通过详细分析代码和数据库配置，提供了解决方案并解释了潜在的原因。 ... [详细]

蜡笔小新 2024-12-21 09:52:27
sql
优化Flask应用的并发处理：解决Mysql连接过多问题

本文探讨了在Flask应用中通过优化后端架构来应对高并发请求，特别是针对Mysql 'too many connections' 错误的解决方案。我们将介绍如何利用Redis缓存、Gunicorn多进程和Celery异步任务队列来提升系统的性能和稳定性。 ... [详细]

蜡笔小新 2024-12-21 09:21:49
sql
优化SQL Server批量数据插入存储过程的实现

本文介绍了一种改进的SQL Server存储过程，用于生成批量插入语句。该方法不仅提高了性能，还支持单行和多行模式，适用于SQL Server 2005及以上版本。 ... [详细]

蜡笔小新 2024-12-21 06:43:52
sql
使用JS、HTML5和C3创建自定义弹出窗口

本文介绍如何结合JavaScript、HTML5和C3.js来实现一个功能丰富的自定义弹出窗口。通过具体的代码示例，详细讲解了实现过程中的关键步骤和技术要点。 ... [详细]

蜡笔小新 2024-12-20 21:22:27
sql
主调|大侠_重温C++

主调|大侠_重温C++ ... [详细]

蜡笔小新 2024-12-20 20:43:56
sql
Java SpringMVC SSM 实现多模块集成：操作日志、文件管理、头像编辑、权限控制及缓存优化

本文介绍了一个基于 Java SpringMVC 和 SSM 框架的综合系统，涵盖了操作日志记录、文件管理、头像编辑、权限控制、以及多种技术集成如 Shiro、Redis 等，旨在提供一个高效且功能丰富的开发平台。 ... [详细]

蜡笔小新 2024-12-20 19:17:47
sql
使用正则表达式去除字符串中单词间的空格

本文探讨了如何在Hive（基于Hadoop）环境中编写类似SQL的语句，以去除字段中的空格。特别是在处理邮政编码等数据时，去除特定位置的空格是常见的需求。 ... [详细]

蜡笔小新 2024-12-20 19:08:43
sql
备战BAT面试：掌握这些MySQL核心问题

本文深入探讨了MySQL中常见的面试问题，包括事务隔离级别、存储引擎选择、索引结构及优化等关键知识点。通过详细解析，帮助读者在面对BAT等大厂面试时更加从容。 ... [详细]

蜡笔小新 2024-12-20 18:58:01

小勺年

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章