| layout | default |
|---|---|
| title | DBA Result Cache Objects and Dependencies | Oracle EBS SQL Report |
| description | Shows result cache objects with the current number cached results and their dependency on objects causing the most frequent invalidations. Warning !!!… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, DBA, Result, Cache, Objects, gv$result_cache_objects, gv$result_cache_dependency, dba_objects |
| permalink | /DBA%20Result%20Cache%20Objects%20and%20Dependencies/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
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.
Show Dependencies, I have read the warning
gv$result_cache_objects, gv$result_cache_dependency, dba_objects
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
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.
| 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/ |
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.
- 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.
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.
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.
- 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.
- 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