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