原 什么是Oracle的高版本游标（High Version Count）？如何排查？（持续更新）
Tags： Oracle原创版本数高(High Version Count)高版本游标
- 简介
- AWR报告中SQL统计部分的执行次数和每次执行时间为空的说明 (Doc ID 1522547.1)
- 游标不共享的原因整理
- 相关实验
- BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE实验
- Insert 语句
- update语句
- ROLL_INVALID_MISMATCH
- OPTIMIZER_MODE_MISMATCH--由于优化器模式不同导致游标不共享的示例
- LANGUAGE_MISMATCH
- version count高的原因查询
- bug 12539487
- 函数VERSION_RPT
- 如何有效减少高版本游标呢？
- 方案1：配置10503 event
- 方案2：修改隐含参数"_cursor_obsolete_threshold"
- 其它
- 参考
简介
一个父游标下对应的子游标个数被称为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)。
游标不共享的原因整理
在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导致不能共享。
例如:
123select count(*) from emp; ->> 1 父, 1 子游标alter session set optimizer_mode=ALL_ROWSselect count(*) from emp; ->> 1 父, 2 子游标注: 该行为适用于跟踪事件的设置。例如，如果使用 10046 打开跟踪，将新添加一个由于OPTIMIZER_MISMATCH引起的子游标。
OUTLINE_MISMATCHOUTLINES 与现有的子游标不一致。例如：如果用户之前为这个SQL创建了存储OUTLINES，并且这些OUTLINES被存储在不同的分类里(称为"OUTLINES1" 和 "OUTLINES2")。如果执行下面的命令：
12345alter 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：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | 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 现有子游标的远程基础对象不匹配。比如说：
12345USER1:select count(*) from table@remote_dbUSER2:select count(*) from table@remote_db尽管SQL是相同的，但remote_db所指向的dblink可能是一个私有的dblink，它解析到一个完全不同的对象。
LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
