Oracle SQL调优是我们进行应用系统调优的一个重要方面。一个SQL的书写方式、执行计划和执行时间会显著的影响到系统的投产性能。本篇我们主要聊一下SQL version count数目过多，子游标过多的问题诊断。
1、关于version count
首先我们一起来回顾一下Oracle的“父子游标”（child cursor and parent cursor）概念。在Oracle中，任何一个执行的SQL语句，都会以游标cursor的形式进行处理。具体来说，一个SQL要涉及到两个游标对象，共享游标shared cursor和私有游标private cursor。
Private cursor是驻留在Oracle Server Process PGA内部，只能被一个Server Process对应的会话使用。而shared cursor驻留在SGA的shared pool中，具体位于shared pool中的library cache。
对shared cursor而言，主要缓存的目的在于执行计划的共享。一个SQL经过validate和parse过程，会形成父游标和子游标的配对组合。SQL文本完全相同的语句，会共享父游标。而环境信息、对象信息相同的SQL语句，才可能共享子游标。当找不到对应的子游标或者父游标时，也就意味着找不到可共享的执行计划。这样对应的SQL就需要发生hard parse，重新生成执行计划。
一个父游标下对应的子游标个数，我们成为version count。每一个子游标对应一个执行计划对象。下面通过示例来演示，依然选择Oracle 11g环境进行试验。
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
构建数据表T。
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
我们使用实验SQL，通过视图v$sqlarea和v$sql可以观察到library cache中的父子游标情况。v$sqlarea中保存父游标信息，而v$sql保存子游标信息。
SQL> select /*+version_count */count(*) from t where wner='SCOTT';
COUNT(*)
----------
14
SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';
SQL_ID VERSION_COUNT
------------- -------------
54fuganxkyky6 1
SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER
------------- ------------
54fuganxkyky6 0
SQL语句（sql_id=54fuganxkyky6）对应一个父游标和一个子游标。
version count就表示当前父游标下对应子游标的个数。如果一个父游标对应的子游标version count过多，也就是对应了很多的子游标对象。这样，当server process检查可共享的游标时，就需要长时间的检索子游标列表。
最有名的version count过多问题是由于设置cursor_sharing参数为similar后，引发的version count错误。
此外，version count过多也是我们需要诊断SQL为什么不会共享的一个出发点。比较常用的有两种，本篇中进行详细介绍：
2、v$sql_shared_cursor视图
SQL执行计划的生成，是受到很多因素影响的。相同父游标只是表示输入SQL的字面值相同。子游标对应的因素，如优化器类型、optimizer_mode、对应对象权限等的差异，都会影响到子游标的共享。
在Oracle 11g中，一个新的视图被提供出来，用于帮助诊断子游标不共享问题的原因，就是v$sql_shared_cursor。
SQL> desc v$sql_shared_cursor;
Name Type Nullable Default Comments
----------------------------- ------------ -------- ------- --------
SQL_ID VARCHAR2(13) Y
ADDRESS RAW(4) Y
CHILD_ADDRESS RAW(4) Y
CHILD_NUMBER NUMBER Y
UNBOUND_CURSOR VARCHAR2(1) Y
SQL_TYPE_MISMATCH VARCHAR2(1) Y
OPTIMIZER_MISMATCH VARCHAR2(1) Y
OUTLINE_MISMATCH VARCHAR2(1) Y
（篇幅原因，省略……）
PDML_ENV_MISMATCH VARCHAR2(1) Y
INST_DRTLD_MISMATCH VARCHAR2(1) Y
BIND_LENGTH_UPGRADEABLE VARCHAR2(1) Y
该视图是一个宽列视图，通过sql_id和child_number就可以定义某个特定子游标的信息。其他大部分列都是以varchar2(1)的Y/N取值，每列的含义都是一个不能共享的理由。注意：这个理由N表示的是不能共享第一个子游标child_number=0的理由。下面通过简单的示例演示，继续上面的实验。
SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER
------------- ------------
54fuganxkyky6 0
通过变换环境信息来生成新的子游标。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
--会话层面取值变化；
SQL> alter session set optimizer_mode=first_rows;
Session altered
SQL> select value from v$parameter where name='optimizer_mode';
VALUE
-----------------------------------------------------------------
FIRST_ROWS
重新执行SQL，检查执行计划。
SQL> select /*+version_count */count(*) from t where wner='SCOTT';
COUNT(*)
----------
14
SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';
SQL_ID VERSION_COUNT
------------- -------------
54fuganxkyky6 2
SQL> select sql_id, child_number, OPTIMIZER_MODE from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER OPTIMIZER_MODE
------------- ------------ --------------
54fuganxkyky6 0 ALL_ROWS
54fuganxkyky6 1 FIRST_ROWS
生成了两个执行计划。此时，如果我们检查v$sql_shared_cursor，就可以发现非共享的原因。
SQL> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER OPTIMIZER_MODE_MISMATCH
------------- ------------ -----------------------
54fuganxkyky6 0 N
54fuganxkyky6 1 Y
在实际中，使用v$sql_shared_cursor就可以确定非共享SQL子游标的原因。
3、version-rpt脚本
在MOS438755.1中，Oracle提供了一个专门的脚本程序，用于协助诊断high SQL version count问题。
首先，我们需要从MOS上下载到脚本version_rpt.sql，目前笔者的版本为3.1.2。使用sys用户登录之后，调用该脚本创建相应的数据库对象。
SQL> @version_rpt3_12.sql
视图已创建。
函数已创建。
注意，在笔者的实验中，直接从MOS上下载的文件是不能正确编译通过的。要将下面片段进行修改：
--原有结构
SELECT COLUMN_NAME,0
from cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
--修改之后
SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
from dba_tab_cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
借助创建的函数version_rpt，可以详细分析version count问题。具体使用的方法有三个场景：
ü 列出version count大于某个阈值的报告，以sql_id方式显示
SQL> select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=4;
COLUMN_VALUE
----------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:20
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
415205717 4
COLUMN_VALUE
-----------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for SQL_TYPE_MISMATCH :
No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for :
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF 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 (PRECISION,SCALE)
======== ======== =============== =============== ======== ================
4 1 32 32 1 (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 0 0 4
####
COLUMN_VALUE
--------------------------------------------------------------------------------
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3393782897, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
46 rows selected
ü 列出version count大于某个阈值的报告，以sql_hash方式显示
SQL> select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=4;
COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:22
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
415205717 4
（篇幅原因，省略……）
ü 列出某个特定sql_id的version count
SQL> select * from table(version_rpt('9p6bq1v54k13j'));
COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:23
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
三种方法，都可以很直观的帮助我们定位问题和错误。
4、结论
version count问题是Oracle SQL共享的一个方面。借助适当的工具手段，我们可以准确的定位问题，解决问题。