关闭

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

1023人阅读 评论(0) 收藏 举报
分类:

   下列的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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:883122次
    • 积分:15214
    • 等级:
    • 排名:第732名
    • 原创:640篇
    • 转载:108篇
    • 译文:4篇
    • 评论:56条
    最新评论