Spotlight on Oracle 10.9 - Getting Started Guide

Result Cache Find/Create Ratio Alarm

When Oracle executes SQL queries, the result cache stores the result sets of specified queries in shared memory for later use. The Result Cache Find/Create Ratio alarm fires when the rate of find operations on the result cache drops below the rate of create operations, and indicates that the contents of the result cache are being under-used.

Note: When the Result Cache Find/Create Ratio alarm is raised, use the SQL and Application Workload | Result Cache Page to diagnose the problem.

About Finds and Creates

The Creates metric is the rate at which result sets are stored in the result cache. The Finds metric is the rate at which result sets are found in the result cache. A high find rate indicates that the result cache is working properly.

The absolute rates of finds and creates is less important than whether the rate is lower than the rate of finds. If the rate of finds is lower than the rate of creates, perhaps the wrong result sets are being stored.

Result cache process

The result cache process can be summarized as follows:

1. A SQL query is submitted for execution.
2. Oracle uses a combination of defined parameters and SQL hints to determine if the results of the query should be added to the result cache.
3. If so, Oracle checks whether the result cache already has a copy of the result set.
4. If Oracle finds the result set, it re-uses it, and avoids most of the cost of re-executing that query.
5. If not, Oracle executes the query and creates a new entry in the result cache.
6. As the result cache fills, Oracle deletes result sets that are old or no longer valid.

Related Topics

SQL & Application Workload Drilldown | Result Cache Page | Parameters and Hints