【DB笔试面试580】在Oracle中，什么是High Version Count？
【DB笔试面试580】在Oracle中，什么是High Version Count？
♣
题目部分
在Oracle中，什么是High Version Count？
♣
答案部分
一个父游标下对应的子游标个数被称为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;
& 说明：
有关Version Count的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140136/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。