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

数据运维 2024-04-16 大树手机阅读

简介

一个父游标下对应的子游标个数被称为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导致不能共享。

例如:

1select count(*) from emp; ->> 1 父, 1 子游标 2alter session set optimizer_mode=ALL_ROWS 3select count(*) from emp; ->> 1 父, 2 子游标 
注: 该行为适用于跟踪事件的设置。例如，如果使用 10046 打开跟踪，将新添加一个由于OPTIMIZER_MISMATCH引起的子游标。
OUTLINE_MISMATCHOUTLINES 与现有的子游标不一致。例如：如果用户之前为这个SQL创建了存储OUTLINES，并且这些OUTLINES被存储在不同的分类里(称为"OUTLINES1" 和 "OUTLINES2")。如果执行下面的命令：

1alter session set use_stored_outlines = OUTLINES1; 2select count(*) from emp; 3 4alter session set use_stored_oulines= OUTLINES2; 5select 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 variable a varchar2(10); 2 select count(*) from scott.emp where ename = :a ; ->> 1 PARENT, 1 CHILD 3 4 variable a varchar2(2000); 5 select count(*) from scott.emp where ename = :a ; ->> 1 PARENT, 2 CHILDREN 6 7 8 col sql_text format a60 9 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%'; 10 select 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 14 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; 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 Release 19.0.0.0.0 - Production 25 Version 19.3.0.0.0 26 SYS@ORCLCDB> variable a varchar2(10); 27 SYS@ORCLCDB> select count(*) from scott.emp where ename = :a ; 28 29 COUNT(*) 30 ---------- 31 0 32 33 SYS@ORCLCDB> variable a varchar2(2000); 34 SYS@ORCLCDB> select count(*) 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 not like '%v$sql%'; 50 51 SQL_TEXT SQL_ID EXECUTIONS CHILD_NUMBER 52 ------------------------------------------------------------ -------------------------- ---------- ------------ 53 select count(*) from scott.emp where ename = :a 5tvfxfkm0b81p 1 0 54 select count(*) from scott.emp where ename = :a 5tvfxfkm0b81p 1 1 55 56 57 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; 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

1USER1: 2select count(*) from table@remote_db 3 4USER2: 5select count(*) from table@remote_db 