Chat now with support
Chat with Support

Spotlight on Oracle 10.7 - 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

Contents Page

Note: Available for Oracle 11g and later.

To open the Contents page

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

  2. Click SQL & Application Workload | Result Cache Page | Statistics.

Contents and Objects

Contents and Objects show the same type of statistics for items in the result cache.

Contents

Statistics for the result sets that were found in the Result Cache when the table was last refreshed.

Objects

Statistics for the result sets that were found in the Result Cache, grouped according to the Oracle tables and PL/SQL functions on which the result sets depend.

Note: Items that are marked INVALID in the Contents table do not appear in the Objects table.

In both the Contents and Objects tables, some important statistics are:

  • Saved Execution Time and Execution Time % Saved: Use these to see if the result cache is working efficiently.
  • Hit Ratio: Check if the correct result sets are being stored in the cache.
  • Result Sets: This may indicate queries that use bind variables.
  • Row Count: This may suggest result sets that should be excluded from the cache.
  • SQL Statement: Shows the queries that are using the result cache.

Result Cache Contents Grid

Note: The tables are automatically populated the first time you open the Result Cache page. To refresh and filter the contents of the tables, click Set SQL Cache Criteria. Result Cache Selection Criteria Dialog

Column Description

Saved Execution Time (s)

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.

Execution Time (s)

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

Execution Time % Saved

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

Find Count

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

Executions

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

Hit Ratio

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

Saved Buffer Gets

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

Buffer Gets

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

Result Sets

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.

Block Count

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

Row Count

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.

Build Time (s)

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.

Namespace

The type of object associated with the executed SQL query:

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

Type

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.

Status

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.

SQL Statement

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.

Force Matching Signature

Spotlight used this metric to group result sets and permit access to the Top SQL drilldown page for cached SQL statements.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating