Result Cache Page
Note: Available for Oracle 11g and later.
When Oracle executes SQL queries, the Result Set Cache can store the result sets of specified queries in shared memory. When one of those specified queries is re-run, Oracle can re-use the result set, and so avoid most of the cost of executing that query — which can include overhead such as parse time, logical reads, physical reads, and cache contention such as latches. The result cache may not be an effective tool in all cases. It is most useful where:
- Multiple SQL queries do NOT have overlapping result sets.
- The tables queried are read-only or change infrequently.
- The contents of the result cache change slowly enough to avoid contention on the Result Cache latch.
- Result sets are small enough to fit in the cache without forcing most existing cache entries out of the cache.
To open the Result Cache page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Result Cache.
Result Cache Statistics chart
Result Cache Statistics shows the movement of result sets into and out of the result cache, and provides some information on whether the result sets stored there are being used effectively.
| Data | Description |
|---|---|
|
Creates |
The rate at which result sets are stored in the result cache. The absolute rate of creates is less important than whether the rate is lower than the rate of finds. A rate of creates HIGHER than that of finds may indicate that the wrong result sets are being stored, and raises the Result Cache Find/Create Ratio Alarm. A high rate of creates may also cause a high rate of deletes, where result sets are being removed before they can be used effectively. |
|
Finds |
The rate at which result sets are found in the result cache. A high find rate indicates that the result cache is working properly. |
|
Invalidations |
The rate at which result sets in the cache are marked as being out-of-date (and therefore unusable). A high rate of invalidations suggests that the tables being queried are updated too often — the result cache is most effective when queried tables are read-only or do not change often. |
|
Deletes |
The rate at which result sets are deleted from the result cache. A high rate of deletes indicates that either the result cache is too small, or that too many result sets are being cached.
|
Result Cache Latch chart
Result Cache Latch shows two series of data that together indicate the activity of the Result Cache latch.
|
Data |
Description |
|---|---|
| Latch Wait |
There is no wait event specifically for the Result Cache latch. The total time taken by the Latch Wait event (shown in the chart as a percentage of the total DB time) includes the Result Cache latch as well as other latches. A high Latch Wait time is undesirable, but does not by itself indicate a specific problem with the Results Cache latch. For more information, look at the Result Cache Sleep data on the chart. |
|
Result Cache Sleep |
The Result Cache Sleep graph shows the rate of latch sleeps for the result cache latch as a percentage of the latch sleep rate for ALL latches. When this percentage is high, the Result Cache latch is probably responsible for the Latch Wait time. When the percentage is too high Spotlight raises the Result Cache Latch Sleep Ratio Alarm. To reduce contention on the Result Cache latch, you can look at reducing the number of result sets added to the cache:
|
Result Cache Information
The tables in the three Result Cache Information sub-pages provide details of what was in the result cache the last time Spotlight refreshed those pages.