 |  |
In this Document
| Purpose |
| Troubleshooting Steps |
| wait: library cache lock |
| Cause Identified: Unshared SQL Due to Literals |
| Solution Identified: Rewrite the SQL to use bind values |
| Solution Identified: Use the CURSOR_SHARING initialization parameter |
| Cause Identified: Shared SQL being aged out |
| Solution Identified: Increase the size of the shared pool |
| Solution Identified: 10g+: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size |
| Solution Identified: Keep ("pin") frequently used large PL/SQL and cursor objects in the shared pool |
| Cause Identified: Library cache object Invalidations |
| Solution Identified: Do not perform DDL operations during busy periods |
| Solution Identified: Do not collect optimizer statistics during busy periods |
| Solution Identified: Do not perform TRUNCATE operations during busy periods |
| Cause Identified: Objects being compiled across sessions |
| Solution Identified: Avoid compiling objects in different sessions at the same time or during busy times |
| Cause Identified: Auditing is turned on |
| Solution Identified: Evaluate the need to audit |
| Cause Identified: Unshared SQL in a RAC environment |
| Solution Identified: Rewrite the SQL to use bind values |
| Solution Identified: Use the CURSOR_SHARING initialization parameter |
| Cause Identified: Extensive use of row level triggers |
| Solution Identified: Evaluate the need for the row trigger |
| Cause Identified: Excessive Amount of Child Cursors |
| Solution Identified: Inappropriate use of parameter CURSOR_SHARING set to SIMILAR |
| References |
APPLIES TO:
Oracle Database - Personal Edition - Version 8.1.7.4 and laterOracle Database - Standard Edition - Version 8.1.7.4 and later
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
PURPOSE
Troubleshoot waits for 'library cache lock'.
NOTE: The information in this article is taken from the Oracle Performacne Diagnostic Guide (OPDG):
This article also contains similar diagnostics for other wait events.
TROUBLESHOOTING STEPS
wait: library cache lock
The library cache lock controls the concurrency between clients of the library cache by acquiring a lock on the object handle so that either:
- One client can prevent other clients from accessing the same object
- The client can maintain a dependency for a long time (no other client can change the object).
This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).
What to look for:
- TKProf:
- Overall wait event summary for non-recursive and recursive statements shows significant amount of time for library cache lock waits.
- AWR or statspack:
- Significant waits for library cache lock
Cause Identified: Unshared SQL Due to Literals
SQL statements are using literal values where a bind value could have been used. The literal values cause the statement to be unshared and will force a hard parse.
Cause Justification
TKProf :
- Use the report sorted by elapsed parse time
- Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
- Examine the statements that are being hard parsed and look for the presence of literal values.
Solution Identified: Rewrite the SQL to use bind values
Rewriting the SQL to use bind values will allow the statement to be reused when specific values in the statement change but the overall statement is the same. This is the best way to promote sharing of SQL statements in the library cache.
Effort Details : Medium or high effort; rewriting statements requires a change to the application but the change is rather trivial.
Risk Details : Medium risk; the use of bind values could lead to worse execution plans for some statements. The statements modified to use binds values should be thoroughly tested to avoid regressing the statement's performance.
Solution Implementation
See the documents below.
Troubleshooting
Document 296377.1 Troubleshooting: High Version Count Issues
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: Use the CURSOR_SHARING initialization parameter
The CURSOR_SHARING parameter will substitute literal values with bind values in a statement automatically. The settings for this parameter are:
- EXACT: Leave the statement as it was written with literals (default value)
- FORCE: Substitute all literals with binds (as much as possible)
- SIMILAR: Substitute literals with binds only if the query's execution plan won't change (i.e., safe literal replacement)
In general, most OLTP apps that use equality predicates will see little change to their execution plans, but the effects of these parameters should be tested in your application.
These parameters can be set at the session level to further contain their effects - this is the preferred way to use them to minimize widespread changes.
Effort Details : Low effort; an init.ora / spfile change. In the worst case it may require a LOGON trigger to set it for a session.
Risk Details : Medium risk; the use of bind values could lead to worse execution plans for some statements. Risk can be mitigated by using SIMILAR instead of FORCE but this may not make enough statements shareable.
Solution Implementation
See the documents below.
Reference
Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note
Troubleshooting
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
Document 296377.1 Troubleshooting: High Version Count Issues
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Shared SQL being aged out
The shared pool is too small and is causing many statements that could be shared to age out of the library cache and later reloaded. Each reload requires a hard parse and impacts the CPU and latches.
Cause Justification
TKProf:
- Use the report sorted by elapsed parse time
- Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
- Examine the statements that are being hard parsed and look for the ABSENCE of literal values, this means these statements could have been shared but weren't (this is not entirely reliable since you could have statements that use binds but will not be executed again).
AWR or statspack reports:
- Library Cache statistics section shows that reloads are high (usually several thousand per hour) and little or no invalidations are seen
- The "% SQL with executions>1" is over 60%, meaning statements are being shared
Solution Identified: Increase the size of the shared pool
Increasing the shared pool size will reduce the need to age out statements that could be shared.
Effort Details : Low effort; an init.ora / spfile change.
Risk Details : Low risk; increasing the size of the shared pool is not risky unless:
- There are many unshared statements due to literals. For more details, see:
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
- The machine doesn't have enough physical memory and starts swapping
Document 17094.1 TECH: Unix Virtual Memory, Paging & Swapping explained
Verify the above points before changing the size of the shared pool.
Solution Implementation
See the documents below.
Documentation
Reference: SHARED_POOL_SIZE Parameter
Reference: SHARED_POOL_SIZE and Automatic Storage Management
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: 10g+: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
ASMM will automate memory sizing for the shared pool to ensure an optimal amount is available. You will need to set a reasonable value for SGA_MAX_SIZE and SGA_TARGET to enable ASMM.
Effort Details : Low effort; an init.ora / spfile change.
Risk Details: Low risk; ASMM will ensure sufficient memory is available.
Solution Implementation
See the documents below.
Documentation
Concepts: Automatic Shared Memory Management
Admin: Using Automatic Shared Memory Management
Performance Tuning: Tuning the Shared Pool and the Large Pool
Notes
Document 257643.1 Oracle Database Automated SGA Memory Tuning
How-To
Document 270935.1 Shared pool sizing
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: Keep ("pin") frequently used large PL/SQL and cursor objects in the shared pool
Use the DBMS_SHARED_POOL.KEEP() procedure to mark large, frequently used PL/SQL and SQL objects in the shared pool and avoid them being aged out. This will reduce reloads and fragmentation since the object doesn't need to keep re-entering the shared pool over and over.
Effort Details : Medium effort; need to identify which objects should be kept and then run a procedure to keep them.
Risk Details: Medium risk; if you aren't careful in keeping these objects, you may keep too many of them and cause ORA-4031 errors.
Solution Implementation
See the documents below.
Documentation
Performance Tuning: Keeping Large Objects to Prevent Aging
PL/SQL DBMS_SHARED_POOL
How-To
Document 101627.1 How to Automate Pinning Objects in Shared Pool at Database Startup
Document 305529.1 How To Use SYS.DBMS_SHARED_POOL In a PL/SQL Stored procedure To Pin objects in Oracle's Shared Pool.
Reference
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Library cache object Invalidations
When objects (like tables or views) are altered via DDL or collecting statistics, the cursors that depend on them are invalidated. This will cause the cursor to be hard parsed when it is executed again and will impact CPU and latches.
Cause Justification
TKProf:
- Use the report sorted by elapsed parse time
- Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
- Examine the statements that are being hard parsed and look for the ABSENCE of literal values, this means these statements could have been shared but weren't (this is not entirely reliable since you could have statements that use binds but will not be executed again).
AWR or statspack reports:
- Library Cache statistics section shows that reloads are high (usually several thousand per hour) and invalidations are high
- The "% SQL with executions>1" is over 60%, meaning statements are being shared
- Check the Dictionary Statistics section of the report and look for non-zero values in the Modification Requests column, meaning that DDL occurred on some objects.
Solution Identified: Do not perform DDL operations during busy periods
DDL will often cause library cache objects to be invalidated and this could cascade to many different dependent objects like cursors. Invalidations have a large impact on the library cache, shared pool, row cache, and CPU since they will likely require many hard parses to occur at the same time.
Effort Details : Low effort; defer the DDL to a quiet time.
Risk Details: Low risk; may involve some downtime.
Solution Implementation
Not Applicable. Simply schedule DDL during maintenance or low activity periods.
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: Do not collect optimizer statistics during busy periods
Collecting statistics (using ANALYZE or DBMS_STATS) will cause library cache objects to be invalidated and this could cascade to many different dependent objects like cursors. Invalidations have a large impact on the library cache, shared pool, row cache, and CPU since they will likely require many hard parses to occur at the same time.
For some database versions, the DBMS_STATS procedure allows give you the option of not invalidating objects (see the "no_invalidate" option).
Effort Details : Low effort; defer the gathering of statistics to a quiet time. In 10g, you have a choice of whether or not to invalidate objects after gathering statistics.
Risk Details: Low risk; defer the gathering of statistics to a quiet time.
Solution Implementation
The document links below shows how to specify statistics collection without causing invalidations.
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: Do not perform TRUNCATE operations during busy periods
See the document below:
Effort Details : Low effort; defer the DDL to a quiet time.
Risk Details: Low risk; may involve some downtime.
Solution Implementation
See documents below:
Notes
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Objects being compiled across sessions
One or more sessions are compiling objects (typically PL/SQL) while another session wants to pin the same object prior to executing or compiling it. One or more sessions will wait on library cache pin in Share mode (if it just wants to execute it) or eXclusive mode (if it want to compile/change the object).
Cause Justification
TKProf:
- library cache pin waits and / or library cache pin waits
- Statement is compiling or executing PL/SQL
Solution Identified: Avoid compiling objects in different sessions at the same time or during busy times
Do not compile interdependent objects across concurrent sessions or during peak usage.
The HangAnalyze command can usually help identify the blockers, waiters, and the SQL which is causing the waits (see the "Hang / Locking tab > Issue Identification > Data Collection" for more information).
Effort Details : Low effort; requires some thought on how and when to recompile objects.
Risk Details: Low risk.
Solution Implementation
Schedule and/or sequence the recompilation to avoid conflicts.
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Auditing is turned on
Auditing will increase the need to acquire library cache locks and potentially increase contention for them. This is especially true in a RAC environment where the library cache locks become database-wide (across all instances).
Cause Justification
AWR / Statspack:
- library cache lockwaits
- audit_trail parameter is set to something other than "none"
Solution Identified: Evaluate the need to audit
Consider disabling auditing if it is not absolutely necessary.
Effort Details : Low effort; initialization parameter change
Risk Details: Low risk.
Solution Implementation
See the documents below.
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Unshared SQL in a RAC environment
Library cache locks waits may occur in RAC environments when applications are not sharing SQL. In single-instance environments, library cache and shared pool latch contention is typically the symptom for unshared SQL. However, in RAC, the main symptom may belibrary cache lock contention.
Cause Justification
RAC environment
TKProf:
- Many statements are hard parsed
- library cache lock waits occur as part of a hard parse
AWR / Statspack:
- library cache lockwaits
- Low percentage for "% SQL with executions>1" (less than 60%)
- soft parse ratio is below 80%
Solution Identified: Rewrite the SQL to use bind values
Rewriting the SQL to use bind values will allow the statement to be reused when specific values in the statement change but the overall statement is the same. This is the best way to promote sharing of SQL statements in the library cache.
Effort Details : Medium or high effort; rewriting statements requires a change to the application but the change is rather trivial.
Risk Details: Medium risk; the use of bind values could lead to worse execution plans for some statements. The statements modified to use binds values should be thoroughly tested to avoid regressing the statement's performance.
Solution Implementation
See the documents below.
Troubleshooting
Document 296377.1 Troubleshooting: High Version Count Issues
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Solution Identified: Use the CURSOR_SHARING initialization parameter
The CURSOR_SHARING parameter will substitute literal values with bind values in a statement automatically. The settings for this parameter are:
- EXACT: Leave the statement as it was written with literals (default value)
- FORCE: Substitute all literals with binds (as much as possible)
- SIMILAR: Substitute literals with binds only if the query's execution plan won't change (i.e., safe literal replacement)
In general, most OLTP apps that use equality predicates will see little change to their execution plans, but the effects of these parameters should be tested in your application.
These parameters can be set at the session level to further contain their effects - this is the preferred way to use them to minimize widespread changes.
Effort Details : Low effort; an init.ora / spfile change. In the worst case it may require a LOGON trigger to set it for a session.
Risk Details: Medium risk; the use of bind values could lead to worse execution plans for some statements. Risk can be mitigated by using SIMILAR instead of FORCE but this may not make enough statements shareable.
Solution Implementation
See the documents below.
Reference
Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note
Troubleshooting
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
Document 296377.1 Troubleshooting: High Version Count Issues
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Extensive use of row level triggers
When row level triggers are fired frequently, higher than usual library cache activity may occur, because of the need to check if mutating tables are being read. During trigger execution, it is possible that the application tries to read mutating tables, i.e., tables that are in the process of being modified by the statement that caused the trigger to fire. As this may lead to inconsistencies, it is not allowed, and the application should receive the error ORA-4091. The mechanism to detect this error involves one library cache lock acquisition per table referenced in each select statement executed.
The extent of the problem depends on how many times the row triggers fire rather than on the number of row triggers have been created (i.e., one trigger that fires 10000 times will cause more problems than 100 triggers that fire once).
Cause Justification
TKProf:
- Many statements are hard parsed
- library cache lockwaits
- evidence of a row level trigger firing (maybe some recursive SQL related to a trigger)
Solution Identified: Evaluate the need for the row trigger
Sometimes row triggers aren't needed to accomplish the functionality. Consider if there is an alternative.
Effort Details : Medium effort; may require application and schema changes
Risk Details: Medium risk. If the application and schema changes, there is a possibility that some adverse effect will be introduced. Thorough testing will be needed.
Solution Implementation
Requires understanding the application and how row-level triggers are used. See the documents below for reference information.
Documentation
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
Cause Identified: Excessive Amount of Child Cursors
A large number of child cursors are being created for some SQL statements. This activity is causing contention among various sessions that are creating child cursors concurrently or with other sessions that also need similar resources (latches and mutexes).
Cause Justification
AWR / Statspack reports; look in the "SQL ordered by Version Count" section. If there are any SQL statements with more than 500 versions, then this problem is likely to be occurring. Alternatively, you can query V$SQLAREA to look for any SQL with version_count greater than 500.
Query V$SQL_SHARED_CURSOR to see the reasons why SQL isn't being shared.
Solution Identified: Inappropriate use of parameter CURSOR_SHARING set to SIMILAR
The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the SQL area potentially deteriorating execution plans.
One of the cursor sharing criteria when literal replacement is enabled with CURSOR_SHARING as SIMILAR is that bind value should match initial bind value if the execution plan is going to change depending on the value of the literal. The reason for this is we might get a sub-optimal plan if we use the same cursor. This would typically happen when, depending on the value of the literal, the optimizer is going to chose a different plan. For example, if we have a predicate with " > ", then each execution with different bind values would result in a new child cursor because that would ensure that the plan didn't change (a range predicate influences cost and plans), if this was an equality predicate, we would always share the same child cursor.
Avoiding the use of CURSOR_SHARING set to SIMILAR entails either rewriting the SQL in the application so that it uses bind values and still gets a good plan (hints, profiles, or outlines may be needed), or using CURSOR_SHARING set to FORCE which will avoid generating child cursors but can cause plans to be sub-optimal.
Effort Details : Depends on the change made. Changing the CURSOR_SHARING initialization parameter to FORCE is easy; changing the application to use binds will take more effort.
Risk Details : Depends on the change made. Changing the CURSOR_SHARING initialization parameter to FORCE is risky if done at the database instance level, but less risky at the session level. Changing the application SQL is not as risky since only the single statement is affected.
Solution Implementation
See documents below:
Reference
Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note
Troubleshooting
Implementation Verification
Implement the solution and determine if the performance improves. If performance does not improve, examine the following:
- Review other possible reasons
- Verify that the data collection was done properly
- Verify the problem statement
If you would like to log a service request, a test case would be helpful at this stage.
If you have more questions, please ask our community:
https://community.oracle.com/community/support/oracle_database/database_tuning
扫一扫
3744
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
