Chat now with support
Chat with Support

Spotlight on Oracle 10.5 - 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 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.

Rate of Finds > Rate of Creates

The result cache is working properly.

Rate of Finds < Rate of Creates

Indicates the wrong result sets are being stored. Consider one of the following solutions:

  • Increase the size of the result cache.
  • Set the Oracle RESULT_CACHE_MODE parameter to MANUAL. Parameters and Hints
  • Do not use the result_cache hint in your SQL code.

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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating