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.
1 2 3 4 | SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE event = 'library cache: mutex X' ORDER BY sample_time DESC; |
or
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
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.
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.
1 2 3 4 5 | SQL> show parameter parallel_max_servers NAME TYPE VALUE ------------------------------ ----------- ----- parallel_max_servers integer 1 |