解决language_mismatch，bind_mismatch导致的高版本问题
如何解决language_mismatch导致的高版本问题
高版本会导致sql执行变慢，增大搜索子游标的时间，高并发下也容易产生"cursor mutex x" , "cursor mutex s"争用，而对于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.
参考文档：Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1)
补充：对于 BIND_MISMATCH导致的高版本可以通过设置10503事件解决
其原因是绑定变量的buffer与当前子游标的buffer不一致，这样会要求产生一个具有更高bind buffer的子游标，并发生一次硬解析
而这些没有使用的子游标会一直占用slot不释放，从而造成版本持续增加，实际这是由于Oracle的bug bug 2450264 引起的，这个bug可以
通过event解决。
ALTER system SET EVENTS '10503 trace name context forever, level 4000';, level <buffer length>';
Eg:
ALTER system SET EVENTS '10503 trace name context forever, level 4000';
这样就使得子游标中创建字符绑定使用相同的绑定长度。
参考文档：High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)
相关文章