 1. Around 9:55 database started performance bottleneck on 23rd 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';
  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);
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 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 |