Tag Archives: waits

Cursor Mutex S wait event and too many child cursors in Oracle

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
from
(
select count(*) as cursors, ssc.sql_id
from v$sql_shared_cursor ssc
group by ssc.sql_id
order by cursors desc
) a,
(
select sa.sql_id, sa.sql_text, sa.parsing_schema_name from v$sqlarea sa
) b
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.

Solution

1. Set the following paremeter may fixed the issue:

alter system set "_cursor_obsolete_threshold"=1024

Temporary solution
Scheduled a job in dbms_scheduler, running every 1 minute, executing dbms_shared_pool.purge for flushing the sql id cursors:

DECLARE
SQ_ADD VARCHAR2(100) := '';
BEGIN
execute immediate 'select address from v$sqlarea where sql_id = ''someSQLIDfoo''' into SQ_ADD;
dbms_shared_pool.purge (SQ_ADD||',123454321','C');
END;

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.

Advertisements