Cursor Mutex S wait event and too many child cursors in Oracle
Mutexes are a lighter-weight and more granular concurrency mechanism. If one session is changing a data structure in memory,
then another session must wait to acquire the mutex before it can make a similar change.
The wait event: “Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache. Library cache mutex – X is held in exclusive mode by a session
It is involved, as soon as two sessions try to (hard/soft) parse the same statement in sense of SQL_IDs.
Cursor: mutex S wait occurs when Oracle is serializing parsing of multiple SQL statements.
We upgraded the database on 12.2 Environment from 11g, we are start getting
Oracle Wait: “”Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache.
You can check the child cursor having mainly update or insert then select in version count in AWR report
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class cursor: mutex X 5,698,887 512.1K 89.86ms 79.6 Concurrency DB CPU 22.1K 3.4 cursor: mutex S 1,616,430 10.7K 6.62ms 1.7 Concurrency log file sync 546,786 2030.3 3.71ms .3 Commit
It may be caused of version SQL present in database: ( check awr report for following section)
SQL ordered by Version Count
Version Count Executions SQL Id SQL Module SQL Text 4,096 crxk85q87pug9 SELECT COUNT(:"SYS_B_0") FROM ... 1,426 9gm72hfpbn449 SELECT entity_no, date_capture... 1,086 6rkf3uzawjtwk 659 118 79msc2b6tcv7a StmtCOM64.exe SELECT status, page_count, phy... 608 9ktt2cdn2cndn SELECT COUNT(:"SYS_B_0") FROM ... 261 1y4f4wtu63797 WITH MONITOR_DATA AS (SELECT I... 161 33 c7v3v03hs7528 w3wp.exe SELECT IC.Tran.*, rowidtochar(...
With peak hours, lot of update statement run the child cursor is increasing and causing performance issue.
On checking with v$sql immediately showed an select statement that has more than 1000 child cursors for the same SQL_ID,
it was mostly the culprit sql because creating new child cursors means expensive hard parsing,
and searching the list of existing children means CPU load as well.
You can check the child cursor count from the following queries:
Check Number of child cursor present in database:
select a.cursors, a.sql_id,b.sql_text
select count(*) as cursors, ssc.sql_id
from v$sql_shared_cursor ssc
group by ssc.sql_id
order by cursors desc
select sa.sql_id, sa.sql_text, sa.parsing_schema_name from v$sqlarea sa
where a.sql_id=b.sql_id and b.parsing_schema_name = 'SCOTT';
The number of child cursors and the appearance of the “Cursor: mutex S” wait event is closely related,
a stable and well-working workaround was to flush out the particular cursor or flush out the shared pool
but shared pool flush sometime is expensive in Production environment.
1. Set the following paremeter may fixed the issue:
alter system set "_cursor_obsolete_threshold"=1024
Scheduled a job in dbms_scheduler, running every 1 minute, executing dbms_shared_pool.purge for flushing the sql id cursors:
SQ_ADD VARCHAR2(100) := '';
execute immediate 'select address from v$sqlarea where sql_id = ''someSQLIDfoo''' into SQ_ADD;
3. Flush the shared pool if you are having large no of sql having multiple version count.
alter system flush shared_pool;
In my case 12.2 oracle, optimizer parameter disable fixed the issue. It reduced the version count sqls in awr report from next day.
May be the issue with optimizer adaptive plans in 12c, when i updated this parameter to false, then my version count is going down.
adaptive cursor sharing is playing.
alter system set optimizer_adaptive_plans=false scope=both;
Explain for child cursor:
On tracing the sql queries using bind variable at level 10046 traces, level 12.
found that the bind varialbe is treated as different in some case NULL value, some as varchar2 or in some as number data type.
if you have 10 column in where clause using 10 bind variable then it may lead to lot of production of child plans.