DBA Result Cache Objects and Dependencies

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Shows result cache objects with the current number of cached results, the number of times these results were found and reused (scan count) and their dependency on objects causing the most frequent invalidations.

Warning !!!
Don't run this on a prod system during business hours as prior to DB version 12.2, selecting from v$result_cache_objects apparently blocks all result cache objects (s ...  Shows result cache objects with the current number of cached results, the number of times these results were found and reused (scan count) and their dependency on objects causing the most frequent invalidations.

Warning !!!
Don't run this on a prod system during business hours as prior to DB version 12.2, selecting from v$result_cache_objects apparently blocks all result cache objects (see note 2143739.1, section 4.).
You may end up with all server sessions waiting on 'latch free' for 'Result Cache: RC Latch' while the report is running.   more 
select distinct
grco.results_count,
grco.name result_name,
grco.total_scan_count scan_count,
&object_columns
grco.cache_id
from
(
select
count(*) over (partition by grco.cache_id) results_count,
sum(grco.scan_count) over (partition by grco.cache_id) total_scan_count,
grco.*
from
gv$result_cache_objects grco
where
grco.status='Published' and
grco.type='Result'
) grco,
(
select
grcd.inst_id,
grcd.result_id,
grcd.depend_id,
do.object_type,
nvl(do.owner||nvl2(do.object_id,'.',null)||do.object_name,grco2.name) object,
grco2.invalidations
from
gv$result_cache_dependency grcd,
dba_objects do,
(select grco2.* from gv$result_cache_objects grco2) grco2
where
1=1 and
'&show_dependencies'='Y' and
grcd.object_no=do.object_id(+) and
grcd.inst_id=grco2.inst_id(+) and
grcd.depend_id=grco2.id(+)
) x
where
2=2 and
grco.inst_id=x.inst_id(+) and
grco.id=x.result_id(+)
order by
&order_by1
results_count desc,
grco.name
&order_by2
Parameter NameSQL textValidation
Result Name contains
lower(grco.name) like '%'||lower(:result_name)||'%'
Char
Show Dependencies
Y
LOV
I have read the warning
:warning=:warning
LOV
Download
Related Blitz Reports
DBA SGA+PGA Memory Configuration DBA Result Cache Statistics DBA Blocking Sessions DBA SGA Blocking Session Summary DBA AWR Blocking Session Summary DBA AWR Active Session History DBA SGA Buffer Cache Object Usage DBA SGA Active Session History DBA ORDS Configuration Validation
Blitz Report™