我们经常会在SQL相关的动态性能视图中遇VERSION_COUNT，这个字段表示这个SQL在shared pool中的版本数,一个SQL语句在library cache中一般是对应一个cursor head和一个cursor body, 其中cursor head包含sql的代码和优化器的模式,cursor body则包括这个SQL的cursor的详细定义, 比如excution plan, bind variable等等, 所以通常情况下当Server process在shared pool中定位某条SQL语句的时候，首先要对这条SQL进行hash运算，通过运算结果去定位cursor head的地址，然后在根据这个地址去定位其相应的cursor body。如果version_count这个直过高比如>1,则表示针对这同一条SQL，在共享池中有一个cursor head和多个cursor body。那么在定位一个SQL的时候由于存在过多的版本，需要扫描cursor header下面一链表私的一串儿的cursor body,那么这每一个body就对应了一个不同的版本。虽然SQl语句看似是一样的，但是由于body的不同，存在着不同的execution plan。如果这个version_count过高，那么随着cursor body数量的增加。在性能上也会有很大的影响，由于扫描cursor body的时候需要latch，对于shared pool来讲，latch是很宝贵的一种资源，因此这样的情况应该想办法来调整。来提高系统的性能。

产生version_count过多的原因举例如下：
1.Bind var的值的字段范围是50，突然传来一个值跳到了70，这个时候body会增加。
2.根据band var传来的值进行查询时,发现这个结果在table中占了大部分的行,走index已经不是特别的合适,因此优化器选择了走FTS,这个时候新的execution plan就生成了,cursor body也就会增加了。

select sql_id,version_count, sql_text
from  v$sqlarea
where version_count > 10
order by version_count, hash_value;

SQL> select sql_id,sql_text,version_count from v$sqlarea where version_count>5

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------
VERSION_COUNT
-------------
6sx3sq28xhyr7
SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT Employee2T_1.ITEMID,
ployee2T_1.COMPONENTID, Employee2T_1.VERSIONID, :"SYS_B_00" AS COMPONENTTYPEID
:"SYS_B_01" AS ITEMTYPEID FROM ICMUT01120001 Employee2T_1, ICMUT01127001 ICMPa
s1027_5 WHERE ((((Employee2T_1.COMPONENTID = ICMParts1027_5.
PARENTCOMPID) AND (Employee2T_1.VERSIONID = ICMParts1027_5.VERSIONID)) AND (co
ains(ICMParts1027_5.TIEREF, :"SYS_B_02", :"SYS_B_03") > :"SYS_B_04")) AND ((((
EXISTS (SELECT :"SYS_B_05" FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE :
YS_B_06"=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (:"SYS_B_07")
AND (C.ACL = V.ACLCODE OR C.ACL = -:"SYS_B_08") AND C.UNUM=:"SYS_B_09" AND C.R
IV=:"SYS_B_10"  ))))))) AND (((( (EXISTS (SELECT :"SYS_B_11" FROM ICMSTCOMPILE
CL C , ICMSTITEMTYPEREL V WHERE :"SYS_B_12"=V.SOURCEITEMTYPEID AND V.TARGETITE
YPEID = ICMParts1027_5.RTARGETITEMTYPEID AND (C.ACL = V.DFLT
ACLCODE OR C.ACL = -:"SYS_B_13") AND C.UNUM=:"SYS_B_14" AND C.RPRIV=:"SYS_B_15
 ))))))) T
            8

5r1gpmv8num8n
SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT BookChapter_1.ITEMID,
ookChapter_1.COMPONENTID, BookChapter_1.VERSIONID, :"SYS_B_00" AS COMPONENTTYP
D, :"SYS_B_01" AS ITEMTYPEID FROM ICMUT01015001 BookChapter_1 WHERE (contains(
okChapter_1.TIEREF, :"SYS_B_02", :"SYS_B_03") > :"SYS_B_04")
 AND (((( (EXISTS (SELECT :"SYS_B_05" FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEF
V WHERE :"SYS_B_06"=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (:"SYS_B_07") AND (C.
L = V.ACLCODE OR C.ACL = -:"SYS_B_08") AND C.UNUM=:"SYS_B_09" AND C.RPRIV=:"SY
B_10"  ))))))) T
           11

SQL> select SQL_ID,CHILD_NUMBER,
  2  UNBOUND_CURSOR,
  3  SQL_TYPE_MISMATCH,
  4  OPTIMIZER_MISMATCH,
  5  OUTLINE_MISMATCH,
  6  STATS_ROW_MISMATCH,
  7  LITERAL_MISMATCH,
  8  SEC_DEPTH_MISMATCH,
  9  EXPLAIN_PLAN_CURSOR,
 10  BUFFERED_DML_MISMATCH,
 11  PDML_ENV_MISMATCH,
 12  INST_DRTLD_MISMATCH,
 13  SLAVE_QC_MISMATCH,
 14  TYPECHECK_MISMATCH,
 15  AUTH_CHECK_MISMATCH,
 16  BIND_MISMATCH,
 17  DESCRIBE_MISMATCH,
 18  LANGUAGE_MISMATCH,
 19  TRANSLATION_MISMATCH,
 20  ROW_LEVEL_SEC_MISMATCH,
 21  INSUFF_PRIVS,
 22  INSUFF_PRIVS_REM,
 23  REMOTE_TRANS_MISMATCH,
 24  LOGMINER_SESSION_MISMATCH,
 25  INCOMP_LTRL_MISMATCH
 26  from v$sql_shared_cursor
 27  where sql_id='5r1gpmv8num8n';

SQL_ID        CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - -
5r1gpmv8num8n            0 N N N N N N N N N N N N N N N N N N N N N N N N
5r1gpmv8num8n            1 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            2 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            3 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            4 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            5 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            6 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            7 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            8 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n            9 N N N N N N N N N N N N N N Y N N N N N N N N N
5r1gpmv8num8n           10 N N N N N N N N N N N N N N Y N N N N N N N N N

11 rows selected.

SQL>
SQL> select SQL_ID,CHILD_NUMBER,
  2  UNBOUND_CURSOR,
  3  SQL_TYPE_MISMATCH,
  4  OPTIMIZER_MISMATCH,
  5  OUTLINE_MISMATCH,
  6  STATS_ROW_MISMATCH,
  7  LITERAL_MISMATCH,
  8  SEC_DEPTH_MISMATCH,
  9  EXPLAIN_PLAN_CURSOR,
 10  BUFFERED_DML_MISMATCH,
 11  PDML_ENV_MISMATCH,
 12  INST_DRTLD_MISMATCH,
 13  SLAVE_QC_MISMATCH,
 14  TYPECHECK_MISMATCH,
 15  AUTH_CHECK_MISMATCH,
 16  BIND_MISMATCH,
 17  DESCRIBE_MISMATCH,
 18  LANGUAGE_MISMATCH,
 19  TRANSLATION_MISMATCH,
 20  ROW_LEVEL_SEC_MISMATCH,
 21  INSUFF_PRIVS,
 22  INSUFF_PRIVS_REM,
 23  REMOTE_TRANS_MISMATCH,
 24  LOGMINER_SESSION_MISMATCH,
 25  INCOMP_LTRL_MISMATCH
 26  from v$sql_shared_cursor
 27  where sql_id='6sx3sq28xhyr7'
 28  ;

SQL_ID        CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - -
6sx3sq28xhyr7            0 N N N N N N N N N N N N N N N N N N N N N N N N
6sx3sq28xhyr7            1 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            2 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            3 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            4 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            5 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            6 N N N N N N N N N N N N N N Y N N N N N N N N N
6sx3sq28xhyr7            7 N N N N N N N N N N N N N N Y N N N N N N N N N

8 rows selected.

SQL>
相应的 Y 列说明了该cursor不能共享的原因：

BIND_MISMATCH - The bind metadata does not match the existing child cursor

其他问题可能是和BUG相关：

Bug 4458226 High version count with cursor_sharing=force

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected 10.1.0.4 ,10.2.0.1

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.8 (Server Patch Set) ,10.1.0.5 (Server Patch Set)
10.2.0.2 (Server Patch Set) ,11g (Future version)

Bug 4701527 Cursors not shared when executing procedures over a dblink

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected 9.2.0.5 ,9.2.0.7 ,10.1.0.5

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.8 (Server Patch Set) ,10.2.0.4 (Server Patch Set) ,11g (Future version)

Description
A cursor sharing problem exists for INSERT statements that insert into a remote table and are executed via a PLSQL module which itself is being executed remotely.

To be seeing this problem the following must be true:
1. Multiple child cursors are seen for an INSERT statement
2. V$SQL_SHARED_CURSOR will have the INST_DRTLD_MISMATCH column set to 'Y'
   for the cursor
3. The insert statement is executed remotely via a PLSQL module that is
   executed remotely

Bug 3461251 V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING=FORCE

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2
Versions confirmed as being affected 9.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 11.2 (Future Release)

Description
New child cursors may be created but V$SQL_SHARED_CURSOR does not have a 'Y' in any of the columns that denote the reason why the cursor could not be shared for the cursor in question. This is common when literal replacement is enabled and histograms exist on the columns that are predicates of the SQL statement and have undergone literal replacement.

This fix adds a new column to V$SQL_SHARED_CURSOR called HASH_MATCH_FAILED which is set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data.

Workaround:
Check if the unshared child cursors have histogram data on key columns.

Bug 5705795 Win/Linux: Many child cursors possible for SQL using BINDS

This bug is alerted in Note 403616.1
Affects:

    Product (Component)    Oracle Server (Rdbms)
    Range of versions believed to be affected    Versions >= 10.2.0.3 but < 10.2.0.4
    Versions confirmed as being affected   

        * 10.2.0.3

    Platforms affected   

        * Linux 32bit
        * Windows/NT/XP

     It is believed to be a regression in default behaviour thus:
       Regression introduced in 10.2.0.3

Fixed:

    This issue is fixed in   

        * 10.2.0.4 (Server Patch Set)

Symptoms:
   

Description

    This problem is introduced in 10.2.0.3 on Windows 32bit and Linux 32bit only by the fix for bug 4752541.

    10.2.0.3 on other platforms includes the fix for this bug# and so are not affected.

    SQL using bind variables with different bind sizes can lead to a large number of child cursors being
    created leading to excess shared pool usage and latch contention.