<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
SELECT *\n FROM DBA_HIST_ACTIVE_SESS_HISTORY\n WHERE event = 'library cache: mutex X'\nORDER BY sample_time DESC;<\/pre>\nor<\/p>\n
select sql_id, session_id,in_parse, in_sql_execution\n from gv$active_session_history\n where sample_time between to_date('16052017 10:57:00', 'ddmmyyyy hh24:mi:ss'\n and to_date('16052017 11:30:00', 'ddmmyyyy hh24:mi:ss'\n and event = 'library cache: mutex X'\n order by sql_id;<\/pre>\nYou can also use the following query to see what the working query is.<\/p>\n
WITH got_my_sql_id\n AS ( SELECT sql_id, COUNT (1)\n FROM gv$active_session_history\n WHERE sample_time BETWEEN TO_DATE ('06062017 13:00:00',\n 'ddmmyyyy hh24:mi:ss')\n AND TO_DATE ('07062017 09:30:00',\n 'ddmmyyyy hh24:mi:ss')\n AND event = 'library cache: mutex X'\n GROUP BY sql_id)\n SELECT DISTINCT sql_id, sql_text\n FROM v$sql b\n WHERE EXISTS\n (SELECT NULL\n FROM got_my_sql_id a\n WHERE a.sql_id = b.sql_id)\nORDER BY sql_id;<\/pre>\nYou can check the parallel degree values of the indexes of the relavant tables in the queries with the following query.<\/p>\n
select * from dba_indexes where degree > '1';<\/pre>\nAs a condition , you can also add tables returned from the previous query.<\/p>\n
Then the relevant indexes should be changed to noparallel. You can do this as follows.<\/p>\n
Create the necessary alter index commands with the following query.<\/p>\n
select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where degree > '1'<\/pre>\nRun the generated queries with sqlplus.<\/p>\n
You should also check the parallel_max_server parameter. If the value of this parameter is high, it can cause the same wait event.<\/p>\n
SQL> show parameter parallel_max_servers\n \nNAME TYPE VALUE\n------------------------------ ----------- -----\nparallel_max_servers integer 1<\/pre>\n\n<\/div>\n