原创 Oracle 作者:paulyibinyi 时间:2016-07-06 15:56:57 0 删除 编辑

What do the reasons given in v$SQL_SHARED_CURSOR mean
If you are unable to use that script then you can select the same information from the base views as illustrated in the examples below.

Lets use the example above and take a look at what SQL we can use to see this in the shared pool.

SCOTT runs select count(*) from emp

I can now run the following to see the PARENT statement and it's hash value and address

select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';

SQL_TEXT                 HASH_VALUE    ADDRESS
------------------------ ------------ ----------------
select count(*) from emp 4085390015   0000000386BC2E58

To see the CHILDREN (I expect to see 1 at this point) :-

  • 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'


ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F 
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
0000000386BC2D08 0000000386BC2E58 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 

We can see we have a single child (ADDRESS 0000000386BC2D08).
The mismatch information (U S O O S L etc) is all N because this is the first child. Now, if I log in as another user and run the same select (select count(*) from emp) and look again I will get the following output:-

ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F 
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
0000000386BC2D08 0000000386BC2E58 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 
0000000386A91AA0 0000000386BC2E58 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 

We can now see the 2nd child ( 0000000386A91AA0) and also the reasons why it could not be shared with the first (The 'Y's denote a mismatch). The reasons are:


This is because the objects under my new user do not map to those of SCOTT (the current child). A mismatch occurs because I cannot access SCOTTs objects and translation fails since we have different object_ids for the objects in each of our schemas. 

What do the reasons given in v$SQL_SHARED_CURSOR mean?

Below are the list of reasons as well as some worked examples (Those denoted by ** are the ones most often seen) :-


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

    The SQL type does not match the existing child cursor


    The optimizer environment does not match the existing child cursor (The optimizer mode has changed and therefore the existing child cannot be re-used).
    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 
    Note: The behavior applies with the setting of trace events. for example, if I turned on tracing with 10046 than I would get an OPTIMIZER_MISMATCH and another child cursor

    The outlines do not match the existing child cursor. For example, if a user had created stored outlines previously for this command and they were stored in separate categories (say "OUTLINES1" and "OUTLINES2"), if they then executed the following:
    alter session set use_stored_outlines = OUTLINES1;
    select count(*) from emp;

    alter session set use_stored_oulines= OUTLINES2;
    select count(*) from emp;

    The second execution of the select from emp would create another child since the outline used is different than the first run. This child would be marked as an OUTLINE_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.

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

    Security level does not match the existing child 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 does not match the existing child cursor

    PDML environment does not match the existing child cursor

    Insert direct load does not match the existing child cursor

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

    The existing child cursor is not fully optimized

    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

    The bind metadata does not match the existing child cursor. For example, in the following, the definition of the bind variable 'a' has changed between the 2 statements:

    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 type-check heap is not present during the describe for the child cursor

    The language handle does not match the existing child cursor

    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.

    The row level security policies do not match

    Insufficient privileges on objects referenced by the existing child cursor

    Insufficient privileges on remote objects referenced by the existing child cursor

    The remote base objects of the existing child cursor do not match. For example:
    select count(*) from table@remote_db  
    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



    Error_on_overlap_time mismatch

    SQL redirection mismatch

    Materialized view query generation

    User bind peeking mismatch

    Cursor has type-check dependencies

    No trigger mismatch

    No cursor sharing for flashback

    Anydata transformation change

    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. In a Parallel Query invocation this is expected behaviour (we purposely do not share)

    Different long length

    Logical standby apply mismatch

    Different call duration

    Bind uacs mismatch

    PL/SQL compiler switches mismatch

    Cursor "parts executed" mismatch

    STB object different (now exists). For explanation of STB_OBJECT_MISMATCH, please read following blog:

    Row shipping capability mismatch

    PQ slave mismatch If you encounter this reason code and you are using parallel execution (PX), then check you really want to be using it. This mismatch can be caused by running lots of small SQL statements which do not really need PX. Also, if you are on versions prior to 11g you may be hitting Bug:4367986

    Top-level DDL cursor

    Multi-px and slave-compiled cursor

    Bind-peeked PQ cursor

    MV rewrite cursor

    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:

    Document 557661.1  Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

    Optimizer mode mismatch

    Parallel query execution mismatch. Refer to the following for known issues where this reason is shown:
    Document 1629107.1 Common Bugs Associated with PX_MISMATCH

    Materialixed View stale object mismatch

    Flashback table mismatch

    Literal replacement compilation mismatch 

New in 11g :


    Debug mismatch Session has debugging parameter plsql_debug set to true

    Load optimizer stats for cursor sharing

    Check ACL mismatch

    Flashback archive mismatch

    Failed to lock user and schema

    Remote mapping mismatch

    Runtime heap mismatch

    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:


    Cursor marked for purging. The cursor has been marked for purging with dbms_shared_pool.purge

    Bind length upgradeable and could not be shared because a bind variable size was smaller than the new value being inserted (marked as BIND_MISMATCH in earlier versions).

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

    The bind value's selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan. For an example, see Document 836256.1. After each execution in the example, run:
    select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';
    ... once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:
    ------------- ---------------- ---------------- ------------ -
    19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0 N
    19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1 Y

    As can be seen, the new version is created due to BIND_EQUIV_FAILURE


There is no longer  ROW_LEVEL_SEC_MISMATCH in 11.2.

Version_rpt script:

The script version_rpt can also be run to produce a summary report of the v$sql_shared_cursor view with additional diagnostic information. The script can be found in:

Document 438755.1 High SQL Version Counts - Script to determine reason(s) 

Running the Script:
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'));

What further tracing is available.

In 10G it is possible to use CURSORTRACE to aid the investigation of why cursors are not being shared. This event should only be used under the guidance of support and the resultant trace file is undocumented. To get the trace for a particular SQL statement you first of all need to get the hash_value (See the above select from v$sqlarea). You then set the trace on using:-  

alter system set events 'immediate trace name cursortrace level 577, address hash_value';

(levels 578-580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)


This will write a trace file to user_dump_dest each time we try to reuse the cursor.
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 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)

In 11.2 there is also cursordump:

alter system set events 'immediate trace name cursordump level 16'

 (please ensure system , not session, is used as the level meaning changes)

This dumps some additional information such as expanding on the parameters for 'optimizer_mismatch' issues.

In later versions of the RDBMS there are also enhancements which dump more information as to the actual reason a child cursor could not share (ie the parameter differences). This information can be found in the REASON column of v$sql_shared_cursor and is in XML format. See Bug 16770590 for example.

Are there any times when a high version count is expected even though BINDS are being used?

Consider the following where cursor_sharing=SIMILAR

select /* TEST */ * from emp where sal > 100;
select /* TEST */ * from emp where sal > 101;
select /* TEST */ * from emp where sal > 102;
select /* TEST */ * from emp where sal > 103;
select /* TEST */ * from emp where sal > 104;

SELECT sql_text,version_count,address
WHERE sql_text like 'select /* TEST */%';

SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = '&my_addr';

You will see several versions , each with no obvious reason for not being shared

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data


  • 博文量
  • 访问量