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

AiDBA宝典

发布于 2024-04-18 12:37:12

79800

代码可运行

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

运行总次数：0

代码可运行

简介

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

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

image-20240414175413082

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导致不能共享。

例如:

select count(*) from emp;    ->> 1 父, 1 子游标
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp;    ->> 1 父, 2 子游标

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

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

alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;

alter session set use_stored_oulines= OUTLINES2;
select count(*) 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：

  variable a varchar2(10);
  select count(*) from scott.emp where ename = :a ;       ->> 1 PARENT, 1 CHILD

  variable a varchar2(2000);
  select count(*) from scott.emp where ename = :a ;      ->> 1 PARENT, 2 CHILDREN 


  col sql_text format a60
  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 not like '%v$sql%';
  select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='5tvfxfkm0b81p';

  col name format a10
  col DATATYPE_STRING format a20
  select sql_id,child_number,name,datatype_string,max_length from v$sql_bind_capture a where a.sql_id='5tvfxfkm0b81p'  order by CHILD_NUMBER;

  -- alter system flush shared_pool;



  SYS@ORCLCDB> select banner_full from v$version;

  BANNER_FULL
  -----------------------------------------------------------------------------------------
  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  Version 19.3.0.0.0
  SYS@ORCLCDB> variable a varchar2(10);
  SYS@ORCLCDB> select count(*) from scott.emp where ename = :a ;

    COUNT(*)
  ----------
           0

  SYS@ORCLCDB> variable a varchar2(2000);
  SYS@ORCLCDB> select count(*) from scott.emp where ename = :a ; 

    COUNT(*)
  ----------
           0

  SYS@ORCLCDB> select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='5tvfxfkm0b81p';

  SQL_ID                     BI BI
  -------------------------- -- --
  5tvfxfkm0b81p              N  N
  5tvfxfkm0b81p              N  Y


  SYS@ORCLCDB> col sql_text format a60
  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 not like '%v$sql%';

  SQL_TEXT                                                     SQL_ID                     EXECUTIONS CHILD_NUMBER
  ------------------------------------------------------------ -------------------------- ---------- ------------
  select count(*) from scott.emp where ename = :a              5tvfxfkm0b81p                       1            0
  select count(*) from scott.emp where ename = :a              5tvfxfkm0b81p                       1            1


  SYS@ORCLCDB> select sql_id,child_number,name,datatype_string,max_length from v$sql_bind_capture a where a.sql_id='5tvfxfkm0b81p' order by CHILD_NUMBER;

  SQL_ID                     CHILD_NUMBER NAME       DATATYPE_STRING      MAX_LENGTH
  -------------------------- ------------ ---------- -------------------- ----------
  5tvfxfkm0b81p                         0 :A         VARCHAR2(32)                 32
  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 现有子游标的远程基础对象不匹配。比如说：

USER1:
select count(*) from table@remote_db  

USER2:
select count(*) from table@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>。在例子中每次执行后，运行:

select 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> ¨K44K19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N 19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y

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

11.2 中不再有 ROW_LEVEL_SEC_MISMATCH。

相关实验

BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE实验

Insert 语句

 DROP TABLE T_BG_20280414_LHR_02;
 CREATE TABLE T_BG_20280414_LHR_02(N NUMBER,V VARCHAR2(4000), v2 date); 

 CREATE INDEX idx_T_BG_20280414_LHR_02_N on  T_BG_20280414_LHR_02(N);

 SELECT COUNT(*) FROM T_BG_20280414_LHR_02;
 SELECT * FROM T_BG_20280414_LHR_02; 
 truncate table  T_BG_20280414_LHR_02;

 begin     

         FOR i IN 1..3000 LOOP
                    -- alter system flush shared_pool;
                     DECLARE
                        N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 
                        -- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ;

                       V1 VARCHAR2(32767) := lpad('1', i, '1');          
                       --V1 NUMBER := lpad('1', i, '1');          
                      --V1 NVARCHAR2(4000) := lpad('1', i, '1');    
                      --V1 char(4000) := lpad('1', i, '1');


                         V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 
                        -- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 


                     begin  

                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, V1 ,V2; 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1 , V2;
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1 , V2;


                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,V2; 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,V2;    
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) , V2;  
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1),TO_NUMBER(V1) ,V2; 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NCHAR(V1) ,V2; 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NUMBER(V1) ,V2;   



                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, V1 ,TO_CHAR(V2,'YYYY-MM-DD');
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, V1,cast(V2 as timestamp);    
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, TO_CHAR(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,cast(V2 as timestamp);



                                EXECUTE IMMEDIATE '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');
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1,cast(V2 as timestamp);    
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NUMBER(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) ,cast(V2 as timestamp);


                                EXECUTE IMMEDIATE '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');
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1,cast(V2 as timestamp);    
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NUMBER(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 as timestamp);
                                EXECUTE IMMEDIATE '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'); 
                                EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NCHAR(V1) ,cast(V2 as timestamp);        


                     END; 

         END LOOP; 

         COMMIT;

 end; 


 select  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%' ;
 select 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' 
 order by a.sql_id,a.child_number,a.position;

-- select * from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';  
 select a.sql_id,A.child_number,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';



 select  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%' ;


  BEGIN   dbms_shared_pool.keep('000000007A84D5F8,1233586980','C');  END; 

     BEGIN dbms_shared_pool.PURGE('000000007A84D5F8,1233586980','C');  END; 


  SELECT * FROM TABLE(VERSION_RPT('610ygu94sf2t4'));    


 select * from vw_sql_version_count_reason_lhr where sql_id='610ygu94sf2t4';   




select * from table(DBMS_XPLAN.DISPLAY_CURSOR('610ygu94sf2t4',0,'all'));



 select  a.plan_hash_value from v$sql a where sql_id='610ygu94sf2t4';                                                         


   ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; 

SELECT * FROM DBA_SQL_PLAN_BASELINES;
SELECT * FROM DBA_SQLSET_PLANS;
SELECT * FROM DBA_ADVISOR_SQLPLANS;



    ALTER system SET EVENTS '10503 trace name context forever, level 4000'; 
    ALTER system SET EVENTS '10503 trace name context off';


      alter system set "_cursor_obsolete_threshold"=10 scope=spfile sid='*';





--  alter index idx_T_BG_20280414_LHR_02_N monitoring usage; 
-- alter index idx1 nomonitoring USAGE;

-- SELECT * FROM V$OBJECT_USAGE;






Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 16-apr-24 14:58
RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB
==================================================================
Addr: 000000007A84D5F8  Hash_Value: 1233586980  SQL_ID 610ygu94sf2t4
Sharable_Mem: 1497694 bytes   Parses: 111028   Execs:151018
Stmt: 
0 INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)
1 

Versions Summary
----------------
BIND_MISMATCH :66
BIND_LENGTH_UPGRADEABLE :62

Total Versions:69

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
cursor_sharing = EXACT
_cursor_obsolete_threshold = 8192 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
              0 70

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :

Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
======== ======== =============== =============== ======== =============== =================
      37        1              32              32        1     No           (,)
      33        1              22              22        2     No           (,)
      57        2              32            8192        1     Yes          (,)
       5        2              22              22        2     No           (,)
       8        2            4000            4000       96     No           (,)
      16        3              32              32        1     No           (,)
      26        3               7               7       12     No           (,)
      23        3              11              11      180     No           (,6)
       5        3              11              11      180     No           (,9)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
          0                 0             0           26
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_LENGTH_UPGRADEABLE :

Details shown already.
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events 
 'immediate trace name cursortrace address 1233586980, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

update语句

 begin     

     FOR i IN 1..2100   LOOP
          -- alter system flush shared_pool;
            IF MOD(i, 16) = 0 THEN 
           DECLARE
            N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; 
            -- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ;

             V1 VARCHAR2(32767) := lpad('1', i, '1');          
             --V1 NUMBER := lpad('1', i, '1');          
              --V1 NVARCHAR2(4000) := lpad('1', i, '1');    
            --V1 char(4000) := lpad('1', i, '1');


             V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 
            -- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; 


           begin  



                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1 ,V2,N1; 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1 ,V2,TO_CHAR(N1);
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1 ,V2,TO_NCHAR(N1);


                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) ,V2,N1; 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,V2,N1;    
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) , V2, TO_CHAR(N1);  
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,V2, TO_CHAR(N1); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) ,V2,TO_NCHAR(N1); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,V2,TO_NCHAR(N1);   



                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1 ,TO_CHAR(V2,'YYYY-MM-DD'),N1;
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1,cast(V2 as timestamp),N1;    
                                    EXECUTE IMMEDIATE '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; 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,cast(V2 as timestamp),N1;
                                    EXECUTE IMMEDIATE '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; 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_CHAR(V1) ,cast(V2 as timestamp),N1;
                                    EXECUTE IMMEDIATE '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; 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) ,cast(V2 as timestamp),N1;



                                    EXECUTE IMMEDIATE '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);
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1,cast(V2 as timestamp), TO_CHAR(N1);    
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,cast(V2 as timestamp), TO_CHAR(N1);
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_CHAR(V1) ,cast(V2 as timestamp), TO_CHAR(N1);
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) ,cast(V2 as timestamp), TO_CHAR(N1);


                                    EXECUTE IMMEDIATE '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);
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  V1,cast(V2 as timestamp), TO_NCHAR(N1);    
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NUMBER(V1) ,cast(V2 as timestamp), TO_NCHAR(N1);
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_CHAR(V1) ,cast(V2 as timestamp), TO_NCHAR(N1);
                                    EXECUTE IMMEDIATE '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); 
                                    EXECUTE IMMEDIATE 'update  T_BG_20280414_LHR_02 set v=:v1,v2=:v2 where n=:n1'  USING  TO_NCHAR(V1) ,cast(V2 as timestamp), TO_NCHAR(N1);        





           END; 

             END IF;

     END LOOP; 

     COMMIT;


 end;   





 select  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%' ;

 select 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' 
 order by a.sql_id,a.child_number,a.position;

-- select * from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';  
 select a.sql_id,A.child_number,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='0dbvtumzg263r';



 select  address,hash_value ,plan_hash_value from v$sql a where a.sql_text like '%update  T_BG_20280414_LHR_02 set%' ;


  BEGIN   dbms_shared_pool.keep('000000006674C050,4277213303','C');  END;
     BEGIN dbms_shared_pool.PURGE('000000006674C050,4277213303','C');  END; 


 BEGIN   dbms_shared_pool.unkeep('000000006674C050,4277213303','C');  END;

  SELECT * FROM TABLE(VERSION_RPT('0dbvtumzg263r'));    


 select * from vw_sql_version_count_reason_lhr where sql_id='0dbvtumzg263r';   




select * from table(DBMS_XPLAN.DISPLAY_CURSOR('0dbvtumzg263r',1,'all'));



 select  a.plan_hash_value from v$sql a where sql_id='610ygu94sf2t4';                             


   ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
SELECT * FROM DBA_SQL_PLAN_BASELINES;
SELECT * FROM DBA_SQLSET_PLANS;
SELECT * FROM DBA_ADVISOR_SQLPLANS;




    ALTER system SET EVENTS '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值后，下次解析时就会被置为失效。

实验：

alter system set "_optimizer_invalidation_period"=60;

create table X as select * from dba_tables;
exec dbms_stats.gather_table_stats(null,'X');


select count(*) from X;
select 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%';
select * from v$sql_shared_cursor where sql_id='c3zk14hnh9ydw';

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

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

SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0;
no rows selected
SYS@lhrdb> select sql_id, version_count from v$sqlarea where sql_text like 'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%';
SQL_ID        VERSION_COUNT
------------- -------------
7u75n20ktntsb             1
SYS@lhrdb>  show parameter optimizer_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SYS@lhrdb>  alter session set optimizer_mode=first_rows;
Session altered.
SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0;
no rows selected
SYS@lhrdb> select sql_id, version_count,SQL_TEXT from v$sqlarea where sql_text like 'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%';
SQL_ID        VERSION_COUNT
------------- -------------
7u75n20ktntsb             2
SYS@lhrdb>  select sql_id, child_number, OPTIMIZER_MODE from v$sql where sql_id='7u75n20ktntsb';
SQL_ID        CHILD_NUMBER OPTIMIZER_
------------- ------------ ----------
7u75n20ktntsb            0 ALL_ROWS
7u75n20ktntsb            1 FIRST_ROWS
SYS@lhrdb> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='7u75n20ktntsb';
SQL_ID        CHILD_NUMBER O
------------- ------------ -
7u75n20ktntsb            0 N
7u75n20ktntsb            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：

alter system flush shared_pool;

create table X as select * from dba_tables;

set time on pages 1000 lin 200

col FIRST_LOAD_TIME format a20

col last_load_time format a20

var b1 varchar2(128);

var b2 varchar2(128);

exec :b1 := '0';         

exec :b2 := '0';

nls_sort

alter session set = 'SCHINESE_RADICAL_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;

--- 修改 nls_sort

alter session set nls_sort = 'SCHINESE_STROKE_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;

-- 修改 nls_sort

alter session set nls_sort = 'SCHINESE_PINYIN_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;


select sql_id from v$sql where sql_text='select count(*) from X where table_name=:b1 and OWNER=:b2';
select sql_id,child_address,language_mismatch,roll_invalid_mismatch,BIND_MISMATCH from v$sql_shared_cursor where sql_id='1uk1h0wuzcuaf';

测试2：

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


Language Mismatch Not Found With CURSOR_SHARING = FORCE as follows:

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

version count高的原因查询

create or replace view vw_sql_version_count_reason_lhr as 
select sql_id,
       sc.address,  
       hash_value,
        sq.executions,
       version_count,
       parsing_schema_name, 
             sq.loads,
             sq.parse_calls, 
             sq.last_load_time,
       reason,
       sql_text
  from (
select
 sql_id, address, 'reason => '
||decode(max(UNBOUND_CURSOR),'Y','UNBOUND_CURSOR '||': '||count(*)||' |  ')
||decode(max(SQL_TYPE_MISMATCH),'Y','SQL_TYPE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(OPTIMIZER_MISMATCH),'Y','OPTIMIZER_MISMATCH '||': '||count(*)||' |  ')
||decode(max(OUTLINE_MISMATCH),'Y','OUTLINE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(STATS_ROW_MISMATCH),'Y','STATS_ROW_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LITERAL_MISMATCH),'Y','LITERAL_MISMATCH '||': '||count(*)||' |  ')
||decode(max(FORCE_HARD_PARSE),'Y','FORCE_HARD_PARSE '||': '||count(*)||' |  ')
||decode(max(EXPLAIN_PLAN_CURSOR),'Y','EXPLAIN_PLAN_CURSOR '||': '||count(*)||' |  ')
||decode(max(BUFFERED_DML_MISMATCH),'Y','BUFFERED_DML_MISMATCH '||': '||count(*)||' |  ')
||decode(max(PDML_ENV_MISMATCH),'Y','PDML_ENV_MISMATCH '||': '||count(*)||' |  ')
||decode(max(INST_DRTLD_MISMATCH),'Y','INST_DRTLD_MISMATCH '||': '||count(*)||' |  ')
||decode(max(SLAVE_QC_MISMATCH),'Y','SLAVE_QC_MISMATCH '||': '||count(*)||' |  ')
||decode(max(TYPECHECK_MISMATCH),'Y','TYPECHECK_MISMATCH '||': '||count(*)||' |  ')
||decode(max(AUTH_CHECK_MISMATCH),'Y','AUTH_CHECK_MISMATCH '||': '||count(*)||' |  ')
||decode(max(BIND_MISMATCH),'Y','BIND_MISMATCH '||': '||count(*)||' |  ')
||decode(max(DESCRIBE_MISMATCH),'Y','DESCRIBE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LANGUAGE_MISMATCH),'Y','LANGUAGE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(TRANSLATION_MISMATCH),'Y','TRANSLATION_MISMATCH '||': '||count(*)||' |  ')
||decode(max(BIND_EQUIV_FAILURE),'Y','BIND_EQUIV_FAILURE '||': '||count(*)||' |  ')
||decode(max(INSUFF_PRIVS),'Y','INSUFF_PRIVS '||': '||count(*)||' |  ')
||decode(max(INSUFF_PRIVS_REM),'Y','INSUFF_PRIVS_REM '||': '||count(*)||' |  ')
||decode(max(REMOTE_TRANS_MISMATCH),'Y','REMOTE_TRANS_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LOGMINER_SESSION_MISMATCH),'Y','LOGMINER_SESSION_MISMATCH '||': '||count(*)||' |  ')
||decode(max(INCOMP_LTRL_MISMATCH),'Y','INCOMP_LTRL_MISMATCH '||': '||count(*)||' |  ')
||decode(max(OVERLAP_TIME_MISMATCH),'Y','OVERLAP_TIME_MISMATCH '||': '||count(*)||' |  ')
||decode(max(EDITION_MISMATCH),'Y','EDITION_MISMATCH '||': '||count(*)||' |  ')
||decode(max(MV_QUERY_GEN_MISMATCH),'Y','MV_QUERY_GEN_MISMATCH '||': '||count(*)||' |  ')
||decode(max(USER_BIND_PEEK_MISMATCH),'Y','USER_BIND_PEEK_MISMATCH '||': '||count(*)||' |  ')
||decode(max(TYPCHK_DEP_MISMATCH),'Y','TYPCHK_DEP_MISMATCH '||': '||count(*)||' |  ')
||decode(max(NO_TRIGGER_MISMATCH),'Y','NO_TRIGGER_MISMATCH '||': '||count(*)||' |  ')
||decode(max(FLASHBACK_CURSOR),'Y','FLASHBACK_CURSOR '||': '||count(*)||' |  ')
||decode(max(ANYDATA_TRANSFORMATION),'Y','ANYDATA_TRANSFORMATION '||': '||count(*)||' |  ')
||decode(max(PDDL_ENV_MISMATCH),'Y','PDDL_ENV_MISMATCH '||': '||count(*)||' |  ')
||decode(max(TOP_LEVEL_RPI_CURSOR),'Y','TOP_LEVEL_RPI_CURSOR '||': '||count(*)||' |  ')
||decode(max(DIFFERENT_LONG_LENGTH),'Y','DIFFERENT_LONG_LENGTH '||': '||count(*)||' |  ')
||decode(max(LOGICAL_STANDBY_APPLY),'Y','LOGICAL_STANDBY_APPLY '||': '||count(*)||' |  ')
||decode(max(DIFF_CALL_DURN),'Y','DIFF_CALL_DURN '||': '||count(*)||' |  ')
||decode(max(BIND_UACS_DIFF),'Y','BIND_UACS_DIFF '||': '||count(*)||' |  ')
||decode(max(PLSQL_CMP_SWITCHS_DIFF),'Y','PLSQL_CMP_SWITCHS_DIFF '||': '||count(*)||' |  ')
||decode(max(CURSOR_PARTS_MISMATCH),'Y','CURSOR_PARTS_MISMATCH '||': '||count(*)||' |  ')
||decode(max(STB_OBJECT_MISMATCH),'Y','STB_OBJECT_MISMATCH '||': '||count(*)||' |  ')
||decode(max(CROSSEDITION_TRIGGER_MISMATCH),'Y','CROSSEDITION_TRIGGER_MISMATCH '||': '||count(*)||' |  ')
||decode(max(PQ_SLAVE_MISMATCH),'Y','PQ_SLAVE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(TOP_LEVEL_DDL_MISMATCH),'Y','TOP_LEVEL_DDL_MISMATCH '||': '||count(*)||' |  ')
||decode(max(MULTI_PX_MISMATCH),'Y','MULTI_PX_MISMATCH '||': '||count(*)||' |  ')
||decode(max(BIND_PEEKED_PQ_MISMATCH),'Y','BIND_PEEKED_PQ_MISMATCH '||': '||count(*)||' |  ')
||decode(max(MV_REWRITE_MISMATCH),'Y','MV_REWRITE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(ROLL_INVALID_MISMATCH),'Y','ROLL_INVALID_MISMATCH '||': '||count(*)||' |  ')
||decode(max(OPTIMIZER_MODE_MISMATCH),'Y','OPTIMIZER_MODE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(PX_MISMATCH),'Y','PX_MISMATCH '||': '||count(*)||' |  ')
||decode(max(MV_STALEOBJ_MISMATCH),'Y','MV_STALEOBJ_MISMATCH '||': '||count(*)||' |  ')
||decode(max(FLASHBACK_TABLE_MISMATCH),'Y','FLASHBACK_TABLE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LITREP_COMP_MISMATCH),'Y','LITREP_COMP_MISMATCH '||': '||count(*)||' |  ')
||decode(max(PLSQL_DEBUG),'Y','PLSQL_DEBUG '||': '||count(*)||' |  ')
||decode(max(LOAD_OPTIMIZER_STATS),'Y','LOAD_OPTIMIZER_STATS '||': '||count(*)||' |  ')
||decode(max(ACL_MISMATCH),'Y','ACL_MISMATCH '||': '||count(*)||' |  ')
||decode(max(FLASHBACK_ARCHIVE_MISMATCH),'Y','FLASHBACK_ARCHIVE_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LOCK_USER_SCHEMA_FAILED),'Y','LOCK_USER_SCHEMA_FAILED '||': '||count(*)||' |  ')
||decode(max(REMOTE_MAPPING_MISMATCH),'Y','REMOTE_MAPPING_MISMATCH '||': '||count(*)||' |  ')
||decode(max(LOAD_RUNTIME_HEAP_FAILED),'Y','LOAD_RUNTIME_HEAP_FAILED '||': '||count(*)||' |  ')
||decode(max(HASH_MATCH_FAILED),'Y','HASH_MATCH_FAILED '||': '||count(*)||' |  ')
||decode(max(PURGED_CURSOR),'Y','PURGED_CURSOR '||': '||count(*)||' |  ')
||decode(max(BIND_LENGTH_UPGRADEABLE),'Y','BIND_LENGTH_UPGRADEABLE '||': '||count(*)||' |  ')
||decode(max(USE_FEEDBACK_STATS),'Y','USE_FEEDBACK_STATS '||': '||count(*)||' |  ')
 reason
from
   v$sql_shared_cursor
group by
   sql_id, address
       ) sc
  join v$sqlarea sq
 using (sql_id)
 where 1=1
 and  version_count > 2
-- and parsing_schema_name not in ('SYS')
 order by version_count desc;


 select * from vw_sql_version_count_reason_lhr where sql_id='XXXX';

bug 12539487

有时候会遇到某些SQL的VSQL_SHARED_CURSOR所有的字段的结果都为N，但是其Version Count还是很高的情况。这种情况主要的原因是存在部分BUG，可能导致VSQL_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的方法如下所示：

alter system set events 'immediate trace name cursortrace level 577, address <hash_value>';

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

alter system set events 'immediate trace name cursortrace level 2147483648, address 1';

或者使用以下方式关闭：

alter session set events '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方式显示

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

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

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

③　列出某个特定SQL_ID的Version Count

SELECT * FROM TABLE(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)文档，可以如下配置

ALTER system SET EVENTS '10503 trace name context forever, level 4000';

-- 关闭：   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，并重启数据库。

alter system set "_cursor_obsolete_threshold"=1024 scope=spfile sid='*';

若数据库为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）外，还同时需要设置以下参数：

-- 11.1.0.7
SQL> alter system set "_cursor_features_enabled"=18 scope=spfile;


-- Oracle 11.2.0.1：
SQL> alter system set "_cursor_features_enabled"=34 scope=spfile;
SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;


-- Oracle 11.2.0.2：
SQL> alter system set "_cursor_features_enabled"=1026 scope=spfile;
SQL> alter system set event='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

11.2.0.3: _cursor_obsolete_threshold=100
11.2.0.4: _cursor_obsolete_threshold=1024
12.1: _cursor_obsolete_threshold=1024
12.2: _cursor_obsolete_threshold=8192