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

dbtut March 11, 2019 ORACLE

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.

  SELECT *
    FROM DBA_HIST_ACTIVE_SESS_HISTORY
   WHERE event = 'library cache: mutex X'
ORDER BY sample_time DESC;

SELECT *

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE event = 'library cache: mutex X'

ORDER BY sample_time DESC;

select sql_id, session_id,in_parse, in_sql_execution
    from gv$active_session_history
    where sample_time between to_date('16052017 10:57:00', 'ddmmyyyy hh24:mi:ss'
                      and     to_date('16052017 11:30:00', 'ddmmyyyy hh24:mi:ss'
    and event = 'library cache: mutex X'
    order by sql_id;

select sql_id, session_id,in_parse, in_sql_execution

from gv$active_session_history

where sample_time between to_date('16052017 10:57:00', 'ddmmyyyy hh24:mi:ss'

and to_date('16052017 11:30:00', 'ddmmyyyy hh24:mi:ss'

and event = 'library cache: mutex X'

order by sql_id;

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

WITH got_my_sql_id
     AS (  SELECT sql_id, COUNT (1)
             FROM gv$active_session_history
            WHERE     sample_time BETWEEN TO_DATE ('06062017 13:00:00',
                                                   'ddmmyyyy hh24:mi:ss')
                                      AND TO_DATE ('07062017 09:30:00',
                                                   'ddmmyyyy hh24:mi:ss')
                  AND event = 'library cache: mutex X'
         GROUP BY sql_id)
  SELECT DISTINCT sql_id, sql_text
    FROM v$sql b
   WHERE EXISTS
            (SELECT NULL
               FROM got_my_sql_id a
              WHERE a.sql_id = b.sql_id)
ORDER BY sql_id;

WITH got_my_sql_id

AS ( SELECT sql_id, COUNT (1)

FROM gv$active_session_history

WHERE sample_time BETWEEN TO_DATE ('06062017 13:00:00',

'ddmmyyyy hh24:mi:ss')

AND TO_DATE ('07062017 09:30:00',

'ddmmyyyy hh24:mi:ss')

AND event = 'library cache: mutex X'

GROUP BY sql_id)

SELECT DISTINCT sql_id, sql_text

FROM v$sql b

WHERE EXISTS

(SELECT NULL

FROM got_my_sql_id a

WHERE a.sql_id = b.sql_id)

ORDER BY sql_id;

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

select * from dba_indexes where degree > '1';

1	select * from dba_indexes where degree > '1';

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.

select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where degree > '1'

1	select 'alter index '\|\|owner\|\|'.'\|\|index_name\|\|' noparallel;' from dba_indexes where degree > '1'

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.

SQL> show parameter parallel_max_servers
 
NAME                           TYPE        VALUE
------------------------------ ----------- -----
parallel_max_servers           integer     1

SQL> show parameter parallel_max_servers

NAME TYPE VALUE

------------------------------ ----------- -----

parallel_max_servers integer 1

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

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

About dbtut

Leave a Reply Cancel reply

Categories