This metric represents the library cache efficiency, as measured by the percentage of times the fully parsed or compiled representation of PL/SQL blocks and SQL statements are already in memory.
The shared pool is an area in the SGA that contains the library cache of shared SQL requests, the dictionary cache and the other cache structures that are specific to a particular instance configuration.
The shared pool mechanism can greatly reduce system resource consumption in at least three ways: Parse time is avoided if the SQL statement is already in the shared pool.
Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
I/O resources are saved, since dictionary elements that are in the shared pool do not require access.
If the shared pool is too small, users will consume additional resources to complete a database operation. For library cache access, the overhead is primarily the additional CPU resources required to re-parse the SQL statement.
This test checks the percentage of parse requests where cursor already in cache If the value is less than or equal to the threshold values specified by the threshold arguments, and the number of occurrences exceeds the value specified in the "Number of Occurrences" parameter, then a warning or critical alert is generated.
The rest of the information in this section is only valid for this metric when it appears in either the Enterprise Manager Grid Control or the Enterprise Manager Database Control (if applicable).
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
|
Target Version |
Evaluation and Collection Frequency |
Upload Frequency |
Operator |
Default Warning Threshold |
Default Critical Threshold |
Consecutive Number of Occurrences Preceding Notification |
Alert Text |
|
pre-10g |
Every 5 Minutes |
After Every Sample |
< |
Not Defined |
Not Defined |
2 |
Library cache hit ratio is %value%%%. |
|
Target Version |
Server Evaluation Frequency |
Collection Frequency |
Upload Frequency |
Operator |
Default Warning Threshold |
Default Critical Threshold |
Consecutive Number of Occurrences Preceding Notification |
Alert Text |
|
10.1.0.x; 10.2.0.x; 11.1.0.x; 11.2.0.x |
Every Minute |
Not Defined |
After Every Sample |
< |
Not Defined |
Not Defined |
2 |
Generated By Database Server |
(DeltaPinHits / DeltaPins) * 100 where:
DeltaPinHits: difference in 'select sum(pinhits) from v$librarycache' between sample end and start
DeltaPins: difference in 'select sum(pins) from v$librarycache' between sample end and start
The Top Sessions page sorted by Hard Parses lists the sessions incurring the most hard parses. Hard parses occur when the server parses a query and cannot find an exact match for the query in the library cache. You can avoid hard parses by sharing SQL statements efficiently. The use of bind variables instead of literals in queries is one method to increase sharing.
By showing you which sessions are incurring the most hard parses, this page can identify the application or programs that are the best candidates for SQL rewrites.
Also, examine SQL statements that can be modified to optimize shared SQL pool memory use and avoid unnecessary statement reparsing. This type of problem is commonly caused when similar SQL statements are written which differ in space, case, or some combination of the two. You may also consider using bind variables rather than explicitly specified constants in your statements whenever possible.
The SHARED_POOL_SIZE initialization parameter controls the total size of the shared pool. Consider increasing the SHARED_POOL_SIZE to decrease the frequency in which SQL requests are being flushed from the shared pool to make room for new requests.
To take advantage of the additional memory available for shared SQL areas, you may also need to increase the number of cursors permitted per session. You can increase this limit by increasing the value of the initialization parameter OPEN_CURSORS.
Related Topics
About Alerts
About the Metric Detail Page
Editing Thresholds
Understanding Line Charts
Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.