Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 304

row_size_max rsm,
row_size_avg rsa,
space_overhead soh,
space_unused sun
FROM gv$result_cache_objects;
WHERE inst_id =&& instnum;
INT ID TYPE BLKCNT CLMCNT SCAN_COUNT RCNT RSM RSA SOH SUN
---- --- ---------- ------- ------- ---------- ----- ----- ----- ----- -----
2 2 Dependency 1 0 0 0 0 0 0 0
2 0 Dependency 1 0 0 0 0 0 0 0
2 1 Result 1 1 0 1 5 5 373 646
Step 5
From the previous output discussed in Step 2 of the workshop, you noticed that .036% of the dynamic memory section
of the shared pool was utilized by this operation. The following query provides a detailed report:
SELECT *
FROM gv$sgastat
WHERE pool = 'shared pool'
AND name LIKE 'Result%';
INST_ID POOL NAME BYTES CON_ID
------- ------------ ---------------------------------- ---------- ----------
1 shared pool Result Cache: Memory Mgr 208 0
1 shared pool Result Cache: Cache Mgr 256 0
1 shared pool Result Cache: 4088 0
1 shared pool Result Cache: RAC Cbk 6240 0
1 shared pool Result Cache: State Objs 2928 0
1 shared pool Result Cache: Bloom Fltr 2048 0
2 shared pool Result Cache: State Objs 2928 0
2 shared pool Result Cache: Bloom Fltr 2048 0
2 shared pool Result Cache 163952 0
2 shared pool Result Cache: Memory Mgr 208 0
2 shared pool Result Cache: Cache Mgr 256 0
2 shared pool Result Cache: RAC Cbk 6240 0
2 shared pool Result Cache: 680 0
As noticed from the discussions above, the behavior of result cache utilization and reusability of the results
captured from executing a function or a SQL script (in the previous workshop) are very similar. This is also true when
the PL/SQL function is executed on another instance in the RAC cluster. The results are transferred from the result
cache of the first instance where it was executed to the new instance.
When data changes in any of the underlying objects, it invalidates the result set (output below ID=6 ) on all the
instances in the cluster, indicating that subsequent execution of the same query will require fresh processing of data to
rebuild the result set and store the result in the result cache section of the shared pool.