Wednesday, January 16, 2013

What other things can we look at in the result set cache ?



Well what items are in the result set cache ?

All docs are at

http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2141.htm#sthref3469


select id,type,status,namespace,name from v$result_cache_objects


        ID TYPE       STATUS    NAMES NAME
---------- ---------- --------- ----- --------------------------------------------------------------------------------------------------------------------------------
         3 Dependency Published       SH.CUSTOMERS
       110 Result     Published SQL   select cust_first_name,cust_last_name from customers where cust_id=104489
       109 Result     Published SQL   select cust_first_name,cust_last_name from customers where cust_id=103379
       108 Result     Published SQL   SELECT USERENV('SESSIONID') FROM DUAL
         2 Result     Published SQL   SELECT USERENV('SESSIONID') FROM DUAL
         1 Result     Published SQL   SELECT DECODE('A','A','1','2') FROM DUAL
         0 Result     Published SQL   SELECT USER FROM DUAL
         4 Result     Invalid   SQL   select cust_id from customers


So we can see some information. We can also change that slightly to see how many times it has been used 

  1* select id,type,pin_count,scan_count,block_count from v$result_cache_objects
SQL> /

        ID TYPE        PIN_COUNT SCAN_COUNT BLOCK_COUNT
---------- ---------- ---------- ---------- -----------
         3 Dependency          0          0           1
       110 Result              0          0           1
       109 Result              0          1           1
       108 Result              0          0           1
         2 Result              0          0           1
         1 Result              0          3           1
         0 Result              0          1           1
         4 Result              0          0         104

8 rows selected.

And this lets us look a little more detail into the usage of the cache. 


What if we want more summary information ? 

select * from V$RESULT_CACHE_STATISTICS
/

  1* select * from V$RESULT_CACHE_STATISTICS
SQL> /
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            2080
         3 Block Count Current            128
         4 Result Size Maximum (Blocks)   104
         5 Create Count Success           7
         6 Create Count Failure           0
         7 Find Count                     5
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              1
        12 Find Copy Count                5

12 rows selected.


OK All great. 



No comments: