Note: Available for Oracle 11g and later.
To open the Objects page
Select the Spotlight on Oracle connection in the Spotlight Browser.
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:
Result Cache Dependencies 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 Cache | Statistics 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:
|
Type |
The type of entry in the result cache for the executed SQL query:
|
Status |
The status of an object in the result cache:
|
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:
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. |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center