关于High Versions Count总结

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:关于High Versions Count总结

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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]

此条目发表在 Oracle性能优化 分类目录,贴了 标签。将固定链接加入收藏夹。

关于High Versions Count总结》有 4 条评论

  1. 惜分飞 说:

    In 11.1.0.7 set “_cursor_features_enabled” to value 18 ( 2 is the default value )
    In 11.2.0.1 set “_cursor_features_enabled” to value 34 ( 2 is the default value )
    In 11.2.0.2 set “_cursor_features_enabled” to value 1026 ( 2 is the default value )
    Additionnally set event 106001 to a level greater than 0. The level will be the number
    of child cursors that a parent cursor can have before the parent cursor is
    obsoleted.
    In 11.2.0.3 and 11.2.0.4 set “_cursor_obsolete_threshold” to 100 (this is the number of child cursor after which we obsolete it)

  2. 玉面飞龙 说:

    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

    当时我们就因为这个event重新启动了数据库

  3. anbob 说:

    所谓的high versions count我发现不是v$sqlare 的version_count ,而是loaded_versions,

  4. 惜分飞 说:

    查看High Versions Count原因

    SQL> show user;
    USER is "CHF"
    
    SQL> create table t_xifenfei as select * from dba_tables where rownum<10;
    
    Table created.
    
    SQL> select count(*) from t_xifenfei;
    
      COUNT(*)
    ----------
             9
    
    SQL> show user;
    USER is "SYSTEM"
    
    SQL> create table t_xifenfei as select * from dba_tables where rownum<10;
    
    Table created.
    
    SQL> select count(*) from t_xifenfei;
    
      COUNT(*)
    ----------
             9
    
    SQL> select hash_value,sql_id,ADDRESS,version_count from 
      2  v$sqlarea where sql_text ='select count(*) from t_xifenfei'; 
    
    HASH_VALUE SQL_ID                     ADDRESS  VERSION_COUNT
    ---------- -------------------------- -------- -------------
    3344474632 gj4x1fz3pj8h8              680C351C             2
    
    --直接查看
    SQL> select * from v$sql_shared_cursor where address ='680C351C';
    
    SQL_ID                     ADDRESS  CHILD_AD CHILD_NUMBER UN SQ OP OU ST LI FO
    -------------------------- -------- -------- ------------ -- -- -- -- -- -- --
    EX BU PD IN SL TY AU BI DE LA TR BI IN IN RE LO IN OV ED MV US TY NO FL AN PD TO
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    DI LO DI BI PL CU ST CR PQ TO MU BI MV RO OP PX MV FL LI PL LO AC FL LO RE LO HA
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    PU BI US
    -- -- --
    REASON
    --------------------------------------------------------------------------------
    gj4x1fz3pj8h8              680C351C 680C3254            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  N  N  N  N  N  N  N  N  N  N
    N  N  N  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                     ADDRESS  CHILD_AD CHILD_NUMBER UN SQ OP OU ST LI FO
    -------------------------- -------- -------- ------------ -- -- -- -- -- -- --
    EX BU PD IN SL TY AU BI DE LA TR BI IN IN RE LO IN OV ED MV US TY NO FL AN PD TO
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    DI LO DI BI PL CU ST CR PQ TO MU BI MV RO OP PX MV FL LI PL LO AC FL LO RE LO HA
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    PU BI US
    -- -- --
    REASON
    --------------------------------------------------------------------------------
    N  N  N
    <ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check fa
    
    
    SQL_ID                     ADDRESS  CHILD_AD CHILD_NUMBER UN SQ OP OU ST LI FO
    -------------------------- -------- -------- ------------ -- -- -- -- -- -- --
    EX BU PD IN SL TY AU BI DE LA TR BI IN IN RE LO IN OV ED MV US TY NO FL AN PD TO
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    DI LO DI BI PL CU ST CR PQ TO MU BI MV RO OP PX MV FL LI PL LO AC FL LO RE LO HA
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    PU BI US
    -- -- --
    REASON
    --------------------------------------------------------------------------------
    gj4x1fz3pj8h8              680C351C 680725E8            1 N  N  N  N  N  N  N
    N  N  N  N  N  N  Y  N  N  N  Y  N  N  N  N  N  N  N  N  N  N  N  N  N  N  N  N
    N  N  N  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                     ADDRESS  CHILD_AD CHILD_NUMBER UN SQ OP OU ST LI FO
    -------------------------- -------- -------- ------------ -- -- -- -- -- -- --
    EX BU PD IN SL TY AU BI DE LA TR BI IN IN RE LO IN OV ED MV US TY NO FL AN PD TO
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    DI LO DI BI PL CU ST CR PQ TO MU BI MV RO OP PX MV FL LI PL LO AC FL LO RE LO HA
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    PU BI US
    -- -- --
    REASON
    --------------------------------------------------------------------------------
    N  N  N
    
    
    
    --version_rpt函数查看
    SQL> conn / as sysdba
    Connected.
    SQL> select * from table(version_rpt('gj4x1fz3pj8h8'));
    
    COLUMN_VALUE
    -------------------------------------------------------------------------
    Version Count Report Version 3.2.1 -- Today's Date 02-may-12 02:24
    RDBMS Version :11.2.0.3.0 Host: xifenfei Instance 1 : ora11g
    ==================================================================
    Addr: 680C351C  Hash_Value: 3344474632  SQL_ID gj4x1fz3pj8h8
    Sharable_Mem: 19192 bytes   Parses: 2
    Stmt:
    0 select count(*) from t_xifenfei
    1
    
    Versions Summary
    ----------------
    
    COLUMN_VALUE
    ----------------------------------------------------------------------------
    AUTH_CHECK_MISMATCH :1
    TRANSLATION_MISMATCH :1
    
    Total Versions:1
    
    Plan Hash Value Summary
    -----------------------
    Plan Hash Value Count
    =============== =====
         2715729601 2
    
    
    COLUMN_VALUE
    -----------------------------------------------------------------------
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Details for AUTH_CHECK_MISMATCH :
    
      # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
    ========== =============== ================= ===================
             1               84                84 CHF
             1                5                 5 SYSTEM
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Details for TRANSLATION_MISMATCH :
    
    Summary of objects probably causing TRANSLATION_MISMATCH
    
    COLUMN_VALUE
    ----------------------------------------------------------------------------
    
      Object# Owner.Object_Name
    ========= =================
        75915 CHF.T_XIFENFEI
        75916 SYSTEM.T_XIFENFEI
    ####
    To further debug Ask Oracle Support for the appropiate level LLL.
    alter session set events
     'immediate trace name cursortrace address 3344474632, level LLL';
    To turn it off do use address 1, level 2147483648
    ================================================================
    
    44 rows selected.