Reports2017-11-18T12:27:27+00:00

DBA Result Cache Objects and Invalidations

Description
Categories: DBA, Enginatics
Shows result cache objects with the current number cached results and their dependency on objects causing the most frequent invalidations.

select
y.result_name,
y.results_count
&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
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
'&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

Parameter Name SQL text Validation
Show Invalidation Dependencies
,y.object_type,
y.owner,
y.object_name,
y.invalidations
Show Invalidation Dependencies
Y
LOV Oracle

By continuing to use the site, you agree to the use of cookies. Accept