什么是Oracle的高版本游标（High Version Count）？如何排查？（持续更新）

2024-04-18码农

‍ ‍

简介

一个父游标下对应的子游标个数被称为Version Count，每个子游标对应一个执行计划。 对于一个特定的游标有多少个版本（Version Count）就属于高版本游标是没有明确定义的。对于不同的系统有不同的数量界定。High Version Count不仅产生的原因是多种多样的，而且会导致各种令人头痛的问题，轻则导致数据库的性能急剧下降，CPU利用率剧增，重则导致数据库挂起，触发ORA-04031或者其它BUG导致宕机。

在AWR报告中，默认Version Count大于20就会被报告出来， 如下图所示：

SQL执行计划的生成，是受到很多因素影响的。相同父游标只是表示输入SQL的字面值相同。子游标对应的因素，如优化器类型、优化器的模式（OPTIMIZER_MODE）、对应对象权限等的差异，都会影响到子游标的共享。

AWR报告中SQL统计部分的执行次数和每次执行时间为空的说明 (Doc ID 1522547.1)

从Oracle 10.2开始，若Version Count大于200，则Executions和 "Elap per Exec(s)"列不再自动收集，因为会引起性能问题，可以参考Why "Executions" and "Elap per Exec(s)" are 'Blank' in AWR for Some SQL Statements (Doc ID 1522547.1)。

参考：https://xmmup.com/awrbaogaozhongsqltongjibufendezhixingcishuhemeicizhixingshijianweikongdeshuoming-doc-id-15225471.html

游标不共享的原因整理

在Oracle 11g中， V$SQL_SHARED_CURSOR 可以用来诊断子游标不共享问题的原因。 该视图通过SQL_ID和CHILD_NUMBER就可以定义某个特定子游标的信息。该视图中大部分列都是以VARCHAR2(1)的Y/N取值，每列的含义都是一个不能共享的理由。需要注意的是， 这个理由Y表示的是不能与第一个子游标（CHILD_NUMBER=0）共享的理由。

下面是原因列表以及实际例子(标记的是非常常见原因) :

UNBOUND_CURSOR 现有的子游标没有构建完全(换言之, 该子游标没有被优化).

SQL_TYPE_MISMATCH SQL类型与现有的子游标不匹配。例如，在两个版本不同的客户端上运行同一个应用程序，在服务器中产生不同的子游标。

OPTIMIZER_MISMATCH 优化器环境与现有的子游标不匹配 (修改优化器模式之后，现有的子游标不能被重新使用)。在高版本中，修改参数statistics_level也会因为OPTIMIZER_MISMATCH导致不能共享。

例如:

1selectcount(*) from emp; ->> 1 父, 1 子游标 2altersessionset optimizer_mode=ALL_ROWS 3selectcount(*) from emp; ->> 1 父, 2 子游标

注: 该行为适用于跟踪事件的设置。例如，如果使用 10046 打开跟踪，将新添加一个由于OPTIMIZER_MISMATCH引起的子游标。

OUTLINE_MISMATCHOUTLINES 与现有的子游标不一致。例如：如果用户之前为这个SQL创建了存储OUTLINES，并且这些OUTLINES被存储在不同的分类里(称为"OUTLINES1" 和 "OUTLINES2")。如果执行下面的命令：

1altersessionsetuse_stored_outlines = OUTLINES1; 2selectcount(*) from emp; 3 4altersessionset use_stored_oulines= OUTLINES2; 5selectcount(*) from emp;

第二次执行"select from emp" 将创建另一个子游标，因为使用的OUTLINES与第一次运行的OUTLINES不同。这个子游标将被标记为 OUTLINE_MISMATCH。

STATS_ROW_MISMATCH现有的统计数据与现有的子游标不匹配。检查是否在所有会话上都设置了10046/sql_trace，因为这可能导致这种情况。

LITERAL_MISMATCH非数据字面值与现有的子游标不匹配。

SEC_DEPTH_MISMATCH安全级别与现有的子游标不匹配。

EXPLAIN_PLAN_CURSOR子游标是一个 explain plan 游标，不应该被共享。 explain plan 句将默认生成一个新的子游标--这种情况将不匹配。

BUFFERED_DML_MISMATCH缓冲的DML与现有的子游标不匹配。

PDML_ENV_MISMATCH PDML环境与现有的子游标不匹配。参数 parallel_dml_mode 和/或 parallel_max_degree 可能已经改变。

INST_DRTLD_MISMATCH 直接加载插入与现有的子游标不匹配。

SLAVE_QC_MISMATCH 现有的子游标是一个工作游标，而新的游标是由协调者发出的(或者，现有的子游标是由协调者发出的，而新的是一个工作游标)。

TYPECHECK_MISMATCH 现有的子游标没有完全优化。

AUTH_CHECK_MISMATCH 对于现有的子游标，认证/翻译检查失败。用户没有权限访问以前任何版本游标中的对象。一个典型的例子是，对于一个表，每个用户都有一个属于自己的副本。

BIND_MISMATCH ： 绑定元数据与现有的子游标不匹配 ，常见原因：

① 变量长度问题，包括声明变量长度（变量的定义长度）跨度很大和传入的具体值的长度跨度很大 （同一个变量值，传入的长度出现在(0,32]、[33,128]、[129,2000]、(2000++)区间，出现绑定变量分级

② 变量类型问题（如传入TIMESTAMP，但列类型为DATE）等

③ SQL绑定变量输入null值触发BUG 8198150

例如，在下面的语句中，绑定变量'a'的定义在两条语句中发生了变化，但在这里是因为BIND_LENGTH_UPGRADEABLE的原因，早期版本归于BIND_MISMATCH：

1 variable a varchar2(10); 2selectcount(*) from scott.emp where ename = :a ; ->> 1 PARENT, 1 CHILD 3 4 variable a varchar2(2000); 5selectcount(*) from scott.emp where ename = :a ; ->> 1 PARENT, 2 CHILDREN 6 7 8 col sql_text format a60 9select a.sql_text, a.sql_id,a.executions,CHILD_NUMBER from v$sql a where a.sql_text like'%select count(*) from scott.emp where ename%'and a.sql_text notlike'%v$sql%'; 10select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='5tvfxfkm0b81p'; 11 12 col name format a10 13 col DATATYPE_STRING format a20 14select sql_id,child_number,name,datatype_string,max_length from v$sql_bind_capture a where a.sql_id='5tvfxfkm0b81p'orderby CHILD_NUMBER; 15 16-- alter system flush shared_pool; 17 18 19 20 SYS@ORCLCDB> select banner_full from v$version; 21 22 BANNER_FULL 23----------------------------------------------------------------------------------------- 24 Oracle Database 19c Enterprise Edition Release19.0.0.0.0 - Production 25Version19.3.0.0.0 26SYS@ORCLCDB> variable a varchar2(10); 27 SYS@ORCLCDB> selectcount(*) from scott.emp where ename = :a ; 28 29 COUNT(*) 30---------- 31 0 32 33 SYS@ORCLCDB> variable a varchar2(2000); 34 SYS@ORCLCDB> selectcount(*) from scott.emp where ename = :a ; 35 36 COUNT(*) 37---------- 38 0 39 40 SYS@ORCLCDB> select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='5tvfxfkm0b81p'; 41 42 SQL_ID BI BI 43-------------------------- -- -- 44 5tvfxfkm0b81p N N 45 5tvfxfkm0b81p N Y 46 47 48 SYS@ORCLCDB> col sql_text format a60 49 SYS@ORCLCDB> select a.sql_text, a.sql_id,a.executions,CHILD_NUMBER from v$sql a where a.sql_text like'%select count(*) from scott.emp where ename%'and a.sql_text notlike'%v$sql%'; 50 51 SQL_TEXT SQL_ID EXECUTIONS CHILD_NUMBER 52------------------------------------------------------------ -------------------------- ---------- ------------ 53selectcount(*) from scott.emp where ename = :a 5tvfxfkm0b81p 10 54selectcount(*) from scott.emp where ename = :a 5tvfxfkm0b81p 11 55 56 57SYS@ORCLCDB> select sql_id,child_number,name,datatype_string,max_length from v$sql_bind_capture a where a.sql_id='5tvfxfkm0b81p'orderby CHILD_NUMBER; 58 59 SQL_ID CHILD_NUMBER NAME DATATYPE_STRING MAX_LENGTH 60-------------------------- ------------ ---------- -------------------- ---------- 61 5tvfxfkm0b81p 0 :A VARCHAR2(32) 32 62 5tvfxfkm0b81p 1 :A VARCHAR2(8192) 8192

DESCRIBE_MISMATCH 在描述子游标时，类型检查堆不存在。

LANGUAGE_MISMATCH 语言句柄与现有的子游标不匹配，一般是由于客户端字符集导致的。

TRANSLATION_MISMATCH 现有子游标的基本对象不匹配。该对象的定义与当前的任何版本不匹配。通常这表明与对象不同的"AUTH_CHECK_MISMATCH"相同的问题。

ROW_LEVEL_SEC_MISMATCH 行级安全策略不匹配。

INSUFF_PRIVS 现有子游标所参考的对象的权限不足。

INSUFF_PRIVS_REM 现有子游标所参考的远程对象的权限不足。

REMOTE_TRANS_MISMATCH 现有子游标的远程基础对象不匹配。比如说：

1USER1: 2selectcount(*) fromtable@remote_db 3 4USER2: 5selectcount(*) fromtable@remote_db

尽管SQL是相同的，但remote_db所指向的dblink可能是一个私有的dblink，它解析到一个完全不同的对象。

LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH

OVERLAP_TIME_MISMATCH Error_on_overlap_time 不匹配。

SQL_REDIRECT_MISMATCH SQL 重定向不匹配。

MV_QUERY_GEN_MISMATCH 生成物化视图查询。

USER_BIND_PEEK_MISMATCH 用户的 BIND PEEK 不匹配。

TYPCHK_DEP_MISMATCH 游标有类型检查的依赖性。

NO_TRIGGER_MISMATCH 触发器不一致。

FLASHBACK_CURSOR 对于闪回没有游标共享。

ANYDATA_TRANSFORMATION 数据转换有变化。

INCOMPLETE_CURSOR 不完整的游标。当绑定长度可以升级时(也就是说，找到了一个子游标，除了绑定长度不够之外，其他都匹配)，旧的游标不能使用，并且建立一个新的。这意味着该版本可以被忽略。

TOP_LEVEL_RPI_CURSOR 最顶端的RPI游标。在并行查询的调用中，这是预期的行为(故意不分享)。

DIFFERENT_LONG_LENGTH LONG值的长度不一致。

LOGICAL_STANDBY_APPLY 逻辑备库应用上下文不匹配。

DIFF_CALL_DURN 调用期间不一致。

BIND_UACS_DIFF 绑定UAC不匹配。

PLSQL_CMP_SWITCHS_DIFF PL/SQL编译器开关不匹配。

CURSOR_PARTS_MISMATCH 游标 "parts executed" 不匹配。

STB_OBJECT_MISMATCH STB 对象不一致(现在存在的). 关于STB_OBJECT_MISMATCH的说明请阅读下面的博客: https://blogs.oracle.com/optimizer/entry/my_cursor_wasn_t_shared

ROW_SHIP_MISMATCH 行的传输能力不匹配。

PQ_SLAVE_MISMATCH PQ工作进程不匹配。如果遇到这种原因编号，并且正在使用并行执行(PX)，那么请检查是否真的想使用它。这种不匹配可能是由于运行大量不需要并行执行的小SQL语句造成的。另外，如果使用的是11g之前的版本，可能会遇到Bug:4367986 。

TOP_LEVEL_DDL_MISMATCH 最顶端的DDL游标。

MULTI_PX_MISMATCH 多个并行进程以及工作进程编译的游标。

BIND_PEEKED_PQ_MISMATCH Bind-peeked PQ 游标。

MV_REWRITE_MISMATCH 物化视图重写游标。

ROLL_INVALID_MISMATCH ： 超过了滚动无效窗口。这是由DBMS_STATS的滚动无效功能引起的。因为它的无效窗口已经超过了，所以子游标不能被共享。 ROLL_INVALID_MISMATCH 与 dbms_stats 的 no_invalidate 参数有关，一般大批量 sql 出现这种情况一般是因为自动收集统计信息导致的。参考： Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE

OPTIMIZER_MODE_MISMATCH 优化器模式不匹配。

PX_MISMATCH 并行查询执行不匹配。请参考以下显示此原因的已知问题： Document 1629107.1 Common Bugs Associated with PX_MISMATCH

MV_STALEOBJ_MISMATCH 失效的物化视图对象不匹配。

FLASHBACK_TABLE_MISMATCH 闪回表不匹配。

LITREP_COMP_MISMATCH Literal 替换的使用不匹配。

11g 新追加 :

PLSQL_DEBUG调试不匹配。会话的调试参数 plsql_debug 设置为true。

LOAD_OPTIMIZER_STATS游标共享的负载优化器统计。

ACL_MISMATCH检查ACL不匹配。

FLASHBACK_ARCHIVE_MISMATCH闪回归档不匹配。

LOCK_USER_SCHEMA_FAILED锁定用户和模式失败。

REMOTE_MAPPING_MISMATCH远程映射不匹配

LOAD_RUNTIME_HEAP_FAILED运行时堆栈不匹配。

HASH_MATCH_FAILED哈希值不匹配。如果由于哈希值不匹配导致共享失败，例如直方图数据不匹配或通过字面替换标记为不安全的范围谓词的情况，则设置为 "Y"(参考Bug 3461251)。

11.2 新追加:

PURGED_CURSOR 被标记为清除的游标。该游标已被标记为使用dbms_shared_pool.purge进行了清除。

BIND_LENGTH_UPGRADEABLE ：绑定长度可升级，并且无法共享，因为一个绑定变量大小小于正在插入的新值(在早期版本中被标记为BIND_MISMATCH)。

USE_FEEDBACK_STATS Cardinality反馈。正在使用Cardinality反馈，因此可以为当前执行形成一个新的计划。

BIND_EQUIV_FAILURE 绑定值的选择性与用于优化现有子游标的选择性不一致。当使用ACS自适应游标共享并且游标是绑定感知的，那么如果选择性超出了当前的范围，并且新的计划是可取的，那么就会产生一个新的子游标，其原因代码是不共享以前的计划。

BIND_EQUIV_FAILURE常见原因：

① 由于ACS自适应游标的bug导致

② 表字段为VARCHAR2，但是输入值为NVARCHAR2

③设置 alter session set statistics_level=all; 导致出现子光标不能共享，在高版本中已经归类到OPTIMIZER_MISMATCH中了。

④ 由于bug 28794230导致，**12.2 由于 Bind_equiv_failure 引发 SQL 不能共享进而造成 Cursor Mutex: x (Doc ID 2610645.1) ** 、12.2 Cursor Mutex X Due To Sql Not Shared Because Of BIND_EQUIV_FAILURE

⑤ 由于SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)引起的，解决办法： alter system set "_fix_control"='17443547:OFF';

参考：https://www.xmmup.com/youyubind_equiv_failuredaozhideyoubiaobunenggongxiangwenti.html

关于一个例子，请看文档<836256.1>。在例子中每次执行后，运行:

1select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';

一旦游标被标记为绑定感知，并且看到了第二个计划，那么以下将是结果输出：

sql<br />SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B</p></li> </ul> ¨K44K 19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N 19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y

可以看出，由于BIND_EQUIV_FAILURE，新的版本被创建。

11.2 中不再有 ROW_LEVEL_SEC_MISMATCH。

相关实验

BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE实验

Insert 语句

1DROPTABLE T_BG_20280414_LHR_02; 2CREATETABLE T_BG_20280414_LHR_02(N NUMBER,V VARCHAR2(4000), v2 date); 3 4CREATEINDEX idx_T_BG_20280414_LHR_02_N on T_BG_20280414_LHR_02(N); 5 6SELECTCOUNT(*) FROM T_BG_20280414_LHR_02; 7SELECT * FROM T_BG_20280414_LHR_02; 8truncatetable T_BG_20280414_LHR_02; 9 10begin 11 12FOR i IN1..3000LOOP 13-- alter system flush shared_pool; 14DECLARE 15 N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 16-- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 17 18 V1 VARCHAR2(32767) := lpad('1', i, '1'); 19--V1 NUMBER := lpad('1', i, '1'); 20--V1 NVARCHAR2(4000) := lpad('1', i, '1'); 21--V1 char(4000) := lpad('1', i, '1'); 22 23 24 V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 25-- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 26 27 28begin 29 30EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, V1 ,V2; 31EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), V1 , V2; 32EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), V1 , V2; 33 34 35EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NCHAR(V1) ,V2; 36EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NUMBER(V1) ,V2; 37EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_NCHAR(V1) , V2; 38EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1),TO_NUMBER(V1) ,V2; 39EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1),TO_NCHAR(V1) ,V2; 40EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1),TO_NUMBER(V1) ,V2; 41 42 43 44EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, V1 ,TO_CHAR(V2,'YYYY-MM-DD'); 45EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, V1,cast(V2 astimestamp); 46EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 47EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NUMBER(V1) ,cast(V2 astimestamp); 48EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 49EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_CHAR(V1) ,cast(V2 astimestamp); 50EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 51EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING N1, TO_NCHAR(V1) ,cast(V2 astimestamp); 52 53 54 55EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), V1 ,TO_CHAR(V2,'YYYY-MM-DD'); 56EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), V1,cast(V2 astimestamp); 57EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 58EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_NUMBER(V1) ,cast(V2 astimestamp); 59EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 60EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 astimestamp); 61EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 62EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_NCHAR(V1) ,cast(V2 astimestamp); 63 64 65EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), V1 ,TO_CHAR(V2,'YYYY-MM-DD'); 66EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), V1,cast(V2 astimestamp); 67EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 68EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), TO_NUMBER(V1) ,cast(V2 astimestamp); 69EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 70EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 astimestamp); 71EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'); 72EXECUTEIMMEDIATE'INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)'USING TO_NCHAR(N1), TO_NCHAR(V1) ,cast(V2 astimestamp); 73 74 75END; 76 77ENDLOOP; 78 79COMMIT; 80 81end; 82 83 84select address,hash_value,A.sql_profile,A.sql_plan_baseline,A.* from v$sql a where a.sql_text like'%INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES%' ; 85select a.sql_id,a.child_number,a.name,a.position,a.datatype_string,a.max_length from v$sql_bind_capture a where a.sql_id='610ygu94sf2t4' 86orderby a.sql_id,a.child_number,a.position; 87 88-- select * from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4'; 89select a.sql_id,A.child_number,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4'; 90 91 92 93select address,hash_value ,plan_hash_value from v$sql a where a.sql_text like'%INSERT INTO T_BG_20280414_LHR_02(N,V,V2) VALUES%' ; 94 95 96BEGIN dbms_shared_pool.keep('000000007A84D5F8,1233586980','C'); END; 97 98BEGIN dbms_shared_pool.PURGE('000000007A84D5F8,1233586980','C'); END; 99 100 101SELECT * FROMTABLE(VERSION_RPT('610ygu94sf2t4')); 102 103 104select * from vw_sql_version_count_reason_lhr where sql_id='610ygu94sf2t4'; 105 106 107 108 109select * fromtable(DBMS_XPLAN.DISPLAY_CURSOR('610ygu94sf2t4',0,'all')); 110 111 112 113select a.plan_hash_value from v$sql a where sql_id='610ygu94sf2t4'; 114 115 116ALTERSYSTEMSET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; 117 118SELECT * FROM DBA_SQL_PLAN_BASELINES; 119SELECT * FROM DBA_SQLSET_PLANS; 120SELECT * FROM DBA_ADVISOR_SQLPLANS; 121 122 123 124ALTERsystemSETEVENTS'10503 trace name context forever, level 4000'; 125ALTERsystemSETEVENTS'10503 trace name context off'; 126 127 128altersystemset"_cursor_obsolete_threshold"=10scope=spfilesid='*'; 129 130 131 132 133 134-- alter index idx_T_BG_20280414_LHR_02_N monitoring usage; 135-- alter index idx1 nomonitoring USAGE; 136 137-- SELECT * FROM V$OBJECT_USAGE; 138 139 140 141 142 143 144Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 16-apr-24 14:58 145RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB 146================================================================== 147Addr: 000000007A84D5F8 Hash_Value: 1233586980 SQL_ID 610ygu94sf2t4 148Sharable_Mem: 1497694 bytes Parses: 111028 Execs:151018 149Stmt: 1500 INSERTINTO T_BG_20280414_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2) 1511 152 153Versions Summary 154---------------- 155BIND_MISMATCH :66 156BIND_LENGTH_UPGRADEABLE :62 157 158Total Versions:69 159 160~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 161cursor_sharing = EXACT 162_cursor_obsolete_threshold = 8192 (See Note:10187168.8) 163~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 164 165Plan HashValue Summary 166----------------------- 167Plan HashValueCount 168=============== ===== 169070 170 171~~~~~~~~~~~~~~~~~~~~~~~~~~~ 172Details for BIND_MISMATCH : 173 174Consolidated details for BIND* columns: 175BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and 176BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441in11gR1) 177 178from v$sql_bind_capture 179COUNT(*) POSITIONMIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE) 180======== ======== =============== =============== ======== =============== ================= 18137132321No (,) 18233122222No (,) 1835723281921 Yes (,) 1845222222No (,) 185824000400096No (,) 18616332321No (,) 1872637712No (,) 1882331111180No (,6) 189531111180No (,9) 190 191SUM(DECODE(column,Y, 1, 0) FROM V$SQL 192IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE 193=========== ================= ============= ============ 19400026 195~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 196Details for BIND_LENGTH_UPGRADEABLE : 197 198Details shown already. 199#### 200To further debug Ask Oracle Support for the appropiate level LLL. 201altersessionsetevents 202'immediate trace name cursortrace address 1233586980, level LLL'; 203To turn it off douse address 1, level2147483648 204================================================================

update语句

1begin 2 3FOR i IN1..2100LOOP 4-- alter system flush shared_pool; 5IFMOD(i, 16) = 0THEN 6DECLARE 7 N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 8-- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 9 10 V1 VARCHAR2(32767) := lpad('1', i, '1'); 11--V1 NUMBER := lpad('1', i, '1'); 12--V1 NVARCHAR2(4000) := lpad('1', i, '1'); 13--V1 char(4000) := lpad('1', i, '1'); 14 15 16 V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 17-- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 18 19 20begin 21 22 23 24EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,V2,N1; 25EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,V2,TO_CHAR(N1); 26EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,V2,TO_NCHAR(N1); 27 28 29EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,V2,N1; 30EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,V2,N1; 31EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) , V2, TO_CHAR(N1); 32EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,V2, TO_CHAR(N1); 33EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,V2,TO_NCHAR(N1); 34EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,V2,TO_NCHAR(N1); 35 36 37 38EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,TO_CHAR(V2,'YYYY-MM-DD'),N1; 39EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1,cast(V2 astimestamp),N1; 40EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'),N1; 41EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,cast(V2 astimestamp),N1; 42EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'),N1; 43EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,cast(V2 astimestamp),N1; 44EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'),N1; 45EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,cast(V2 astimestamp),N1; 46 47 48 49EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,TO_CHAR(V2,'YYYY-MM-DD'), TO_CHAR(N1); 50EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1,cast(V2 astimestamp), TO_CHAR(N1); 51EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_CHAR(N1); 52EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,cast(V2 astimestamp), TO_CHAR(N1); 53EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_CHAR(N1); 54EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,cast(V2 astimestamp), TO_CHAR(N1); 55EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_CHAR(N1); 56EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,cast(V2 astimestamp), TO_CHAR(N1); 57 58 59EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1 ,TO_CHAR(V2,'YYYY-MM-DD'), TO_NCHAR(N1); 60EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING V1,cast(V2 astimestamp), TO_NCHAR(N1); 61EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_NCHAR(N1); 62EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NUMBER(V1) ,cast(V2 astimestamp), TO_NCHAR(N1); 63EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_NCHAR(N1); 64EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_CHAR(V1) ,cast(V2 astimestamp), TO_NCHAR(N1); 65EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD'), TO_NCHAR(N1); 66EXECUTEIMMEDIATE'update T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'USING TO_NCHAR(V1) ,cast(V2 astimestamp), TO_NCHAR(N1); 67 68 69 70 71 72END; 73 74ENDIF; 75 76ENDLOOP; 77 78COMMIT; 79 80 81end; 82 83 84 85 86 87select address,hash_value,A.sql_profile,A.sql_plan_baseline,A.* from v$sql a where a.sql_text like'%update T_BG_20280414_LHR_02 set%' ; 88 89select a.sql_id,a.child_number,a.name,a.position,a.datatype_string,a.max_length from v$sql_bind_capture a where a.sql_id='0dbvtumzg263r' 90orderby a.sql_id,a.child_number,a.position; 91 92-- select * from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4'; 93select a.sql_id,A.child_number,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='0dbvtumzg263r'; 94 95 96 97select address,hash_value ,plan_hash_value from v$sql a where a.sql_text like'%update T_BG_20280414_LHR_02 set%' ; 98 99 100BEGIN dbms_shared_pool.keep('000000006674C050,4277213303','C'); END; 101BEGIN dbms_shared_pool.PURGE('000000006674C050,4277213303','C'); END; 102 103 104BEGIN dbms_shared_pool.unkeep('000000006674C050,4277213303','C'); END; 105 106SELECT * FROMTABLE(VERSION_RPT('0dbvtumzg263r')); 107 108 109select * from vw_sql_version_count_reason_lhr where sql_id='0dbvtumzg263r'; 110 111 112 113 114select * fromtable(DBMS_XPLAN.DISPLAY_CURSOR('0dbvtumzg263r',1,'all')); 115 116 117 118select a.plan_hash_value from v$sql a where sql_id='610ygu94sf2t4'; 119 120 121ALTERSYSTEMSET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; 122SELECT * FROM DBA_SQL_PLAN_BASELINES; 123SELECT * FROM DBA_SQLSET_PLANS; 124SELECT * FROM DBA_ADVISOR_SQLPLANS; 125 126 127 128 129ALTERsystemSETEVENTS'10503 trace name context forever, level 2000';

参考：https://www.xmmup.com/moshigh-version-count-due-to-bind_mismatch-doc-id-3362681-10503-events.html

ROLL_INVALID_MISMATCH

超过了滚动无效窗口。这是由DBMS_STATS的滚动无效功能引起的。因为它的无效窗口已经超过了，所以子游标不能被共享。 ROLL_INVALID_MISMATCH 与 dbms_stats 的 no_invalidate 参数有关，一般大批量 sql 出现这种情况一般是因为自动收集统计信息导致的。

参考： Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE

在10g之前，使用dbms_stats采集对象统计信息，除非no_invalidate设为TRUE，否则所有缓存在Library Cache中的游标都会失效，下次执行时需要做硬解析。隐患就是对于一个OLTP系统，会产生一次硬解析风暴，消耗大量的CPU、库缓存以及共享池latch的争用，进而影响应用系统的响应时间。如果设置no_invalidate为FALSE，则现有存储的游标不会使用更新的对象统计信息，仍使用旧有执行计划，直到下次硬解析，要么因为时间太久，导致cursor被刷出，要么手工执行flush刷新了共享池，这两种情况下会重新执行硬解析，根据更新的对象统计信息，生成更新的执行计划。这么做其实还是有可能出现硬解析风暴，特别是OLTP系统，高并发时候，有SQL语句频繁访问。

使用dbms_stats.gather_XXX_stats的时候，有个参数no_invalidate，

TRUE: does not invalidate the dependent cursors
FALSE: invalidates the dependent cursors immediately
AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors

默认是AUTO_INVALIDATE，这表示是由Oracle来决定什么时候让依赖的游标失效。

10g之后，如果采集对象统计信息使用的no_invalidate参数是auto_invalidate，则Oracle会采用如下操作，来缓解可能的硬解析风暴。
1.执行dbms_stats，所有依赖于这个已分析对象的缓存cursor游标会被标记为rolling invalidation，并且记录此时刻是T0。
2.下次某个session需要解析这个标记为rolling invalidation的cursor游标时，会设置一个时间戳，其取值为 _optimizer_invalidation_period 定义的最大值范围内的一个随机数。之所以是随机数，就是为了分散这些 invalidation的游标，防止出现硬解析风暴。参数 _optimizer_invalidation_period 默认值是18000秒，5小时。记录这次解析时间为T1，时间戳值为Tmax。但此时，仍是重用了已有游标，不会做硬解析，不会使用更新的统计信息来生成一个新的执行计划。
3.接下来这个游标(标记了rolling invalidation和时间戳)的每次使用时，都会判断当前时刻T2是否超过了时间戳Tmax。如果未超过，则仍使用已存在的cursor。如果Tmax已经超过了，则会让此游标失效，创建一个新的版本(一个新的child cursor子游标)，使用更新的执行计划，并且新的子游标会标记V$SQL_SHARED_CURSOR中ROLL_INVALID_MISMATCH的值。

MOS中还描述了一些游标使用的场景：

1.如果一个游标被标记为rolling invalidation，但是再不会做解析，则这个游标不会失效，最终还是可能根据LRU被刷出共享池。

2.如果一个游标被标记为rolling invalidation，后面只会解析一次，那么这个游标依然不会失效(仅仅使用时间戳标记)，最终还是可能根据LRU被刷出共享池。 3.频繁使用的游标，在超过时间戳Tmax值后，下次解析时就会被置为失效。

实验：

1altersystemset"_optimizer_invalidation_period"=60; 2 3createtable X asselect * from dba_tables; 4exec dbms_stats.gather_table_stats(null,'X'); 5 6 7selectcount(*) from X; 8select sql_id,sql_text, a.executions,a.parse_calls,a.loads, a.first_load_time, a.last_load_time from v$sql a where sql_text like'%select count(*) from X%'; 9select * from v$sql_shared_cursor where sql_id='c3zk14hnh9ydw';

OPTIMIZER_MODE_MISMATCH--由于优化器模式不同导致游标不共享的示例

下面举一个由于优化器模式不同导致游标不能共享的例子：

1SYS@lhrdb> SELECT * FROM SCOTT.EMP WHEREROWNUM<=0; 2no rows selected 3SYS@lhrdb> select sql_id, version_count from v$sqlarea where sql_text like'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%'; 4SQL_ID VERSION_COUNT 5------------- ------------- 67u75n20ktntsb 1 7SYS@lhrdb> show parameter optimizer_mode 8NAMETYPEVALUE 9------------------------------------ ----------- ------------------------------ 10optimizer_mode string ALL_ROWS 11SYS@lhrdb> altersessionset optimizer_mode=first_rows; 12Session altered. 13SYS@lhrdb> SELECT * FROM SCOTT.EMP WHEREROWNUM<=0; 14no rows selected 15SYS@lhrdb> select sql_id, version_count,SQL_TEXT from v$sqlarea where sql_text like'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%'; 16SQL_ID VERSION_COUNT 17------------- ------------- 187u75n20ktntsb 2 19SYS@lhrdb> select sql_id, child_number, OPTIMIZER_MODE from v$sqlwhere sql_id='7u75n20ktntsb'; 20SQL_ID CHILD_NUMBER OPTIMIZER_ 21------------- ------------ ---------- 227u75n20ktntsb 0 ALL_ROWS 237u75n20ktntsb 1 FIRST_ROWS 24SYS@lhrdb> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='7u75n20ktntsb'; 25SQL_ID CHILD_NUMBER O 26------------- ------------ - 277u75n20ktntsb 0 N 287u75n20ktntsb 1 Y

LANGUAGE_MISMATCH

可能原因：

1、与环境设置 NLS_* 有关系，比如客户端session 中使用了不同的NLS_SORT 、nls_language等。

2、cdb和pdb的字符集不一样，参考：Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1) 、数据库挂起由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1) 、 Bug 25054064 - Cursor Has High Version Count In PDB Whose Character Set Is Different From CDB$ROOT (Doc ID 25054064.8)

测试1：

1altersystemflushshared_pool; 2 3createtable X asselect * from dba_tables; 4 5settimeon pages 1000 lin 200 6 7col FIRST_LOAD_TIME format a20 8 9col last_load_time format a20 10 11var b1 varchar2(128); 12 13var b2 varchar2(128); 14 15exec :b1 := '0'; 16 17exec :b2 := '0'; 18 19nls_sort 20 21altersessionset = 'SCHINESE_RADICAL_M'; 22 23selectcount(*) from X where table_name=:b1 and OWNER=:b2; 24 25--- 修改 nls_sort 26 27altersessionset nls_sort = 'SCHINESE_STROKE_M'; 28 29selectcount(*) from X where table_name=:b1 and OWNER=:b2; 30 31-- 修改 nls_sort 32 33altersessionset nls_sort = 'SCHINESE_PINYIN_M'; 34 35selectcount(*) from X where table_name=:b1 and OWNER=:b2; 36 37 38select sql_id from v$sqlwhere sql_text='select count(*) from X where table_name=:b1 and OWNER=:b2'; 39select sql_id,child_address,language_mismatch,roll_invalid_mismatch,BIND_MISMATCH from v$sql_shared_cursor where sql_id='1uk1h0wuzcuaf';

测试2：

1SQL> conn / as sysdba 2Connected. 3 4SQL> VAR B1 VARCHAR2(32); 5SQL> EXEC :B1 := ''; 6 7SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 8 9FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 10--------------------------------------------------------------------------- 11 12SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; 13 14SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L 15------------- ---------------- ---------------- ------------ - 16a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N 17 18SQL> altersessionset nls_language='DUTCH'; 19 20Sessie is gewijzigd. 21 22SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 23 24FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 25--------------------------------------------------------------------------- 26 27SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; 28 29SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L 30------------- ---------------- ---------------- ------------ - 31a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N 32a9x5sbz88kmfh 0000000067968598 000000006B8CAC30 1 Y 33 34SQL> altersessionset nls_language='FRENCH'; 35 36Session modifiee. 37 38SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 39 40FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 41--------------------------------------------------------------------------- 42 43SQL> altersessionset nls_territory='BELGIUM'; 44 45Session modifiee. 46 47SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 48 49FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 50--------------------------------------------------------------------------- 51 52SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='a9x5sbz88kmfh'; 53 54SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L 55------------- ---------------- ---------------- ------------ - 56a9x5sbz88kmfh 0000000067968598 000000006EB0DD48 0 N 57a9x5sbz88kmfh 0000000067968598 000000006B8CAC30 1 Y 58a9x5sbz88kmfh 0000000067968598 000000006B8CAAB0 2 Y 59a9x5sbz88kmfh 0000000067968598 000000006F29A308 3 Y 60 61 62Language Mismatch Not Found With CURSOR_SHARING = FORCE as follows: 63 64SQL> conn / as sysdba 65 66SQL> altersessionset cursor_sharing=force; 67 68Session altered. 69 70SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 71 72FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 73--------------------------------------------------------------------------- 74 75SQL> altersessionset nls_language='FRENCH'; 76 77Session modifiee. 78 79SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 80 81FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 82--------------------------------------------------------------------------- 83 84SQL> altersessionset nls_territory='BELGIUM'; 85 86Session altered. 87 88SQL> SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO_CHAR(SYSTIMESTAMP, 'TZR')) ATTIME ZONE 'GMT'FROM SYS.DUAL; 89 90FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),TO 91--------------------------------------------------------------------------- 92 93SQL> select sql_id, open_versions, sql_text from v$sqlwhere sql_text like'SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1%'; 94 95SQL_ID OPEN_VERSIONS SQL_TEXT 96------------- ------------- -------------------------------------------------------------------------------- 9732mcj6mff82su 1 SELECT FROM_TZ(TO_TIMESTAMP(TO_CHAR(:B1 ,:"SYS_B_0"),:"SYS_B_1"),TO_CHAR(SYSTIME 98STAMP, :"SYS_B_2")) ATTIME ZONE :"SYS_B_3"FROM SYS.DUAL 99 100SQL> select SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER,LANGUAGE_MISMATCH from v$sql_shared_cursor where sql_id='32mcj6mff82su'; 101 102SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER L 103------------- ---------------- ---------------- ------------ - 10432mcj6mff82su 00000000620175A8 0000000063758060 0 N

version count高的原因查询

1createorreplaceview vw_sql_version_count_reason_lhr as 2select sql_id, 3 sc.address, 4 hash_value, 5 sq.executions, 6 version_count, 7 parsing_schema_name, 8 sq.loads, 9 sq.parse_calls, 10 sq.last_load_time, 11 reason, 12 sql_text 13from ( 14select 15 sql_id, address, 'reason => ' 16||decode(max(UNBOUND_CURSOR),'Y','UNBOUND_CURSOR '||': '||count(*)||' | ') 17||decode(max(SQL_TYPE_MISMATCH),'Y','SQL_TYPE_MISMATCH '||': '||count(*)||' | ') 18||decode(max(OPTIMIZER_MISMATCH),'Y','OPTIMIZER_MISMATCH '||': '||count(*)||' | ') 19||decode(max(OUTLINE_MISMATCH),'Y','OUTLINE_MISMATCH '||': '||count(*)||' | ') 20||decode(max(STATS_ROW_MISMATCH),'Y','STATS_ROW_MISMATCH '||': '||count(*)||' | ') 21||decode(max(LITERAL_MISMATCH),'Y','LITERAL_MISMATCH '||': '||count(*)||' | ') 22||decode(max(FORCE_HARD_PARSE),'Y','FORCE_HARD_PARSE '||': '||count(*)||' | ') 23||decode(max(EXPLAIN_PLAN_CURSOR),'Y','EXPLAIN_PLAN_CURSOR '||': '||count(*)||' | ') 24||decode(max(BUFFERED_DML_MISMATCH),'Y','BUFFERED_DML_MISMATCH '||': '||count(*)||' | ') 25||decode(max(PDML_ENV_MISMATCH),'Y','PDML_ENV_MISMATCH '||': '||count(*)||' | ') 26||decode(max(INST_DRTLD_MISMATCH),'Y','INST_DRTLD_MISMATCH '||': '||count(*)||' | ') 27||decode(max(SLAVE_QC_MISMATCH),'Y','SLAVE_QC_MISMATCH '||': '||count(*)||' | ') 28||decode(max(TYPECHECK_MISMATCH),'Y','TYPECHECK_MISMATCH '||': '||count(*)||' | ') 29||decode(max(AUTH_CHECK_MISMATCH),'Y','AUTH_CHECK_MISMATCH '||': '||count(*)||' | ') 30||decode(max(BIND_MISMATCH),'Y','BIND_MISMATCH '||': '||count(*)||' | ') 31||decode(max(DESCRIBE_MISMATCH),'Y','DESCRIBE_MISMATCH '||': '||count(*)||' | ') 32||decode(max(LANGUAGE_MISMATCH),'Y','LANGUAGE_MISMATCH '||': '||count(*)||' | ') 33||decode(max(TRANSLATION_MISMATCH),'Y','TRANSLATION_MISMATCH '||': '||count(*)||' | ') 34||decode(max(BIND_EQUIV_FAILURE),'Y','BIND_EQUIV_FAILURE '||': '||count(*)||' | ') 35||decode(max(INSUFF_PRIVS),'Y','INSUFF_PRIVS '||': '||count(*)||' | ') 36||decode(max(INSUFF_PRIVS_REM),'Y','INSUFF_PRIVS_REM '||': '||count(*)||' | ') 37||decode(max(REMOTE_TRANS_MISMATCH),'Y','REMOTE_TRANS_MISMATCH '||': '||count(*)||' | ') 38||decode(max(LOGMINER_SESSION_MISMATCH),'Y','LOGMINER_SESSION_MISMATCH '||': '||count(*)||' | ') 39||decode(max(INCOMP_LTRL_MISMATCH),'Y','INCOMP_LTRL_MISMATCH '||': '||count(*)||' | ') 40||decode(max(OVERLAP_TIME_MISMATCH),'Y','OVERLAP_TIME_MISMATCH '||': '||count(*)||' | ') 41||decode(max(EDITION_MISMATCH),'Y','EDITION_MISMATCH '||': '||count(*)||' | ') 42||decode(max(MV_QUERY_GEN_MISMATCH),'Y','MV_QUERY_GEN_MISMATCH '||': '||count(*)||' | ') 43||decode(max(USER_BIND_PEEK_MISMATCH),'Y','USER_BIND_PEEK_MISMATCH '||': '||count(*)||' | ') 44||decode(max(TYPCHK_DEP_MISMATCH),'Y','TYPCHK_DEP_MISMATCH '||': '||count(*)||' | ') 45||decode(max(NO_TRIGGER_MISMATCH),'Y','NO_TRIGGER_MISMATCH '||': '||count(*)||' | ') 46||decode(max(FLASHBACK_CURSOR),'Y','FLASHBACK_CURSOR '||': '||count(*)||' | ') 47||decode(max(ANYDATA_TRANSFORMATION),'Y','ANYDATA_TRANSFORMATION '||': '||count(*)||' | ') 48||decode(max(PDDL_ENV_MISMATCH),'Y','PDDL_ENV_MISMATCH '||': '||count(*)||' | ') 49||decode(max(TOP_LEVEL_RPI_CURSOR),'Y','TOP_LEVEL_RPI_CURSOR '||': '||count(*)||' | ') 50||decode(max(DIFFERENT_LONG_LENGTH),'Y','DIFFERENT_LONG_LENGTH '||': '||count(*)||' | ') 51||decode(max(LOGICAL_STANDBY_APPLY),'Y','LOGICAL_STANDBY_APPLY '||': '||count(*)||' | ') 52||decode(max(DIFF_CALL_DURN),'Y','DIFF_CALL_DURN '||': '||count(*)||' | ') 53||decode(max(BIND_UACS_DIFF),'Y','BIND_UACS_DIFF '||': '||count(*)||' | ') 54||decode(max(PLSQL_CMP_SWITCHS_DIFF),'Y','PLSQL_CMP_SWITCHS_DIFF '||': '||count(*)||' | ') 55||decode(max(CURSOR_PARTS_MISMATCH),'Y','CURSOR_PARTS_MISMATCH '||': '||count(*)||' | ') 56||decode(max(STB_OBJECT_MISMATCH),'Y','STB_OBJECT_MISMATCH '||': '||count(*)||' | ') 57||decode(max(CROSSEDITION_TRIGGER_MISMATCH),'Y','CROSSEDITION_TRIGGER_MISMATCH '||': '||count(*)||' | ') 58||decode(max(PQ_SLAVE_MISMATCH),'Y','PQ_SLAVE_MISMATCH '||': '||count(*)||' | ') 59||decode(max(TOP_LEVEL_DDL_MISMATCH),'Y','TOP_LEVEL_DDL_MISMATCH '||': '||count(*)||' | ') 60||decode(max(MULTI_PX_MISMATCH),'Y','MULTI_PX_MISMATCH '||': '||count(*)||' | ') 61||decode(max(BIND_PEEKED_PQ_MISMATCH),'Y','BIND_PEEKED_PQ_MISMATCH '||': '||count(*)||' | ') 62||decode(max(MV_REWRITE_MISMATCH),'Y','MV_REWRITE_MISMATCH '||': '||count(*)||' | ') 63||decode(max(ROLL_INVALID_MISMATCH),'Y','ROLL_INVALID_MISMATCH '||': '||count(*)||' | ') 64||decode(max(OPTIMIZER_MODE_MISMATCH),'Y','OPTIMIZER_MODE_MISMATCH '||': '||count(*)||' | ') 65||decode(max(PX_MISMATCH),'Y','PX_MISMATCH '||': '||count(*)||' | ') 66||decode(max(MV_STALEOBJ_MISMATCH),'Y','MV_STALEOBJ_MISMATCH '||': '||count(*)||' | ') 67||decode(max(FLASHBACK_TABLE_MISMATCH),'Y','FLASHBACK_TABLE_MISMATCH '||': '||count(*)||' | ') 68||decode(max(LITREP_COMP_MISMATCH),'Y','LITREP_COMP_MISMATCH '||': '||count(*)||' | ') 69||decode(max(PLSQL_DEBUG),'Y','PLSQL_DEBUG '||': '||count(*)||' | ') 70||decode(max(LOAD_OPTIMIZER_STATS),'Y','LOAD_OPTIMIZER_STATS '||': '||count(*)||' | ') 71||decode(max(ACL_MISMATCH),'Y','ACL_MISMATCH '||': '||count(*)||' | ') 72||decode(max(FLASHBACK_ARCHIVE_MISMATCH),'Y','FLASHBACK_ARCHIVE_MISMATCH '||': '||count(*)||' | ') 73||decode(max(LOCK_USER_SCHEMA_FAILED),'Y','LOCK_USER_SCHEMA_FAILED '||': '||count(*)||' | ') 74||decode(max(REMOTE_MAPPING_MISMATCH),'Y','REMOTE_MAPPING_MISMATCH '||': '||count(*)||' | ') 75||decode(max(LOAD_RUNTIME_HEAP_FAILED),'Y','LOAD_RUNTIME_HEAP_FAILED '||': '||count(*)||' | ') 76||decode(max(HASH_MATCH_FAILED),'Y','HASH_MATCH_FAILED '||': '||count(*)||' | ') 77||decode(max(PURGED_CURSOR),'Y','PURGED_CURSOR '||': '||count(*)||' | ') 78||decode(max(BIND_LENGTH_UPGRADEABLE),'Y','BIND_LENGTH_UPGRADEABLE '||': '||count(*)||' | ') 79||decode(max(USE_FEEDBACK_STATS),'Y','USE_FEEDBACK_STATS '||': '||count(*)||' | ') 80 reason 81from 82 v$sql_shared_cursor 83groupby 84 sql_id, address 85 ) sc 86join v$sqlarea sq 87using (sql_id) 88where1=1 89and version_count > 2 90-- and parsing_schema_name not in ('SYS') 91orderby version_count desc; 92 93 94select * from vw_sql_version_count_reason_lhr where sql_id='XXXX';

bug 12539487

有时候会遇到某些SQL的 V$SQL_SHARED_CURSOR 所有的字段的结果都为N，但是其Version Count还是很高的情况。这种情况主要的原因是存在部分BUG，可能导致 V$SQL_SHARED_CURSOR 的信息不准确。例如：

Bug 12539487 – gv$sql_shared_cursor may not show all reasons to not share a cursor (Doc ID 12539487.8)

所以在Oracle 10g以上版本中可以使用cursortrace来查找High Version Count的原因，打开cursortrace的方法如下所示：

1altersystemsetevents'immediate trace name cursortrace level 577, address <hash_value>';

如需关闭cursortrace，则可以使用以下方式进行关闭：

1altersystemsetevents'immediate trace name cursortrace level 2147483648, address 1';

或者使用以下方式关闭：

1altersessionsetevents'immediate trace name cursortrace level 128 , address <address>';

函数VERSION_RPT

在MOS 438755.1中，Oracle提供了一个专门的脚本程序，用于协助诊断High Version Count问题。运行脚本version_rpt.sql可以创建函数VERSION_RPT。

函数version_rpt具体使用的方法有三个场景：

①　列出Version Count大于某个阈值的报告，以SQL_ID方式显示

1SELECT B.* 2FROM V$SQLAREA A, TABLE(VERSION_RPT(A.SQL_ID)) B 3WHERE LOADED_VERSIONS >= 4;

②　列出Version Count大于某个阈值的报告，以SQL_HASH方式显示

1SELECT B.* 2FROM V$SQLAREA A, TABLE(VERSION_RPT(NULL, A.HASH_VALUE)) B 3WHERE LOADED_VERSIONS >= 4;

③　列出某个特定SQL_ID的Version Count

1SELECT * FROMTABLE(VERSION_RPT('7u75n20ktntsb'));

如何有效减少高版本游标呢？

对于版本过多的SQL，一次软解析甚至不如重新执行一次硬解析来的高效，所以Oracle引入了一系列的控制手段来处理这些特殊的游标。

方案1：配置10503 event

方案1：配置10503 event，可有效减少由于BIND_MISMATCH（主要是BIND_LENGTH_UPGRADEABLE）导致的version count的数量

根据High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)文档，可以如下配置

1ALTERsystemSETEVENTS'10503 trace name context forever, level 4000'; 2 3-- 关闭： ALTER system SET EVENTS '10503 trace name context off';

注意：

1、若是2个节点，则必须同时配置生效，否则可能会触发 ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set (Doc ID 810194.1)

2、该事件建议在session级别配置，否则可能会导致内存空间不足等问题，若在system级别配置，相当于所有的绑定变量都用2000字节的buffer，这会导致内存分配问题，另外，用完及时关闭。

3、根据 Bug 10274265 - Event 10503 does not work at session level (Doc ID 10274265.8)，若数据库大于Versions >= 10.2 且 BELOW 12.1，则在session级别配置该事件并不起作用。

方案2：修改隐含参数"_cursor_obsolete_threshold"

方案2：修改隐含参数

根据 High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1) 的建议，修改隐含参数"_cursor_obsolete_threshold"为1024，并重启数据库。

1altersystemset"_cursor_obsolete_threshold"=1024scope=spfilesid='*';

若数据库为cdb模式，则只能在cdb进行修改。

从Oracle 11.2.0.3开始，Oracle提供了一个隐含参数「_cursor_obsolete_threshold」，其作用是当SQL版本超过这个参数设定后，直接舍弃这个游标，重新解析，从头开始，该隐含参数的默认值为100。如果子游标的数量超过了这个阈值，那么父游标就会被废弃，并且同时重新创建一个新的父游标。

如果Oracle数据库的版本低于11.2.0.3，那么除了需要给系统打Patch（Enhancement Request Bug 10187168 : OBSOLETE PARENT CURSORS IF VERSION COUNT EXCEEDS A THRESHOLD）外，还同时需要设置以下参数：

1-- 11.1.0.7 2SQL> altersystemset"_cursor_features_enabled"=18scope=spfile; 3 4 5-- Oracle 11.2.0.1： 6SQL> altersystemset"_cursor_features_enabled"=34scope=spfile; 7SQL> altersystemsetevent='106001 trace name context forever,level 1024'scope=spfile; 8 9 10-- Oracle 11.2.0.2： 11SQL> altersystemset"_cursor_features_enabled"=1026scope=spfile; 12SQL> altersystemsetevent='106001 trace name context forever,level 1024'scope=spfile;

从Oracle 12.2开始，_cursor_obsolete_threshold的默认值大幅增加（从1024开始为8192）以便支持4096个PDB（而12.1只有252个PDB）。此参数值是在多租户环境中废弃父游标的最大限制，并且不能超过8192。但这个设置并不适用于非CDB环境，因此对于那些数据库，此参数应手动设置为12.1的默认值，即1024. 默认值1024适用于非CDB环境，并且如果出现问题，可以调整相同的参数，应视具体情况而定。

_cursor_obsolete_threshold 首先在11.2.0.3中引入，默认值为100，然后在11.2.0.4中增加到1024

111.2.0.3: _cursor_obsolete_threshold=100 211.2.0.4: _cursor_obsolete_threshold=1024 312.1: _cursor_obsolete_threshold=1024 412.2: _cursor_obsolete_threshold=8192

其它

方案3：修改业务代码

修改业务代码，保证相关的变量类型和表结构的类型都一致。

方案4：修改SQL代码，加伪hint，拆分成多个sql

修改生成的业务SQL，例如根据情况添加hint /*+ xmmup1 */ 、 /*+ xmmup2 */ …. /*+ xmmup10 */ ，等等的，这样的话就相当于把1条SQL语句拆分成了10条SQL，每条SQL的version count自然就降低了。

方案5：绑定执行计划 （不行）

对于高版本游标，可以考虑使用SPM或SQL Profile来绑定执行计划。该操作不需要重启数据库。尤其对于使用主键或惟一键来操作的SQL语句，其执行计划肯定是固定的，对这类SQL完全可以直接绑定执行计划。

但是，经过实验验证，对于使用绑定变量（尤其是BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE）导致的Version Count高的SQL并不能降低其子游标个数。

其它情况没有测试。。。

参考

https://xmmup.com/moshigh-version-count-due-to-bind_mismatch-doc-id-3362681-10503-events.html

https://xmmup.com/mosguzhangpaichu-banbenshugaohigh-version-countdewenti-doc-id-28969231-sql-banbenshuguoa.html

https://xmmup.com/mosjiaobenversion_rpt3_25sqlyongyupaichagaobanbenyoubiaodeyuanyin.html

https://xmmup.com/mosgaobanbenyoubiaoshudesqlyujuzaishengjidao122jigenggaobanbenhouhuidaozhishujukuxingnengxia.html

https://cloud.tencent.com/developer/article/1388964

‍ ‍