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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-1433547/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-1433547/