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

小麦苗DBA宝典

发布于 2024-04-16 11:26:50

880

发布于 2024-04-16 11:26:50

简介

一个父游标下对应的子游标个数被称为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的滚动无效功能引起的。因为它的无效窗口已经超过了，所以子游标不能被共享。参考： 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

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

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

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

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y

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

11.2 中不再有 ROW_LEVEL_SEC_MISMATCH。

BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE实验

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


-- alter system flush shared_pool;
 DECLARE
   N1 NUMBER(10) :=1;            
   -- V1 VARCHAR2(4000) :='1'; 
   --V1 VARCHAR2(4000) :='11111111111111111111';  -- 20 
    --  V1 VARCHAR2(4000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100 
   --V1 VARCHAR2(4000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 200

     --  V1 NUMBER :='1'; 
      --  V1 char :='1'; 
     V1 NVARCHAR2(100) :='1';   

      --  V2 DATE := sysdate; 
     V2 TIMESTAMP := sysdate+100 ; 


   --V1 VARCHAR2(100) :='1'; 
   -- V1 VARCHAR2(2000) :='11111111111111111111';  -- 20 
  -- V1 VARCHAR2(3000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100 
 --  V1 VARCHAR2(3000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 200


 BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)' USING N1, V1 ,V2;
   COMMIT;
 END;



 select * 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.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='610ygu94sf2t4';



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



    Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 15-apr-24 16:29
RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB
==================================================================
Addr: 000000007861B2F0  Hash_Value: 1233586980  SQL_ID 610ygu94sf2t4
Sharable_Mem: 129542 bytes   Parses: 8   Execs:6
Stmt: 
0 INSERT INTO T_BG_20280414_LHR_02(N,V,V2)  VALUES(:N1,:V1,:V2)
1 

Versions Summary
----------------
BIND_MISMATCH :3
HASH_MATCH_FAILED :5
BIND_LENGTH_UPGRADEABLE :5

Total Versions:5

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

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)
======== ======== =============== =============== ======== =============== =================
       6        1              22              22        2     No           (,)
       5        2              32            2000        1     Yes          (,)
       1        2              32              32       96     No           (,)
       3        3               7               7       12     No           (,)
       3        3              11              11      180     No           (,6)

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

No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
================================================================

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

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

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

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

bug

有时候会遇到某些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：绑定执行计划

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

方案2：配置10503 event，可有效减少由于BIND_MISMATCH导致的version count的数量

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

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

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

方案3：修改隐含参数

根据 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='*';

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

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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-04-15，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle