My Oracle Support Banner

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 later
Information in this document applies to any platform.

Symptoms

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.