Oracle数据库性能优化指标-Library Cache Hit(%)
Description
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.
Metric Summary
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.
Data Source
(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
User Action
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.
相关推荐
- **正常值**：`Library Cache Hit Ratio`≥90%，`Library Cache Reload Ratio`≤1%（理想情况为0）。 - **优化策略**：增大`shared_pool_size`初始化参数，以扩大library cache的容量。 **3. Dictionary Cache ...
- Library Hit%：库缓存命中率 - Soft Parse%：软解析比率 - Execute to Parse%：执行到解析比率 - Latch Hit%：锁存命中率 - Parse CPU to Parse Elapsed%：解析CPU与解析时间比率 - Non-Parse CPU：非解析...
1. **检查Get Hit Ratio (获取命中率)**：首先，检查`v$librarycache`中的SQL Area的`get hit ratio`是否超过90%。若低于90%，则需要进一步分析并优化应用代码。 ```sql SELECT get_hit_ratio FROM v$library...
SQL> SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE; 通常情况下，Library Cache 命中率应该在 98% 以上。如果命中率太低，需要考虑加大共享池、绑定变量、修改 cursor_sharing 等参数来优化性能。 二、共享...
5. **共享区命中率(Library Hit%)**：表示SQL在共享区的命中率，应维持在95%以上，否则可能需要增大shared_pool_size，优化cursor_sharing参数，或使用绑定变量。 6. **软解析的百分比(Soft Parse %)**：软解析的...
可以通过查询`v$librarycache`来监控。如果`reloads/pins`比率大于1%，则可能需要增大`shared_pool_size`，或者检查SQL语句是否引用了无效的对象。 2. **Shared Pool预留大小**：预留大小一般为总大小的10%，但不应...
可以使用`v$librarycache`视图来监控这个比率。 4. **Reloads/Pins比率**：这个比率应小于1%，否则可能需要增大`shared_pool_size`。如果比率过高，可能是空间不足或SQL引用的对象无效。 5. **Shared Pool ...
- **Library Hit Ratio**：又称Library Cache Hit Ratio，反映了从Library Cache中获取SQL语句的比例。高Library Hit Ratio意味着更少的硬解析，提高了SQL执行效率。 - **最佳实践**：根据应用特点调整Buffer Cache...
5. **共享区命中率(Library Hit %)**：此指标衡量SQL在共享池中的复用率，应保持在95%以上。如果低，可能需要增大`shared_pool_size`，使用绑定变量或调整`cursor_sharing`参数。 6. **软解析的百分比(Soft Parse %...
- 计算命中率：通过监控`V$SYSSTAT`中的`library cache hit`和`data dictionary cache hit`，可以评估当前共享池的效率。 - 查询空闲空间：检查`V$SHARED_POOL_STAT`中的`free list bytes`，以确定是否需要增大...
- `hit` 变量表示读操作时是否命中CACHE。 - `high_order` 表示地址的高位部分。 - `low_order` 表示地址的低位部分。 - `Mark` 数组用于存储CACHE中各块的标记。 在读操作时，程序会通过比较主存地址的高位部分和`...
通过监控相关的性能指标如`library_cache_hit`和`parse_count`，可以评估Sharedpool的使用效率，并据此调整`shared_pool_size`。 总的来说，理解并优化Sharedpool是提升Oracle数据库性能的关键。通过对Sharedpool的...
- **Library Hit Ratio (Library Cache Hit Ratio)**: 库缓存命中率，反映了从 **Library Cache** 中重用SQL和PL/SQL代码的比例。这两个指标以及其他关键指标可以帮助评估数据库实例操作的效率。需要注意的是，并...
FROM v$librarycache; ``` - 如果Library Cache的命中率低于90%，则可能需要增加共享池的大小。 - 检查Data Dictionary Cache的使用率： ```sql SELECT (SUM(gets - getmisses - usage - fixed)) / SUM(gets) ...
通过监控`v$librarycache`视图可以查看SQL区域的命中率(`gethitratio`)是否足够高（通常建议达到90%以上），如果命中率过低，则需要调整`shared_pool_size`大小。 - **命令**: ```sql SELECT gethitratio FROM ...
- **Get Hit Ratio**：确保`v$librarycache`中`sqlarea`的get hit ratio超过90%。可以通过查询`SELECT get_hit_ratio FROM v$library_cache WHERE namespace = 'sqlarea';`来检查。如果未达到90%，则应检查应用程序...
通过v$library_cache视图可以监控库缓存的使用情况，其中Get Hit Ratio（获取命中率）是衡量共享池性能的一个重要指标。通常而言，对于SQL区域的Get Hit Ratio，理想值应该接近90%。如果低于这个值，可能意味着需要...
1. **检查`v$librarycache`表中的`get hit ratio`**: 如果这个比率低于90%，则需要进一步检查应用程序代码，寻找可优化的地方来提高代码效率。可以通过以下SQL查询来检查该比率： ```sql SELECT get_hit_ratio ...