SQL优化----如何使用工具快速诊断出统计信息有问题?

原创 2013年12月04日 18:09:24

   下列的SQL语句是一个报表统计的SQL,听开发说with里面返回的记录其实很少,只有十几条而已。

SQL> WITH STAT AS (

  2    SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,PTI.IS_COMPLETE,PPE.DEVICE_ID
  3      FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM PTI,GG_PATROL_EQUIPMENT PPE
  4     WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL 
  5       AND PTPD.PATROL_SUB_TYPE=1 
  6       AND PTPD.PATROL_TYPE=1     
  7       AND PTI.TASK_ID=PTPD.TASK_ID
  8       AND PTI.TASK_KIND=1 
  9     )
 10  SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(
 11  SELECT T.FROM_BUREAU_NO,PD.DEVICE_ID,PD.VOLTAGE_LEVEL,DECODE(T.IS_COMPLETE,3,1,0) REC FROM GG_DEVICE PD,STAT T  WHERE PD.DEVICE_ID=T.DEVICE_ID)
 12   GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL;
已用时间:  00: 01: 13.24   --非常慢
执行计划
----------------------------------------------------------
Plan hash value: 1646606100
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |   253 | 15939 |       | 73290   (1)| 00:14:40 |
|   1 |  HASH GROUP BY                  |                          |   253 | 15939 |       | 73290   (1)| 00:14:40 |
|*  2 |   HASH JOIN                     |                          |   107K|  6614K|  6592K| 73283   (1)| 00:14:40 |
|*  3 |    HASH JOIN                    |                          |   105K|  5354K|       |  1625   (1)| 00:00:20 |
|   4 |     NESTED LOOPS                |                          |   375 | 13125 |       |   806   (1)| 00:00:10 |
|*  5 |      TABLE ACCESS FULL          | GG_PTASK_PATROL_DETAIL |   375 |  7500 |       |    55   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| GG_PTASK_ITEM          |     1 |    15 |       |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PK_GG_PTASK_ITEM       |     1 |       |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL           | GG_PATROL_EQUIPMENT    |   192K|  3191K|       |   818   (1)| 00:00:10 |
|   9 |    TABLE ACCESS FULL            | GG_DEVICE              |  5226K|    54M|       | 65586   (1)| 00:13:08 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PD"."DEVICE_ID"=TO_NUMBER("PPE"."DEVICE_ID"))
   3 - access("PTPD"."PATROL_LINE_ID"="PPE"."PATROL_LINE_ID")
   5 - filter("PTPD"."PATROL_SUB_TYPE"=1 AND "PTPD"."PATROL_TYPE"=1 AND "PTPD"."PATROL_LINE_ID" IS NOT NULL)
   6 - filter("PTI"."TASK_KIND"=1)
   7 - access("PTI"."TASK_ID"="PTPD"."TASK_ID")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     302451  consistent gets
     176347  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

--开始使用利器
SQL> alter session set statistics_level=all;
SQL> set pagesize 100
SQL> SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(
  2   SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,
  3          DECODE(PTI.IS_COMPLETE,3,1,0) REC,PPE.DEVICE_ID,PD.VOLTAGE_LEVEL
  4      FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM PTI,GG_PATROL_EQUIPMENT PPE,GG_DEVICE PD
  5     WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL --关联路线
  6       AND PTPD.PATROL_SUB_TYPE=1 
  7       AND PTPD.PATROL_TYPE=1      
  8       AND PTI.TASK_ID=PTPD.TASK_ID
  9       AND PTI.TASK_KIND=1 
 10       AND PD.DEVICE_ID= PPE.DEVICE_ID)
 11       GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL;
FROM_BUREAU_NO VOLTAGE_LEVEL    ALLROWS   SUM(REC)
-------------- ------------- ---------- ----------
            13        110000         11          0
            13        220000          3          0


已用时间:  00: 00: 27.99
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ghhnk0gkny184, child number 1
-------------------------------------
SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(  SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,
DECODE(PTI.IS_COMPLETE,3,1,0) REC,PPE.DEVICE_ID,PD.VOLTAGE_LEVEL     FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM
PTI,GG_PATROL_EQUIPMENT PPE,GG_DEVICE PD    WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL       AND
PTPD.PATROL_SUB_TYPE=1       AND PTPD.PATROL_TYPE=1         AND PTI.TASK_ID=PTPD.TASK_ID      AND PTI.TASK_KIND=1       AND
PD.DEVICE_ID= PPE.DEVICE_ID)      GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL

Plan hash value: 1646606100

--E-Rows是oracle评估的条数,A-Rows是实际返回的条数,这就是统计信息有问题的证据。

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Starts| E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                  |                        |     1|    253 |      2|00:00:27.97 |     302K|  78286 |       |       |          |
|*  2 |   HASH JOIN                     |                        |     1|    104K|    14|00:00:27.97 |     302K|  78286 |  1114K|  1114K| 1616K (0)|
|*  3 |    HASH JOIN                    |                        |     1|    102K|    17|00:00:00.34 |    4314 |      0 |  1114K|  1114K|  441K (0)|
|   4 |     NESTED LOOPS                |                        |     1|    375|      2|00:00:00.01 |     409 |      0 |       |       |          |
|*  5 |      TABLE ACCESS FULL          | GG_PTASK_PATROL_DETAIL |     1|    375|    55|00:00:00.01 |     242 |      0 |       |       |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID| GG_PTASK_ITEM          |    55|      1 |      2 |00:00:00.01 |     167 |      0 |       |       |          |
|*  7 |       INDEX UNIQUE SCAN         | PK_GG_PTASK_ITEM       |    55|      1 |     55 |00:00:00.01 |     112 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL           | GG_PATROL_EQUIPMENT    |     1|    190K|    190K|00:00:00.01 |    3905 |      0 |       |       |          |
|   9 |    TABLE ACCESS FULL            | GG_DEVICE              |     1|   5226K|   5226K|00:00:20.91 |     298K|  78286 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - access("PD"."DEVICE_ID"="PPE"."DEVICE_ID")
   3 - access("PTPD"."PATROL_LINE_ID"="PPE"."PATROL_LINE_ID")
   5 - filter(("PTPD"."PATROL_SUB_TYPE"=1 AND "PTPD"."PATROL_TYPE"=1 AND "PTPD"."PATROL_LINE_ID" IS NOT NULL))
   6 - filter("PTI"."TASK_KIND"=1)
   7 - access("PTI"."TASK_ID"="PTPD"."TASK_ID")
已选择33行。


SQL>  EXEC dbms_stats.gather_table_stats(USER,'GG_PTASK_ITEM',CASCADE=>TRUE);
SQL>  EXEC dbms_stats.gather_table_stats(USER,'GG_PATROL_EQUIPMENT',CASCADE=>TRUE);
SQL>  EXEC dbms_stats.gather_table_stats(USER,'GG_DEVICE',CASCADE=>TRUE);
SQL>  EXEC dbms_stats.gather_table_stats(USER,'GG_pTASK_PATROL_DETAIL',CASCADE=>TRUE);


SQL> WITH STAT AS (
  2    SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,PTI.IS_COMPLETE,PPE.DEVICE_ID
  3      FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM PTI,GG_PATROL_EQUIPMENT PPE
  4     WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL 
  5       AND PTPD.PATROL_SUB_TYPE=1 
  6       AND PTPD.PATROL_TYPE=1      
  7       AND PTI.TASK_ID=PTPD.TASK_ID
  8       AND PTI.TASK_KIND=1 
  9     )
 10  SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(
 11  SELECT T.FROM_BUREAU_NO,PD.DEVICE_ID,PD.VOLTAGE_LEVEL,DECODE(T.IS_COMPLETE,3,1,0) REC
 12  FROM GG_DEVICE PD,STAT T  WHERE PD.DEVICE_ID=T.DEVICE_ID)
 13   GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL;
已用时间:  00: 00: 00.15  --收集统计信息后,性能提高了几十倍。
执行计划
----------------------------------------------------------
Plan hash value: 3921132085
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |   238 | 14042 | 21044   (1)| 00:04:13 |
|   1 |  HASH GROUP BY                  |                          |   238 | 14042 | 21044   (1)| 00:04:13 |
|   2 |   NESTED LOOPS                  |                          | 10132 |   583K| 21042   (1)| 00:04:13 |
|*  3 |    HASH JOIN                    |                          | 10020 |   469K|   988   (1)| 00:00:12 |
|   4 |     NESTED LOOPS                |                          |    36 |  1260 |   127   (0)| 00:00:02 |
|*  5 |      TABLE ACCESS FULL          | GG_PTASK_PATROL_DETAIL |    36 |   720 |    55   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| GG_PTASK_ITEM          |     1 |    15 |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PK_GG_PTASK_ITEM       |     1 |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL           | GG_PATROL_EQUIPMENT    |   192K|  2439K|   859   (1)| 00:00:11 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | GG_DEVICE              |     1 |    11 |     2   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN           | PK_GG_DEVICE           |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PTPD"."PATROL_LINE_ID"="PPE"."PATROL_LINE_ID")
   5 - filter("PTPD"."PATROL_SUB_TYPE"=1 AND "PTPD"."PATROL_TYPE"=1 AND "PTPD"."PATROL_LINE_ID" IS
              NOT NULL)
   6 - filter("PTI"."TASK_KIND"=1)
   7 - access("PTI"."TASK_ID"="PTPD"."TASK_ID")
  10 - access("PD"."DEVICE_ID"="PPE"."DEVICE_ID")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4364  consistent gets
          1  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

相关文章推荐

巧用工具快速定位问题sql

  • 2014年07月09日 17:13
  • 1.68MB
  • 下载

SQL Server统计信息:问题和解决方案

在网上看到一篇介绍使用统计信息出现的问题已经解决方案,感觉写的非常全面。在自己看的过程中顺便做了翻译。由于本人英文水平有限,可能中间有一些错误。如果有哪里有问题欢迎大家批评指正。建议英文好的直接看原文...

安装64位Oracle,使用Pl/SQL工具遇到的问题

1.登录PL/SQL Developer 这里省略Oracle数据库和PL/SQL Developer的安装步骤,注意在安装PL/SQL Developer软件时,不要安装在Program File...

SQL Server 数据库统计信息更新对性能优化的切身体会

年底前在开发一个SQL Server BI项目的过程中,我们测试SSIS包的时候,发现有好几天晚上取数的时间都超出了以前的 取数时间,刚开始我们以为是换了服务器,服务器硬件的性能有差异导致,后来我们仔...

SQL优化:基本概念(索引调优、统计信息、查询调整、资源调控)

管理索引时需要注意的问题: A、标识出索引碎片。指出了应该对哪些索引进行重新生成或重新组织。 B、显示出索引的使用情况。可以指出哪些索引没有使用过。   建立索引的基本规则: A、针对优先级高的、使用...
  • yupeigu
  • yupeigu
  • 2012年08月04日 11:35
  • 2231

SQL优化【基础06】 - 统计信息(直方图)

前言:从10开始默认优化器就是CBO模型,RBO已经靠边站了,CBO

SQL优化一例,由于动态性能视图统计信息不准确引起的查询缓慢

某库查询锁相关信息语句如下

统计分析用户信息量的工具Flurry的使用

Flurry是一家专门为移动应用提供数据统计和分析的公司。他们的数据统计分析SDK支持的平台包括iPhone, iPad, Android, Windows Phone, Java ME和BlackB...

使用sp_monitor 查看SQL Server统计信息

SQL Server提供了sp_monitor存储过程可以方便我们查看SQL Server性能统计信息,包括CPU/Network/IO,通过这些信息可以对自己的数据库性能状况有一个大致的了解。  ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL优化----如何使用工具快速诊断出统计信息有问题?
举报原因:
原因补充:

(最多只允许输入30个字)