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:
Post a Comment