Troubleshooting mutex X/S Due to High Version Count

1. Around 9:55 database started performance bottleneck on 23^rd of December due to soft parsing in library cache (library cache:mutex X). We see sqlid 4h5gmrxn4dkm3 was causing the spikes.

select * from (

select p1, sql_id,

count(*),

(ratio_to_report(count(*)) over ())*100 pct

from dba_hist_active_sess_history

where event='library cache: mutex X'

and snap_id between 16971 and 16974

and dbid = 1727009368

group by p1, sql_id

order by count(*) desc)

where rownum <= 10;

2. Now Question is why soft parsing of sqlid 4h5gmrxn4dkm3 spikes and makes bottleneck as its normal behavior in oracle? From the bottle neck picture, its visible system is loaded with heavy mutex X/S wait and this is for high number of cursors (version count). I see version count for sqlid 4h5gmrxn4dkm3 is 952.

· Generated lot’s of child cursor for one SQL ID 4h5gmrxn4dkm3

· Accessing them in Parallel

o Delay to create new children (X)

o Delay to select good child (S)

select VERSION_COUNT from v$sqlarea where sql_id='4h5gmrxn4dkm3';

VERSION_COUNT

952

3. Now question is why so many version count for sqlid 4h5gmrxn4dkm3? After investigation, I see this is because of bind mismatch.

select version_count,address,hash_value,parsing_schema_name,reason from (

select

address,''

||decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR')

||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH')

||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH')

||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH')

||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH')

||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH')

||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR')

||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH')

||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH')

||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH')

||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH')

||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH')

||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH')

||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH')

||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH')

||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH')

||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH')

||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS')

||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM')

||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH')

||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH')

||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH')

||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH')

||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH')

||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH')

||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH')

||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH')

||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR')

||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION')

||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR')

||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH')

||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY')

||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN')

||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF')

||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF')

||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH')

||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH')

||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH')

||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH')

||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH')

||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH')

||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH')

||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH')

||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH')

||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH')

||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH')

||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH')

||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH')

reason

from

v$sql_shared_cursor

group by

address

) join v$sqlarea using(address) where sql_id='4h5gmrxn4dkm3'

order by version_count desc,address;

Summary of Root cause:

Root-caused by invalidated child cursor(s) => Too many cursor objects in Library Cache for bind mismatch

One example reason for cursor: mutex S/X is Application uses jdbc setter methods improperly on INTEGER column (=2)

• setNUMBER(2) => Bind Var. is NUMBER

• setNULL(2) => Bind Var. is VARCHAR2 Issues

= BIND MISMATCH

Another example: Due to change of variable length

variable a varchar2(100);

select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD

variable a varchar2(400);

select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

Solution:

Quick Fix: Frequently flush this cursor with dbms_shared_pool.purge (look out for new parsing issues

Permanent Solution:

· Check how the application does handle bind variables Avoid BIND MISMATCH at (nearly) any cost

• Reduce the number of cursor versions below 100 More will lead to overhead

o Diagnosis Help:

§ • 10046 Level 12 session trace (=sql_trace event)

§ • v$sql_shared_cursor plus cursortrace [296377.1]

Work Around: Since the application doesn't have any control on how the JDBC driver handles the bind variables and its datatype, workaround was to restrict the number of child cursors a parent can have before it becomes absolute. This workaround sets the threshold on the number of child cursors to 200, which reduces the mutex problem:

_cursor_features_enabled=1026

event="106001 trace name context forever, level 200"

By default in Oracle 11g, there is no limit on maximum number of child cursors which a parent cursor can have. Due to very high version count, the child cursor count was very high. So we limited the number of max child cursor a parent can have to 200 as shown above. To get more information about it please check Doc ID 1469726.1