Chat now with support
Chat with Support

Spotlight on Oracle 10.9 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Spotlight on MySQL Troubleshooting: Connection Problems

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

  1. Select the Spotlight on Oracle RAC connection in the Spotlight Browser.

  2. 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.

  • If the cache is too small, consider increasing its size.
  • If too many result sets are being cached, consider not caching as many statements — do not use the result_cache hint in your SQL code, or set the RESULT_CACHE_MODE parameter to MANUAL. Parameters and Hints

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:

  • Look at the Contents table (in Result Cache Information, below) to find statements that can be excluded from the cache via Result Cache parameters and hints.
  • Reduce the size of the result cache to exclude result sets that are too large.

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.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating