合 【MOS】数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1) (Doc ID 2542447.1)
Tags： OracleMos版本数高(High Version Count)cursor：mutex XLANGUAGE_MISMATCH
👉 本文共约1186个字，系统预计阅读时间或需5分钟。
Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1)
数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1)
数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1)
适用于:
Oracle Database - Enterprise Edition - 版本 12.1.0.2 和更高版本
Generic (Platform Independent)
症状
- 由于 'cursor：mutex X' 争用，数据库挂起
- 由于 LANGUAGE_MISMATCH 导致 SQL 语句的 High Version Count
- 刷新共享池可以暂时缓解互斥锁争用
- Version Count 显示的值超过了_cursor_obsolete_threshold 的值
更改
从应用程序更改 NLS 设置。
由于 NLS 设置不同而导致 LANGUAGE_MISMATCH 的示例代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | SQL> conn / as sysdba Connected. SQL> VAR B1 VARCHAR2(32); SQL> EXEC :B1 := ''; SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L ------------- ---------------- ---------------- ------------ - a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N SQL> alter session set nls_language='DUTCH'; Sessie is gewijzigd. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L ------------- ---------------- ---------------- ------------ - a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N a9x5sbz88kmfh 0000000067968598 000000006B8CAC30 1 Y SQL> alter session set nls_language='FRENCH'; Session modifiee. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> alter session set nls_territory='BELGIUM'; Session modifiee. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L ------------- ---------------- ---------------- ------------ - a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N a9x5sbz88kmfh 0000000067968598 000000006B8CAC30 1 Y a9x5sbz88kmfh 0000000067968598 000000006B8CAAB0 2 Y a9x5sbz88kmfh 0000000067968598 000000006F29A308 3 Y |
使用 CURSOR_SHARING = FORCE 时未发现语言不匹配，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | SQL> conn / as sysdba SQL> alter session set cursor_sharing=force; Session altered. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> alter session set nls_language='FRENCH'; Session modifiee. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> alter session set nls_territory='BELGIUM'; Session altered. SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) AT TIME ZONE 'GMT' FROM SYS.DUAL; FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO --------------------------------------------------------------------------- SQL> select sql_id, open_versions, sql_text from v$sql where sql_text like 'SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1%'; SQL_ID OPEN_VERSIONS SQL_TEXT ------------- ------------- -------------------------------------------------------------------------------- 32mcj6mff82su 1 SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,:"SYS_B_0"),:"SYS_B_1"),TO_CHAR(SYSTIME STAMP, :"SYS_B_2")) AT TIME ZONE :"SYS_B_3" FROM SYS.DUAL SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='32mcj6mff82su'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L ------------- ---------------- ---------------- ------------ - 32mcj6mff82su 00000000620175A8 0000000063758060 0 N |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
