oracle+query+high,关于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]