DBA Result Cache Objects and Invalidations
Description
Categories: Enginatics, Kcapps
Repository: Github
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
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
Run
DBA Result Cache Objects and Invalidations and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Show Invalidation Dependencies |
|
LOV Oracle | |
I have read the warning |
|
LOV |