DBA Result Cache Objects and Dependencies – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Database Administration · all Database Administration reports
Overview
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.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Result Name contains, Show Dependencies, I have read the warning
Oracle EBS Tables Used
gv$result_cache_objects, gv$result_cache_dependency, dba_objects
Report Categories
Related Reports
DBA SGA+PGA Memory Configuration, DBA ORDS Configuration Validation, 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
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | DBA Result Cache Objects and Dependencies 23-Dec-2024 112646.xlsx |
| Blitz Report™ XML Import | DBA_Result_Cache_Objects_and_Dependencies.xml |
| SQL Source | DBA Result Cache Objects and Dependencies.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-result-cache-objects-and-dependencies/ |
Case Study & Technical Analysis
Abstract
The DBA Result Cache Objects and Dependencies report provides a deep dive into the contents of the Oracle Server Result Cache. It maps cached result sets to their underlying database objects and tracks dependencies. This visibility is crucial for diagnosing cache invalidation storms, where frequent updates to a dependency table cause the result cache to constantly flush and reload, potentially degrading performance instead of improving it.
Technical Analysis
Core Components
- Cache Inventory: Lists individual result sets currently stored in the result cache memory.
- Dependency Mapping: Links cached results to the specific database objects (tables, views) they rely on.
- Invalidation Metrics: Highlights objects that trigger frequent cache invalidations.
Critical Warning
Performance Impact: On Oracle Database versions prior to 12.2, querying v$result_cache_objects can acquire a latch that blocks other sessions from accessing the result cache. This can lead to a system-wide hang or severe contention on the ‘Result Cache: RC Latch’ wait event.
- Recommendation: Avoid running this report on production systems during peak business hours unless on a modern database version where this latching behavior is optimized.
Key Views
GV$RESULT_CACHE_OBJECTS: The primary view for listing cached artifacts.GV$RESULT_CACHE_DEPENDENCY: Resolves the many-to-many relationships between cached results and database objects.DBA_OBJECTS: Provides metadata (owner, object name, type) for the dependencies.
Operational Use Cases
- Cache Tuning: Identifying which queries are consuming the most cache space.
- Invalidation Analysis: Determining if a specific table is too volatile to be part of a result cached query.
- Application Debugging: Verifying that expected results are actually being cached by the application logic.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH