Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH
(Doc ID 2542447.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
- Database hang due to 'cursor: mutex X' contention
- High version count observed for SQL statements due to LANGUAGE_MISMATCH
- Flushing the shared pool relieves the mutex contention temporarily
- Version count showing more than the value set for _cursor_obsolete_threshold
Changes
Change in NLS settings from Application.
Sample code to demonstrate LANGUAGE_MISMATCH due to change in NLS settings:
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
Language Mismatch Not Found With CURSOR_SHARING = FORCE as follows:
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
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |