产生library cache latch原因

The library cache latches protect the cached SQL statements and objects' definitions held 
in the library cache within the shared pool. The library cache latch must be acquired 
in order to add a new statement to the library cache. During a parse, Oracle searches 
the library cache for a matching statement. If one is not found, then Oracle will parse 
the SQL statement, obtain the library cache latch and insert the new SQL.

每一个sql被执行之前，先要到library cache中根据hash_value查找parent cursor,这就需要先获得library cache latch；找到parent cursor后，就会去查找对应的child cursor,当发现无法找到时，就会释放library cache latch，获得share pool latch分配空间给硬解析后的产生的执行计划；然后再次获得library cache latch进行把执行计划放入share pool,转入library cache pin+lock(null模式)开始执行sql.library cache latch 的个数有限(与CPU_COUNT参数相关)，当数据库中出现大量硬解析的时候，某一个sql无法得到library cache latch就会开始spin,达到spin count后还没得到，就会开始sleep,达到sleep时间后,醒来还再次试图过的library cache latch得不到就在spin再得不到又sleep…依此类推.
综上可知:在sql执行的过程中可以看出在出现High Versions Count和Hard Parse的情况下都有可能出现library cache latch等待.
关于Hard Parse见:shared pool latch 等待事件
关于High Versions Count见:关于High Versions Count总结

1._KGL_LATCH_COUNT控制library cache latches数量

The hidden parameter _KGL_LATCH_COUNT controls the number of library cache latches. 
The default value should be adequate, but if contention for the library cache latch cannot  be resolved, 
it one may consider increasing this value. The default value for _KGL_LATCH_COUNT is the next prime number 
after CPU_COUNT. This value cannot exceed 67. 

2.Library cache: mutex X
在10g及其以后版本中,很多latch使用mutex代替,我们常见的Library cache: mutex X is similar to library cache wait in earlier version.(_kks_use_mutex_pin=false可以禁止mutex)

High Versions Count后果
sql查询–>hash对比确定是否存在shared pool中(不讨论不存在情况)–>选择合适的children
可能导致library cache latch contention

When you have unnecessary versions of a cursor, each time that cursor is executed, 
the parse engine has to search through the list of versions to see which is the cursor that you want. 
This wastes CPU cycles that you could be using on something else.

High version counts can easily cause high contention for library cache latches.
A process parsing a SQL statement with many versions (children cursors) will need to scan
through all these children while holding on to a library cache latch.
This means that other processes needing the same latch will have to wait and
can lead to significant database-wide performance degradation.

引起High Versions Count原因

•UNBOUND_CURSOR - The existing child cursor was not fully built (in other words, it was not optimized) 

•SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor 

•**OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor. 
For example:
select count(*) from emp; ->> 1 PARENT, 1 CHILD 
alter session set optimizer_mode=ALL_ROWS 
select count(*) from emp; ->> 1 PARENT, 2 CHILDREN 
    (The optimizer mode has changed and therefore 
       the existing child cannot be reused)
(The same applies with events - if I turned on tracing with 10046 than 
I would get the OPTIMIZER_MISMATCH again and a 3rd child) 

•OUTLINE_MISMATCH - The outlines do not match the existing child cursor 
If my user had created stored outlines previously for this command and they were 
stored in seperate categories (say "OUTLINES1" and "OUTLINES2") running:-

alter session set use_stored_outlines = OUTLINES1; 
select count(*) from emp; 
alter session set use_stored_oulines= OUTLINES2; 
select count(*) from emp;
--> Would create a 2nd child as the outline used is different than the first run. 

•STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor. 
Check that 10046/sql_trace is not set on all sessions as this can cause this. 

•LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor 

•SEC_DEPTH_MISMATCH - Security level does not match the existing child cursor 

•EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared. 
Explain plan statements will generate a new child by default - the mismatch will be this. 

•BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor 

•PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor 

•INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor 

•SLAVE_QC_MISMATCH -The existing child cursor is a slave cursor and the new one was issued by the coordinator 
(or, the existing child cursor was issued by the coordinator and the new one is a slave cursor). 

•TYPECHECK_MISMATCH - The existing child cursor is not fully optimized 

•AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor 

The user does not have permission to access the object in any previous version of the cursor. 
A typical example would be where each user has it's own copy of a table 

•**BIND_MISMATCH - The bind metadata does not match the existing child cursor. For example:
variable a varchar2(100); 
select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD 
variable a varchar2(400); 
select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN 
   (The bind 'a' has now changed in definition)

•DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor 

•LANGUAGE_MISMATCH - The language handle does not match the existing child cursor 

•TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match. 
The definition of the object does not match any current version. 
Usually this is indicative of the same issue as "AUTH_CHECK_MISMATCH" where the object is different.

•ROW_LEVEL_SEC_MISMATCH - The row level security policies do not match 

•INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor 

•INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor 

•REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match 
USER1: select count(*) from table@remote_db 
USER2: select count(*) from table@remote_db 

  (Although the SQL is identical, the dblink pointed to 
   by remote_db may be a private dblink which resolves 
   to a different object altogether)

•LOGMINER_SESSION_MISMATCH 

•INCOMP_LTRL_MISMATCH 

•OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch 

•SQL_REDIRECT_MISMATCH - sql redirection mismatch 

•MV_QUERY_GEN_MISMATCH - materialized view query generation 

•USER_BIND_PEEK_MISMATCH - user bind peek mismatch 

•TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies 

•NO_TRIGGER_MISMATCH - no trigger mismatch 

•FLASHBACK_CURSOR - No cursor sharing for flashback 

•ANYDATA_TRANSFORMATION - anydata transformation change 

•INCOMPLETE_CURSOR - incomplete cursor.
When bind length is upgradeable (i.e. we found a child cursor that matches everything
else except that the bind length is not long enough), we mark the old cursor is not usable
and build a new one.  This means the version can be ignored.

•TOP_LEVEL_RPI_CURSOR - top level/rpi cursor 
In a Parallel Query invocation this is expected behaviour (we purposely do not share)

•DIFFERENT_LONG_LENGTH - different long length 

•LOGICAL_STANDBY_APPLY - logical standby apply mismatch 

•DIFF_CALL_DURN - different call duration 

•BIND_UACS_DIFF - bind uacs mismatch 

•PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch 

•CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch 

•STB_OBJECT_MISMATCH - STB object different (now exists) 

•ROW_SHIP_MISMATCH - row shipping capability mismatch 

•PQ_SLAVE_MISMATCH - PQ slave mismatch 
Check you want to be using PX with this reason code, as the problem could be caused by running 
lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting Bug:4367986

•TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor 

•MULTI_PX_MISMATCH - multi-px and slave-compiled cursor 

•BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor 

•MV_REWRITE_MISMATCH - MV rewrite cursor 

•ROLL_INVALID_MISMATCH - rolling invalidation window exceeded 
This is caused by the rolling invalidation capability in DBMS_STATS. 
The child cannot be shared as it's invalidation window is exceeded. See:
Note:557661.1  Rolling Cursor Invalidations with DBMS_STATS in Oracle10g (Doc ID 557661.1)

•OPTIMIZER_MODE_MISMATCH - optimizer mode mismatch 

•PX_MISMATCH - parallel query mismatch 
If running 11.1.0.6 and RAC see Bug:7352775. 
Check that if (on each instance) parallel_instance_groups is set then instance_groups is set to the same.

•MV_STALEOBJ_MISMATCH - mv stale object mismatch 

•FLASHBACK_TABLE_MISMATCH - flashback table mismatch 

•LITREP_COMP_MISMATCH - literal replacement compilation mismatch 

New in 11g :
•PLSQL_DEBUG - debug mismatch Session has debugging parameter plsql_debug set to true

•LOAD_OPTIMIZER_STATS  - Load optimizer stats for cursor sharing

•ACL_MISMATCH   -  Check ACL mismatch

•FLASHBACK_ARCHIVE_MISMATCH  - Flashback archive mismatch

•LOCK_USER_SCHEMA_FAILED  - Failed to lock user and schema

•REMOTE_MAPPING_MISMATCH  - Remote mapping mismatch

•LOAD_RUNTIME_HEAP_FAILED  - Runtime heap mismatch

•HASH_MATCH_FAILED  - Hash mismatch
Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data 
or a range predicate marked as unsafe by literal replacement (See Bug:3461251)

New in 11.2  :
•PURGED_CURSOR - cursor marked for purging
The cursor has been marked for purging with dbms_shared_pool.purge

•BIND_LENGTH_UPGRADEABLE - bind length upgradeable
 Could not be shared because a bind variable size was smaller than the new value beiing inserted 
(marked as BIND_MISMATCH in earlier versions). 

•USE_FEEDBACK_STATS - cardinality feedback
Cardinality feedback is being used and therefore a new plan could be formed for the current execution.


•BIND_EQUIV_FAILURE - The bind value's selectivity does not match that used to optimize the existing child cursor

There is no longer  ROW_LEVEL_SEC_MISMATCH in 11.2.

High Versions Count查询
1.使用version_rpt function查询

--install version_rpt3_21
connect / as sysdba
start version_rpt3_21.sql

-- Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

-- Generate reports for all cursors with more than 100 versions using HASH_VALUE
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

-- Generate the report for cursor with sql_id cyzznbykb509s
select * from table(version_rpt('cyzznbykb509s'));

2.直接查询

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

•Version 9.2.X.X and below : 
select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
•Version 10.0.X.X and above:
select * from v$sql_shared_cursor where address = '0000000386BC2E58'

NOTE:The 'Y's denote a mismatch

High Versions Count跟踪

CURSORTRACE(10G及其以后版本)

TO trace on using:-  
alter system set events 
'immediate trace name cursortrace level 577, address hash_value';
(level 578/580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3) 

To turn off tracing use:- 
alter system set events 
'immediate trace name cursortrace level 2147483648, address 1';

Please note: BUG:5555371 exists in 10.2 (fixed in 10.2.0.4) where cursor trace cannot fully be turned off 
and single line entries will still be made to the trace file as a result. 
The w/a is to restart the instance. How invasive this BUG is depends on the executions of the cursor 
(and the size of the resultant trace file additions) 

cursordump(11GR2)
alter system set events 'immediate trace name cursordump level 16'

version_rpt3_21脚本:下载
参考:Troubleshooting: High Version Count Issues [ID 296377.1]