Oracle SQL with multiple versions in the library cache

from: http://www.dba-oracle.com/t_sql_multiple_children.htm Oracle Tips by Burleson Consulting

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 metalink note 120655.1":

select version_count,address,hash_value,parsing_schema_name,reason,sql_text from (
select
 address,''
 ||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(     LOGMINER_SESSION_MISMATCH),'Y',    ' LOGMINER_SESSION_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(        ANYDATA_TRANSFORMATION),'Y',       ' ANYDATA_TRANSFORMATION')
 ||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(       OPTIMIZER_MODE_MISMATCH),'Y',      ' OPTIMIZER_MODE_MISMATCH')
 ||decode(max(                   PX_MISMATCH),'Y',                  ' PX_MISMATCH')
 ||decode(max(          MV_STALEOBJ_MISMATCH),'Y',         ' MV_STALEOBJ_MISMATCH')
 ||decode(max(      FLASHBACK_TABLE_MISMATCH),'Y',     ' FLASHBACK_TABLE_MISMATCH')
 ||decode(max(          LITREP_COMP_MISMATCH),'Y',         ' LITREP_COMP_MISMATCH')
 reason
from
   v$sql_shared_cursor
group by
   address
) join v$sqlarea using(address) where version_count>&versions
order by version_count desc,address
;