ANBOB™

最近一套oracle 19c的环境出现较高的cursor: mutex x等待，查询SQL ID有近4000+ version count, 从12c “_cursor_obsolete_threshold “参数值达8k，所以这么高的version也未过期，cursor: mutex x是因为version count高引起，哪什么引起high version count呢?MOS上有一篇比输全面的doc. 通过 v$sql_shared_cursor可以发现主要是USER_BIND_PEEK_MISMATCH和ROLL_INVALID_MISMATCH。

ROLL_INVALID_MISMATCH 是什么意思？

ROLL_INVALID_MISMATCH 是滚动游标失效。在早期版本中，每当您收集表上的统计数据时，使用该表的每个游标都会被标记为立即重新优化。这可能会对您的系统造成巨大的即时 CPU 消耗。因此，我们对其进行了更改，以便在收集统计数据后，依赖游标会“缓慢”地标记为需要重新优化，以分散负载。因此，您的子游标之一会受到依赖对象之一的新统计数据的影响。

SQL> select DBMS_STATS.GET_PARAM('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

dbms_stats包的NO_INVALIDATE 值：
TRUE………………….: Does not invalidate the cursor.
FALSE…………………: Invalidates the cursor immediatelly, same as for verion <= 9i
AUTO_INVALIDATE.: Default value which means Oracle will invalidate over time.

AUTO_INVALIDATE 如何工作？

在之前《Alert: Oracle 19c DDL “COMMENT on Table” sql cursor no invalidation(deferred invalidation增强)》也提到过.可以控制在统计信息相关改变后SQL CURSOR是否立即失效还在指定的时间窗口内滚动失效，滚动失效的窗口是有参数“_optimizer_invalidation_period”，单位秒，默认5小时。当超过这个时间，SQL会强制重新硬解析，生成一个新的SQL cursor，并标记与之前Cursor不能共享的原因为ROLL_INVALID_MISMATCH, 假设我们在今天早上 6 点收集了新的统计数据，那么引用具有新统计数据的对象的所有当前游标都将被标记为滚动失效。 下次需要解析这个标记为rolling invalidation的cursor游标时，会设置一个时间戳，其取值为_optimizer_invalidation_period定义的最大值范围内的一个随机数, 为了分散这些 invalidation的游标，防止出现硬解析风暴。假设下次执行是在早上 8 点。时间戳可以是解析时间（早上 8 点）加上隐藏参数 _optimizer_invalidation_period 的值中的随机数值。假设生成的时间戳是上午 9:35.对于在时间戳早上 9:35 之前发生的任何解析也是如此，它将重用当前光标。上午 9:35 之后的执行将使旧游标失效，并使用新统计信息对游标进行硬解析。新子游标被标记为 ROLL_INVALID_MISMATCH，以解释为什么我们无法共享前一个子游标.

如果旧游标从共享池中过期，它将使用新的统计数据进行新的硬解析，并且上述场景无效。

test case 可以参考Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

select reason from v$sql_shared_cursor where sql_id ='<sql_id>

<ChildNode><ChildNumber>0</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
<ChildNode><ChildNumber>1</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
<ChildNode><ChildNumber>2</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win

SQL >select SQL_ID, CHILD_NUMBER, ROLL_INVALID_MISMATCH   
     from v$sql_shared_cursor where sql_id='12wm4h2j0a2rp';

SQL_ID        CHILD_NUMBER R
------------- ------------ -
12wm4h2j0a2rp            0 N
12wm4h2j0a2rp            1 Y
12wm4h2j0a2rp            2 Y
...
12wm4h2j0a2rp           14 Y

select child_number,parse_calls,executions,parse_calls,loads,invalidations,first_load_time,last_load_time,last_active_time 
from v$sql where sql_id='xxxx';

我们可以减少与 ROLL_INVALID_MISMATCH 相关的版本数量吗？

该问题可能是因为改变统计信息,导致SQL硬解析出现峰值，或使用了DDL 延迟时效cursor特性。如果该问题不是很明显可以忽略，否则可以减少统计信息变动，如Lock table statistics或修改参数，使sql cursor立即invalid。 或可以改小“_optimizer_invalidation_period”值，尽快invalid, 关于sql cursor还有另外一个个数限制，也能减少cursor: mutex 的发生，下调“_cursor_obsolete_threshold “到200-500。

但有时调2个参数并不是并能生效，如19.12后的bug 33299877 .

SQL不能共享的原因:

 select * from
  (select sql_id, nonshared_reason, count(*) from v$sql_shared_cursor
  unpivot
  (nonshared_value for nonshared_reason in (
  UNBOUND_CURSOR as 'UNBOUND_CURSOR',
  SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH',
  OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH',
  OUTLINE_MISMATCH as 'OUTLINE_MISMATCH',
  STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH',
  LITERAL_MISMATCH as 'LITERAL_MISMATCH',
  FORCE_HARD_PARSE as 'FORCE_HARD_PARSE',
  EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR',
  BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH',
  PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH',
  INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH',
  SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH',
  TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH',
  AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH',
  BIND_MISMATCH as 'BIND_MISMATCH',
  DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH',
  LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH',
  TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH',
  BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE',
  INSUFF_PRIVS as 'INSUFF_PRIVS',
  INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM',
  REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH',
  LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH',
  INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH',
  OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH',
  EDITION_MISMATCH as 'EDITION_MISMATCH',
  MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH',
  USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH',
  TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH',
  NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH',
  FLASHBACK_CURSOR as 'FLASHBACK_CURSOR',
  ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION',
  PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH',
  TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR',
  DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH',
  LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY',
  DIFF_CALL_DURN as 'DIFF_CALL_DURN',
  BIND_UACS_DIFF as 'BIND_UACS_DIFF',
  PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF',
  CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH',
  STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH',
  CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH',
  PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH',
  TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH',
  MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH',
  BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH',
  MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH',
  ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH',
  OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH',
  PX_MISMATCH as 'PX_MISMATCH',
  MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH',
  FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH',
  LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH',
  PLSQL_DEBUG as 'PLSQL_DEBUG',
  LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS',
  ACL_MISMATCH as 'ACL_MISMATCH',
  FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH',
  LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED',
  REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH',
  LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED',
  HASH_MATCH_FAILED as 'HASH_MATCH_FAILED',
  PURGED_CURSOR as 'PURGED_CURSOR',
  BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE',
  USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS'
  ))
  where nonshared_value = 'Y'
  group by sql_id, nonshared_reason
  )
where sql_id = '&sqlid' ;

References:

Performance Tunning: cursor: mutex X & cursor: mutex S high wait after 12.2.0.1

library cache: mutex X等待事件, blocker session on cpu

Alert: Oracle 19c DDL “COMMENT on Table” sql cursor no invalidation(deferred invalidation增强)

由于 ROLL_INVALID_MISMATCH，游标未共享

打赏

微信扫一扫，打赏作者吧～