Oracle 11.2.0.1 Result Cache 測試 - 9

ID     NAME              VALUE 
---  -----------------  --------
1 Block Size (Bytes) 1024    
2 Block Count Maximum 62944    
3 Block Count Current 12160   
4 Result Size Maximum (Blocks)  3147 
5 Create Count Success 55426  
6 Create Count Failure 5 
7 Find Count 44256   --- 可以發現 Find Count 值增加了 1 。
8 Invalidation Count 32711 
9 Delete Count Invalid 48076  
10 Delete Count Valid 0  
11 Hash Chain Length  1  

再次執行一次同樣的SQL, 發現v$result_cache_statistics中的Find Count再次加1 . 
這說明在資料沒有更新的情況下，同樣的SQL查詢result是可以命中result cache的，
每命中一次就 Found Count + 1 ,  而其他統計值沒有發生變化。 

SQL> update  apps.test_result_cache set  object_name = 'TEST_R_C'  where  object_id >=1 and object_id<=2000 ; 
SQL> commit;  

SQL> select /*+ RESULT_CACHE */ object_id, object_name,object_type,status from apps.test_result_cache  where mod(object_id,3)=1;
66661 rows selected.
Elapsed: 00:00:01.05

Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT   |                            |  2000 |   185K|   772  (2)|
|   1 |  RESULT CACHE      | gs6bnqjyx46aq8m1yu8htw20cc |       |       |     |
|*  2 |   TABLE ACCESS FULL| TEST_RESULT_CACHE          |  2000 |   185K|   772  (2)|
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MOD("OBJECT_ID",3)=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=4; dependencies=(APPS.TEST_RESULT_CACHE); name="select /*+ R
ESULT_CACHE */ object_id, object_name,object_type,status from apps.test_result_c
ache  where mod(object_id,3)=1"
Note
-----
   - 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7173  consistent gets    ----  沒有命中result cache , 需重新構造 
          0  physical reads
       1800  redo size