Spotlight on Oracle 10.6 - Getting Started Guide

The estimate of the total time saved during the session by using cached results instead of re-executing the SQL queries.

If the amount of time saved is low, the result cache MAY not be performing efficiently. Information in other columns (for example, Hit Ratio, Row Count or SQL Statement) may provide more details.

Note: This value will not always show the true performance of the result cache. When result sets are flushed out of the result cache, statistics for those sets are also lost — the remaining statistics may no longer represent the true situation.

The total time taken to execute all occurrences of the submitted SQL query during the session, from the result cache or not.

The calculated percentage of time saved by executing the SQL query from the result cache against the total execution time for the query. (As with Saved Execution Time above, flushing result sets from the result cache may affect this value.)

The number of times that the SQL query has been executed from the result cache.

The number of times that the SQL query has been executed, including executions from the result cache.

The ratio of Find Count to Executions, expressed as a percentage.

A low value for the hit ratio indicates that the result sets stored in the result cache are not being used. You may want to reconsider the settings of the Oracle parameters for the result cache, and the use of result cache hints. (Flushing result sets from the result cache may affect this value.) Parameters and Hints

The number of buffer gets that were NOT performed because the wanted result set was cached. (Flushing result sets from the result cache may affect this value.)

For the specified SQL statement, this is the total number of buffer gets performed because the wanted result set was NOT cached.

The number of result sets stored in the result cache for the specified SQL statement.

A high number for this value may indicate that the relevant SQL statement uses bind variables, which allow executed SQL statements to be re-executed with different data without re-parsing the SQL statement. This may cause the cache to fill quickly with many result sets that have a low Hit Ratio.

The total number of blocks occupied in the result cache by the cached object. (For Block Size, see the Result Cache | Statistics Page.)

The total number of rows occupied in the result cache by the cached result. A high number here indicates either a large number of result sets, or result sets that are individually large.

The amount of time it took to build the cached result. The value in the Saved Execution Time column (see above) is estimated by comparing Build Time to the time needed to execute every occurrence of the SQL query outside the result cache.

The type of object associated with the executed SQL query:

• SQL — SQL statement.
• PL/SQL — Cached PL/SQL statement.

The type of entry in the result cache for the executed SQL query:

• Result — Result set.
• Dependency — A result set is valid and can be used only while the Oracle table or PL/SQL function on which it depends remains usable. (See Status below.) You can view Dependencies only in the Objects table.

The status of an object in the result cache:

• New — The result is still under construction.
• Published — The result is available for use.
• Bypass — The result will be bypassed.
• Expired — The result has exceeded its expiration time.
• Invalid — The result is no longer available for use.

The text of the SQL statement whose result set is in the result cache. When you investigate whether the result of a SQL statement should be stored in cache, consider:

• The size of the result set for the statement.
• The setting of the Oracle parameters used to assess the statement for addition to the cache. Parameters and Hints
• Whether the result cache hints in the statement (if any) are appropriate.
• How often the statement is executed.
• Whether the statement contains bind variables.
• The volatility of the tables queried by the SQL statement.

Note: If the SQL statement is still in cache, right-click on the row and choose SQL Details to view the Top SQL Page for the statement.