“Latch free” and “library cache: mutex X” wait events

 

Latch free and library cache: mutex X wait events can be seen for the corresponding SQL statements in intensive delete and insert operations in databases.

If high levels of Latch free and library cache: mutex X waiting events are seen in ADDM and AWR reports, this may be due to the relevant table indexes ‘parallel degree’ values are too high.

When the DBA_HIST_ACTIVE_SESS_HISTORY table is checked, the “library cache: mutex X” wait event will be displayed extensively. The following query can determine which queries cause this wait event.

or

You can also use the following query to see what the working query is.

You can check the parallel degree values of the indexes of the relavant tables in the queries with the following query.

As a condition , you can also add tables returned from the previous query.

Then the relevant indexes should be changed to noparallel. You can do this as follows.

Create the necessary alter index commands with the following query.

Run the generated queries with sqlplus.

You should also check the parallel_max_server parameter. If the value of this parameter is high, it can cause the same wait event.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *