Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to diagnose library cache: mutex X waiting in SQL". In daily operation, I believe many people have doubts about how to diagnose library cache: mutex X waiting in SQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to diagnose library cache: mutex X waiting in SQL". Next, please follow the editor to study!

What is library cache: mutex X?

This mechanism is used to protect memory structures, and there are many memory structures in library cache that need to be protected by library cache: mutex X.

Library cache is used to store parsed cursor-related memory structures.

Waiting for library cache: mutex X is the same as waiting for previous versions of latch:library cache. Library cache: mutex X can be caused by many factors, such as: (including application problems, high-version cursors caused by the execution plan cannot be shared, etc.), in essence, a process holds library cache: mutex X for too long, resulting in subsequent processes having to wait for the resource. If you wait on the latch or mutex of library cache, there is a lot of pressure during parsing, and the longer time it takes to parse SQL (due to the waiting of library cache's latch or mutex) will degrade the performance of the entire database.

Because there are a variety of reasons for library cache: mutex X, it is important to find the root cause of the problem before you can use the right solution.

What are the main reasons for library cache: mutex X waiting?

* A lot of hard parsing: too often hard parsing can lead to the wait.

* higher versions of cursors: when High version count occurs, a large number of subcursors need to be retrieved, causing the wait.

* cursor invalidation: cursor invalidation means that cursors stored in library cache are deleted from library cache because they are not available. Cursor invalidation refers to some change that causes the cursor in memory to no longer be valid. For example: the collection of statistical information of cursor-related objects; the modification of cursor association tables, views and other objects. A cursor failure will cause the following process to reload the cursor. When the cursor fails too much, it will cause 'library cache: mutex X' to wait.

* cursor reloading: cursor reloading means that the cursor already exists in library cache, but has been removed from the library cache when it is searched again (for example, due to memory pressure), and the cursor needs to be reparsed and loaded. Cursor reloading is not a good thing, it indicates that you are doing something you don't need to do, and if you set the library cache size properly, you can avoid cursor reloading. The cursor cannot be used by the process when the cursor is reloaded, which causes library cache: mutex X to wait.

* known Bug.

12C and later wait for event naming

Library cache: mutex X-used to protect handle.

Library cache: bucket mutex X-used to protect hash buckets in library cache.

Library cache: dependency mutex X-used to protect dependencies.

How to diagnose library cache: mutex X waiting?

Confirm if there are any changes:

a. Is the load increasing?

b. Are there any changes in applications, operating systems, and middleware?

The emerging trend of waiting:

a. Confirm whether the wait occurs at a fixed time of day?

b. Did you do something to trigger the wait?

Generate AWR and ADDM reports of the time the problem occurs, comparing with AWR and ADDM reports of baseline or normal time periods, whether there are changes in load, parameters, etc.

Sometimes using systemstate dump can be used to match known problems, for example, when no obvious SQL is found in AWR, capturing information about blocked and blocked processes through systemstate dump can help identify potential problems.

When systemstate dump is not suitable for collection (because it consumes more resources). The following SQL is executed periodically to determine which processes and SQL are waiting for library cache: mutex X.

Select s.sid, t.sql_text

From vvsql t

Where s.event like'% mutex%'

And t.sql_id = s.sql_id

How do I view the diagnostic information obtained?

Normally, we can see from AWR that library cache: mutex X is the TOP event:

Locate the hard parsed and high-version SQL, and click the "SQL Statistics" link under "Main Report".

Locate the SQL with high resolution:

Pay attention to the SQL with a high parsing ratio. Ideally, the parsing-to-execution ratio should be very low. If the ratio is high, it means that the cursor is not well used in the application. The cursor should be parsed and opened and should remain open. Confirm with the developer how to keep the cursor open to avoid repeated parsing the next time the SQL is executed.

The next step is to check the high version of SQL:

Possible solutions:

Check for the existence of high hard parsing, because hard parsing causes SQL AREA to reload, and the number of hard parsing is determined by load profile.

two。 Check the reload of SQL AREA as well:

If you have a high number of reloads on the SQL AREA, you need to check to see if the cursor is effectively shared (the number of reloads is cached in the shared pool, but is no longer in the shared pool when used). If the cursors have been effectively shared, you need to verify that the shared pool and sga_target are large enough, and if the shared pool is stressed and there is not enough space, then some cached cursors will be removed from the shared pool. If cursors are not fully shared, the shared pool will be filled with cursors that cannot be reused, squeezing out the reusable cursors out of the shared pool, causing them to be reloaded when the SQL is reexecuted. Cursors are fully shared, but too small shared pool space can also cause reusable cursors to be cleared, leading to hard parsing.

Check invalidations under "Library Cache Activity". If the invalidations is too high, you need to confirm whether there are a large number of DDL operations, such as truncate, drop, grants, dbms_stats, etc.

4. For 11G, confirm that cursor_sharing is not similar because it is no longer recommended and will cause mutex X to wait

At this point, the study on "how to diagnose library cache: mutex X waiting in SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.