-------------- ------------- ---------- ----------
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 | | | |
|* 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_PATR