Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle SQL with multiple versions in the library cache

Oracle Database Tips by Donald Burleson

When a SQL statement is executed with multiple child cursors, multiple versions are created within the library cache of the shared pool.  You can query the v$sqlarea view for details on child cursors, using the sharable_mem and persistent_mem columns.

You can also query the v$sql view columns, greatly enhanced in 10g:

  • SHARABLE_MEM - Amount of shared memory used by the child cursor (in bytes)
  • PERSISTENT_MEM - Fixed amount of memory used for the lifetime of the child cursor (in bytes)
  • INVALIDATIONS - Number of times this child cursor has been invalidated
  • PARSE_CALLS - Number of parse calls for this child cursor
  • DISK_READS - Number of disk reads for this child cursor
  • DIRECT_WRITES - Number of direct writes for this child cursor
  • BUFFER_GETS - Number of buffer gets for this child cursor
  • PARSING_USER_ID - User ID of the user who originally built this child cursor
  • PARSING_SCHEMA_ID - Schema ID that was used to originally build this child cursor
  • PARSING_SCHEMA_NAME - Schema name that was used to originally build this child cursor
  • KEPT_VERSIONS - Indicates whether this child cursor has been marked to be kept pinned in the cache using the DBMS_SHARED_POOL package
  • ADDRESS - Address of the handle to the parent for this cursor
  • TYPE_CHK_HEAP - Descriptor of the type check heap for this child cursor
  • CHILD_ADDRESS - Address of the child cursor

For working scripts against these views, see the Oracle script collection.

The STATSPACK report scripts sprepsql.sql and sprsqins.sql scripts are used to generate the STATSPACK report for SQL statements, statistics and plan usage. These STATSPACK SQL report (in $ORACLE_HOME/admin/rdbms/admin) shows the shareable memory used by a SQL cursor, and it's the sum of all memory used by the SQL, including child cursors.

Steve Adams notes the library cache structures for SQL with multiple children and the multiple cursor structure within the shared pool library cache:

"For each SQL statement the library cache contains a "parent" cursor for the text of the SQL statement.

The parent cursor is comprised of a "handle" that can be looked up by hash value via the library cache hash table, and an "object" that contains pointers to each of its "child" cursors.

Each child cursor is also comprised of a handle and an object. The child object is comprised of two heaps numbered 0 and 6. Heap 0 contains all the identifying information for a particular version of the SQL statement and heap 6 contains the execution plan. This distinction between parent and child cursors is maintained even when there is only one version of each SQL statement.

For parent cursors the convention used in the X$ tables is that the parent address is the same as the handle address. V$OPEN_CURSORS, V$SQL and so on are only interested in child cursors and so they exclude parent cursors by requiring that the two addresses are different. The columns that you mentioned are of course the two addresses."

In  statspack you can use the collection threshold p_def_version_count_th to govern the  threshold for the SQL child cursors.  I have  more details on SQL tuning in my book "Oracle Tuning: The Definitive Reference".

Frank Pachot published this nice SQL, intended to be run right after an SQL is executed, using v$session.prev_sql_addr:

"When you have the same statement that has several versions (child) the view v$sql_shared_cursor shows the reason why the statement cannot be shared.  More detail on reasons in MOSC note 120655.1":

select version_count,address,hash_value,parsing_schema_name,reason,sql_text from (
 ||decode(max(                UNBOUND_CURSOR),'Y',               ' UNBOUND_CURSOR')
 ||decode(max(             SQL_TYPE_MISMATCH),'Y',            ' SQL_TYPE_MISMATCH')
 ||decode(max(            OPTIMIZER_MISMATCH),'Y',           ' OPTIMIZER_MISMATCH')
 ||decode(max(              OUTLINE_MISMATCH),'Y',             ' OUTLINE_MISMATCH')
 ||decode(max(            STATS_ROW_MISMATCH),'Y',           ' STATS_ROW_MISMATCH')
 ||decode(max(              LITERAL_MISMATCH),'Y',             ' LITERAL_MISMATCH')
 ||decode(max(            SEC_DEPTH_MISMATCH),'Y',           ' SEC_DEPTH_MISMATCH')
 ||decode(max(           EXPLAIN_PLAN_CURSOR),'Y',          ' EXPLAIN_PLAN_CURSOR')
 ||decode(max(         BUFFERED_DML_MISMATCH),'Y',        ' BUFFERED_DML_MISMATCH')
 ||decode(max(             PDML_ENV_MISMATCH),'Y',            ' PDML_ENV_MISMATCH')
 ||decode(max(           INST_DRTLD_MISMATCH),'Y',          ' INST_DRTLD_MISMATCH')
 ||decode(max(             SLAVE_QC_MISMATCH),'Y',            ' SLAVE_QC_MISMATCH')
 ||decode(max(            TYPECHECK_MISMATCH),'Y',           ' TYPECHECK_MISMATCH')
 ||decode(max(           AUTH_CHECK_MISMATCH),'Y',          ' AUTH_CHECK_MISMATCH')
 ||decode(max(                 BIND_MISMATCH),'Y',                ' BIND_MISMATCH')
 ||decode(max(             DESCRIBE_MISMATCH),'Y',            ' DESCRIBE_MISMATCH')
 ||decode(max(             LANGUAGE_MISMATCH),'Y',            ' LANGUAGE_MISMATCH')
 ||decode(max(          TRANSLATION_MISMATCH),'Y',         ' TRANSLATION_MISMATCH')
 ||decode(max(        ROW_LEVEL_SEC_MISMATCH),'Y',       ' ROW_LEVEL_SEC_MISMATCH')
 ||decode(max(                  INSUFF_PRIVS),'Y',                 ' INSUFF_PRIVS')
 ||decode(max(              INSUFF_PRIVS_REM),'Y',             ' INSUFF_PRIVS_REM')
 ||decode(max(         REMOTE_TRANS_MISMATCH),'Y',        ' REMOTE_TRANS_MISMATCH')
 ||decode(max(          INCOMP_LTRL_MISMATCH),'Y',         ' INCOMP_LTRL_MISMATCH')
 ||decode(max(         OVERLAP_TIME_MISMATCH),'Y',        ' OVERLAP_TIME_MISMATCH')
 ||decode(max(         SQL_REDIRECT_MISMATCH),'Y',        ' SQL_REDIRECT_MISMATCH')
 ||decode(max(         MV_QUERY_GEN_MISMATCH),'Y',        ' MV_QUERY_GEN_MISMATCH')
 ||decode(max(       USER_BIND_PEEK_MISMATCH),'Y',      ' USER_BIND_PEEK_MISMATCH')
 ||decode(max(           TYPCHK_DEP_MISMATCH),'Y',          ' TYPCHK_DEP_MISMATCH')
 ||decode(max(           NO_TRIGGER_MISMATCH),'Y',          ' NO_TRIGGER_MISMATCH')
 ||decode(max(              FLASHBACK_CURSOR),'Y',             ' FLASHBACK_CURSOR')
 ||decode(max(             INCOMPLETE_CURSOR),'Y',            ' INCOMPLETE_CURSOR')
 ||decode(max(          TOP_LEVEL_RPI_CURSOR),'Y',         ' TOP_LEVEL_RPI_CURSOR')
 ||decode(max(         DIFFERENT_LONG_LENGTH),'Y',        ' DIFFERENT_LONG_LENGTH')
 ||decode(max(         LOGICAL_STANDBY_APPLY),'Y',        ' LOGICAL_STANDBY_APPLY')
 ||decode(max(                DIFF_CALL_DURN),'Y',               ' DIFF_CALL_DURN')
 ||decode(max(                BIND_UACS_DIFF),'Y',               ' BIND_UACS_DIFF')
 ||decode(max(        PLSQL_CMP_SWITCHS_DIFF),'Y',       ' PLSQL_CMP_SWITCHS_DIFF')
 ||decode(max(         CURSOR_PARTS_MISMATCH),'Y',        ' CURSOR_PARTS_MISMATCH')
 ||decode(max(           STB_OBJECT_MISMATCH),'Y',          ' STB_OBJECT_MISMATCH')
 ||decode(max(             ROW_SHIP_MISMATCH),'Y',            ' ROW_SHIP_MISMATCH')
 ||decode(max(             PQ_SLAVE_MISMATCH),'Y',            ' PQ_SLAVE_MISMATCH')
 ||decode(max(        TOP_LEVEL_DDL_MISMATCH),'Y',       ' TOP_LEVEL_DDL_MISMATCH')
 ||decode(max(             MULTI_PX_MISMATCH),'Y',            ' MULTI_PX_MISMATCH')
 ||decode(max(       BIND_PEEKED_PQ_MISMATCH),'Y',      ' BIND_PEEKED_PQ_MISMATCH')
 ||decode(max(           MV_REWRITE_MISMATCH),'Y',          ' MV_REWRITE_MISMATCH')
 ||decode(max(         ROLL_INVALID_MISMATCH),'Y',        ' ROLL_INVALID_MISMATCH')
 ||decode(max(                   PX_MISMATCH),'Y',                  ' PX_MISMATCH')
 ||decode(max(          MV_STALEOBJ_MISMATCH),'Y',         ' MV_STALEOBJ_MISMATCH')
 ||decode(max(          LITREP_COMP_MISMATCH),'Y',         ' LITREP_COMP_MISMATCH')
group by
) join v$sqlarea using(address) where version_count>&versions
order by version_count desc,address

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.