sql不能共享的原因About version_count

library cache 相关的latch 等待并都是因为sql语句没有绑定变量造成的！！！

也有很多种情况会造成相同的sql_id,相同的hash_value而不能share。
这通常都会造成很多的child cursor.

       
查询v$sqlarea中版本数大于10的所有sql的语句：
select sql_id,version_count, sql_text
from   v$sqlarea 
where version_count > 10 
order by version_count, hash_value;

通常有问题的时候某些sql_id 的 version_count 可能会大于1000，
如果对比的话，sql_text都是完全相同的，那么到底是什么造成如此多的version呢？

例如我们上面的查询找到了sql_id:10xj8nynmpqtq

则执行下面的查询：

select SQL_ID,CHILD_NUMBER,
UNBOUND_CURSOR,
SQL_TYPE_MISMATCH,
OPTIMIZER_MISMATCH,
OUTLINE_MISMATCH,
STATS_ROW_MISMATCH,
LITERAL_MISMATCH,
SEC_DEPTH_MISMATCH,
EXPLAIN_PLAN_CURSOR,
BUFFERED_DML_MISMATCH,
PDML_ENV_MISMATCH,
INST_DRTLD_MISMATCH,
SLAVE_QC_MISMATCH,
TYPECHECK_MISMATCH,
AUTH_CHECK_MISMATCH,
BIND_MISMATCH,
DESCRIBE_MISMATCH,
LANGUAGE_MISMATCH,
TRANSLATION_MISMATCH,
ROW_LEVEL_SEC_MISMATCH,
INSUFF_PRIVS,
INSUFF_PRIVS_REM,
REMOTE_TRANS_MISMATCH,
LOGMINER_SESSION_MISMATCH,
INCOMP_LTRL_MISMATCH 
from v$sql_shared_cursor 
where sql_id='10xj8nynmpqtq';

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
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - -
10xj8nynmpqtq            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
10xj8nynmpqtq            1 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            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 
10xj8nynmpqtq            3 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            4 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            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
10xj8nynmpqtq            6 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            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
10xj8nynmpqtq            8 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            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
10xj8nynmpqtq           10 N N N N N N N N N N N N N N N N N N N N N N N N

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
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - -
10xj8nynmpqtq           11 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq           12 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq           13 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq           14 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq           15 N N N N N N N N N N N N N N N N N N N N N N N N

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

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

除了这些能够查询到的原因之外，还有可能所有的列都是 N ,但是仍然还是有很多version_count .

那么恭喜你中奖了，这种情况可能的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) 

sql有绑定变量但是却不能共享的原因About version_count

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.