什么是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

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

  • LANGUAGE_MISMATCH语言句柄与现有的子游标不匹配。

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

  • ROW_LEVEL_SEC_MISMATCH 行级安全策略不匹配。

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

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

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

    1USER1:
    2select count(*) from table@remote_db  
    3
    4USER2:
    5select 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的滚动无效功能引起的。因为它的无效窗口已经超过了，所以子游标不能被共享。参考： Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

  • 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自适应游标共享并且游标是绑定感知的，那么如果选择性超出了当前的范围，并且新的计划是可取的，那么就会产生一个新的子游标，其原因代码是不共享以前的计划。

    常见原因：

    ① 由于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

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

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

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

    1SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
    2------------- ---------------- ---------------- ------------ -
    319sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N
    419sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y

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

11.2 中不再有 ROW_LEVEL_SEC_MISMATCH。

BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE实验

1 DROP TABLE T_BG_20280414_LHR_02;
2 CREATE TABLE T_BG_20280414_LHR_02(N NUMBER(10),V VARCHAR2(1000), v2 date);
3
4
5-- alter system flush shared_pool;
6 DECLARE
7   N1 NUMBER(10) :=1;            
8   -- V1 VARCHAR2(4000) :='1'; 
9   --V1 VARCHAR2(4000) :='11111111111111111111';  -- 20 
10    --  V1 VARCHAR2(4000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100 
11   --V1 VARCHAR2(4000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 200
12
13     --  V1 NUMBER :='1'; 
14      --  V1 char :='1'; 
15     V1 NVARCHAR2(100) :='1';   
16
17      --  V2 DATE := sysdate; 
18     V2 TIMESTAMP := sysdate+100 ; 
19
20
21   --V1 VARCHAR2(100) :='1'; 
22   -- V1 VARCHAR2(2000) :='11111111111111111111';  -- 20 
23  -- V1 VARCHAR2(3000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100 
24 --  V1 VARCHAR2(3000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 200
25
26
27 BEGIN
28   EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, V1 ,V2;
29   COMMIT;
30 END;
31
32
33
34 select * from v$sql a where a.sql_text like '%INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES%' ;
35 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' 
36 order by a.sql_id,a.child_number,a.position;
37
38-- select * from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';  
39 select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';
40
41
42
43  SELECT * FROM TABLE(VERSION_RPT('610ygu94sf2t4'));    
44
45
46
47    Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 15-apr-24 16:29
48RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB
49==================================================================
50Addr: 000000007861B2F0  Hash_Value: 1233586980  SQL_ID 610ygu94sf2t4
51Sharable_Mem: 129542 bytes   Parses: 8   Execs:6
52Stmt: 
530 INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)
541 
55
56Versions Summary
57----------------
58BIND_MISMATCH :3
59HASH_MATCH_FAILED :5
60BIND_LENGTH_UPGRADEABLE :5
61
62Total Versions:5
63
64~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
65cursor_sharing = EXACT
66_cursor_obsolete_threshold = 8192 (See Note:10187168.8)
67~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
68
69Plan Hash Value Summary
70-----------------------
71Plan Hash Value Count
72=============== =====
73              0 6
74
75~~~~~~~~~~~~~~~~~~~~~~~~~~~
76Details for BIND_MISMATCH :
77
78Consolidated details for BIND* columns:
79BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
80BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
81
82from v$sql_bind_capture
83COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
84======== ======== =============== =============== ======== =============== =================
85       6        1              22              22        2     No           (,)
86       5        2              32            2000        1     Yes          (,)
87       1        2              32              32       96     No           (,)
88       3        3               7               7       12     No           (,)
89       3        3              11              11      180     No           (,6)
90
91SUM(DECODE(column,Y, 1, 0) FROM V$SQL
92IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
93=========== ================= ============= ============
94          0                 0             0            4
95~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
96Details for HASH_MATCH_FAILED :
97
98No details available
99~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
100Details for BIND_LENGTH_UPGRADEABLE :
101
102Details shown already.
103####
104To further debug Ask Oracle Support for the appropiate level LLL.
105alter session set events 
106 'immediate trace name cursortrace address 1233586980, level LLL';
107To turn it off do use address 1, level 2147483648
108================================================================

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

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

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

1SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE ROWNUM select sql_id, version_count from v$sqlarea where sql_text like 'SELECT * FROM SCOTT.EMP WHERE ROWNUM