解决language_mismatch,bind_mismatch导致的高版本问题

2021-11-24 00:00:00 查询 版本 游标 绑定 导致

如何解决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)


相关文章