My Oracle Support Banner

数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor:mutex X' 争用 (Doc ID 2577528.1)

Last updated on DECEMBER 19, 2019

适用于:

Oracle Database - Enterprise Edition - 版本 12.1.0.2 和更高版本
Generic (Platform Independent)

症状

更改

从应用程序更改 NLS 设置。

由于 NLS 设置不同而导致 LANGUAGE_MISMATCH 的示例代码:

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 时未发现语言不匹配,如下所示:

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

 

原因

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


文档内容
症状
更改
原因
解决方案
参考

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