and I.occure_time >= to_date('2016-06-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and I.recover_time <= to_date('2016-06-30 17:20:08', 'yyyy-MM-dd hh24:mi:ss')
同一个SQL 在上面的日期那里 1号到30号 查询 比30号到30的 还要快好多,执行计划也不同
但是 consistent gets 要高好多倍? 搞不懂 ,谁给分析分析。。。。。坐等
统计信息
----------------------------------------------------------
1 recursive calls
3 db block gets
1509921 consistent gets
3 physical reads
96 redo size
673 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
-------
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 202 | 82416 | 366 (3)| 00:00:05 | | |
| 1 | SORT ORDER BY | | 202 | 82416 | 366 (3)| 00:00:05 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 202 | 82416 | 365 (3)| 00:00:05 | | |
| 3 | VIEW | | 8 | 1168 | 10 (20)| 00:00:01 | | |
|* 4 | HASH JOIN OUTER | | 8 | 912 | 10 (20)| 00:00:01 | | |
| 5 | MERGE JOIN OUTER | | 8 | 736 | 6 (17)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| C_CLUSTER | 5 | 195 | 2 (0)| 00:00:01 | | |
| 7 | INDEX FULL SCAN | PK_C_CLUSTER | 6 | | 1 (0)| 00:00:01 | | |
|* 8 | SORT JOIN | | 10 | 530 | 4 (25)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL | C_CLUST_NODE | 10 | 530 | 3 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | P_CODE | 5 | 110 | 3 (0)| 00:00:01 | | |
|* 11 | HASH JOIN RIGHT OUTER | | 202 | 52924 | 355 (2)| 00:00:05 | | |
| 12 | TABLE ACCESS FULL | S_RESOURCE_INSTANCE_INFO | 246 | 12300 | 3 (0)| 00:00:01 | | |
| 13 | VIEW | | 202 | 42824 | 352 (2)| 00:00:05 | | |
| 14 | HASH UNIQUE | | 202 | 46258 | 352 (2)| 00:00:05 | | |
|* 15 | HASH JOIN | | 202 | 46258 | 351 (2)| 00:00:05 | | |
|* 16 | HASH JOIN | | 202 | 30502 | 342 (2)| 00:00:05 | | |
| 17 | VIEW | | 1199 | 50358 | 176 (2)| 00:00:03 | | |
| 18 | HASH GROUP BY | | 1199 | 88726 | 176 (2)| 00:00:03 | | |
|* 19 | HASH JOIN OUTER | | 25675 | 1855K| 174 (1)| 00:00:03 | | |
| 20 | TABLE ACCESS FULL | S_WARNING_HANDLE_INFO | 1199 | 39567 | 8 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE ALL | | 25761 | 1031K| 166 (1)| 00:00:02 | 1 | 10 |
| 22 | TABLE ACCESS FULL | S_WARNING_INFO | 25761 | 1031K| 166 (1)| 00:00:02 | 1 | 10 |
| 23 | PARTITION RANGE ALL | | 4335 | 461K| 166 (1)| 00:00:02 | 1 | 10 |
|* 24 | TABLE ACCESS FULL | S_WARNING_INFO | 4335 | 461K| 166 (1)| 00:00:02 | 1 | 10 |
|* 25 | TABLE ACCESS FULL | S_WARNING_HANDLE_INFO | 1199 | 93522 | 8 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AA"."NODEIP"(+)="R"."RESOURCE_IP")
4 - access("C"."CLUST_TYPE"="P"."CODE_VALUE"(+))
6 - filter("C"."CLUST_TYPE"<>'0')
8 - access("C"."CLUST_ID"="D"."CLUST_ID"(+))
filter("C"."CLUST_ID"="D"."CLUST_ID"(+))
10 - filter("P"."CODE_SORT_ID"(+)='100006' AND "P"."CODE_VALUE"(+)<>'0')
11 - access("R"."INSTANCE_ID"(+)="I"."INSTANCE_ID")
15 - access("H"."WARNING_ID"="W1"."WARNING_ID")
16 - access("W1"."OCCURE_TIME"="W2"."OCCURE_TIME" AND "W1"."WARNING_ID"="W2"."WARNING_ID")
19 - access("H"."WARNING_ID"="W"."WARNING_ID"(+))
24 - filter(("W2"."WARNING_LEVEL"='3' OR "W2"."WARNING_CONTENT" LIKE '%内存利用率%' OR "W2"."WARNING_CONTENT" LIKE
'%CPU%') AND "W2"."RECOVER_TIME"<=TO_DATE(' 2016-06-30 17:20:08', 'syyyy-mm-dd hh24:mi:ss'))
25 - filter("H"."OCCURE_TIME">=TO_DATE(' 2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16027 consistent gets
0 physical reads
72 redo size
28775 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
260 rows processed