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)
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.