DBA Result Cache Objects and Dependencies – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Overview
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 sessions waiting on ‘latch free’ for ‘Result Cache: RC Latch’ while the report is running.
Report Parameters
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 Result Cache Statistics, DBA Blocking Sessions, DBA AWR Blocking Session Summary, DBA AWR Active Session History, DBA SGA Buffer Cache Object Usage, DBA Blitz Report ORDS Configuration, DBA SGA Active Session History, DBA ORDS Configuration Validation
Running This SQL Without Blitz Report
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
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 |
| Full SQL 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
© 2026 Enginatics