转载自：小麦苗 公众号，DBA宝典   DB笔试面试题

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

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

SQL 执行计划的生成，是受到很多因素影响的。相同父游标只是表示输入 SQL 的字面值相同。子游标对应的因素，如优化器类型、优化器的模式（ OPTIMIZER_MODE ）、对应对象权限等的差异，都会影响到子游标的共享。

在Oracle 11g 中， V$SQL_SHARED_CURSOR 可以用来诊断子游标不共享问题的原因。该视图通过 SQL_ID 和 CHILD_NUMBER 就可以定义某个特定子游标的信息。该视图中大部分列都是以 VARCHAR2(1) 的 Y/N 取值，每列的含义都是一个不能共享的理由。需要注意的是，这个理由 N 表示的是不能与第一个子游标（ CHILD_NUMBER=0 ）共享的理由。

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

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

有时候会遇到某些SQL 的 V$SQL_SHARED_CURSOR 所有的字段的结果都为 N ，但是其 Version Count 还是很高的情况。这种情况主要的原因是存在部分 BUG ，可能导致 V$SQL_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>';

在MOS 438755.1 中， Oracle 提供了一个专门的脚本程序，用于协助诊断 High Version Count 问题。运行脚本 version_rpt.sql 可以创建函数 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 引入了一系列的控制手段来处理这些特殊的游标。从 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;