DBA Result Cache Objects and Invalidations

Description

Categories: Enginatics, Kcapps
Repository: Github

Shows result cache objects with the current number cached results 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 session ... more

DBA Result Cache Objects and Invalidations and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

select
y.result_name,
y.results_count,
y.cache_id
&object_columns
from
(
select distinct
count(*) over (partition by grco.cache_id) results_count,
grco.name result_name,
x.object_type,
x.owner,
x.object_name,
x.invalidations,
max(x.invalidations) over (partition by grco.cache_id) max_invalidations,
grco.cache_id
from
gv$result_cache_objects grco,
(
select
grcd.result_id,
do.object_type,
do.owner,
do.object_name,
grco0.invalidations
from
gv$result_cache_dependency grcd,
gv$result_cache_objects grco0,
dba_objects do
where
1=1 and
'&show_invalidation_dependencies'='Y' and
grcd.depend_id=grco0.id and
grco0.invalidations>0 and
grco0.type='Dependency' and
grcd.object_no=do.object_id
) x
where
grco.status='Published' and
grco.type='Result' and
grco.id=x.result_id(+)
) y
order by
y.max_invalidations desc nulls last,
y.results_count desc nulls last,
y.result_name,
y.invalidations desc nulls last,
y.object_type,
y.owner,
y.object_name