何解决language_mismatch导致的高版本问题

如何解决language_mismatch导致的高版本问题
高版本会导致sql执行变慢，增大搜索子游标的时间，高并发下也容易产生cursor mutex x 争用，而对于language_mismatch
导致的高版本问题，可以采用两种解决方案，下面是测试过程。
次查询
SQL> VAR B1 VARCHAR2(32);
SQL> EXEC :B1 := '';

PL/SQL procedure successfully completed.

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='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> 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 LA
-------------------------- ---------------- ---------------- ------------ --
a9x5sbz88kmfh 00000000714DB3E8 00000000714D9C90 0 N
a9x5sbz88kmfh 00000000714DB3E8 000000007148DAD0 1 Y
a9x5sbz88kmfh 00000000714DB3E8 0000000071480F20 2 Y
a9x5sbz88kmfh 00000000714DB3E8 000000007146F068 3 Y

其原因就是会话修改了 NLS 设置，在V$SQL_SHARED_CURSOR 显示 LANGUAGE_MISMATCH字段为Y。
SQL> select DBMS_LOB.SUBSTR(REASON,4000,1) from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'
DBMS_LOB.SUBSTR(REASON,4000,1)
------------------------------------------------------------------------------------------------------------------------
<ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(22)</reason><size>4x8</size><bind_position>00000
00000000000 </bind_position><original_oacflg>0000000300000000 </original_oacflg><original_oacmxl>0000008000000000 </orig
inal_oacmxl><upgradeable_new_oacmxl>00000fa000000000 </upgradeable_new_oacmxl></ChildNode><ChildNode><ChildNumber>0</Chi
ldNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_LANGUAGE>'AMERICAN'->'DUTCH'</NLS_LANGUAGE><N
LS_DATE_LANGUAGE>'AMERICAN'->'DUTCH'</NLS_DATE_LANGUAGE><NLS_SORT>'BINARY'->'DUTCH'</NLS_SORT></ChildNode><ChildNode><Ch
ildNumber>0</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_LANGUAGE>'AMERICAN'->'DUTCH'<
/NLS_LANGUAGE><NLS_DATE_LANGUAGE>'AMERICAN'->'DUTCH'</NLS_DATE_LANGUAGE><NLS_SORT>'BINARY'->'DUTCH'</NLS_SORT></ChildNod
e>

<ChildNode><ChildNumber>1</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_LANGUAGE>'DUTCH
'->'FRENCH'</NLS_LANGUAGE><NLS_DATE_LANGUAGE>'DUTCH'->'FRENCH'</NLS_DATE_LANGUAGE><NLS_SORT>'DUTCH'->'FRENCH'</NLS_SORT>
</ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_LAN
GUAGE>'DUTCH'->'FRENCH'</NLS_LANGUAGE><NLS_DATE_LANGUAGE>'DUTCH'->'FRENCH'</NLS_DATE_LANGUAGE><NLS_SORT>'DUTCH'->'FRENCH
'</NLS_SORT></ChildNode>
......
<ChildNode><ChildNumber>3</ChildNumber><ID>39</ID><reason>Bind mismatch(22)</reason><size>4x8</size><bind_position>00000
00000000000 </bind_position><original_oacflg>0000000300000000 </original_oacflg><original_oacmxl>00000fa000000000 </orig
inal_oacmxl><upgradeable_new_oacmxl>0000008000000000 </upgradeable_new_oacmxl></ChildNode>

如何解决
1 cursor_sharing=force 这个要根据影响范围谨慎选择。
2 修改隐藏参数，设置子游标阈值进而使得父游标失效
2.1为了降低'cursor: mutex X' or 'cursor: mutex S'争用，可以先purge父游标
SQL> select address,hash_value,version_count from v$sqlarea where sql_id='a9x5sbz88kmfh';
ADDRESS HASH_VALUE VERSION_COUNT
---------------- ---------- -------------
00000000714DB3E8 3498659280 4

SQL> exec dbms_shared_pool.purge('00000000714DB3E8,3498659280','C');

PL/SQL procedure successfully completed.

2.2 修改隐藏参数
SQL> alter system set "_cursor_obsolete_threshold"=200 scope=spfile sid='*';

System altered.