V$SQL_SHARED_CURSOR

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors.

Each column identifies a specific reason why the cursor cannot be shared.

You can find additional diagnostic information about the cursors displayed in this view by querying the V$SQL_SHARED_CURSOR_DIAG view. Join this view with V$SQL_SHARED_CURSOR_DIAG on the SQL_ID and CHILD_NUMBER columns.

Column	Datatype	Description
`SQL_ID`	`VARCHAR2(13)`	SQL identifier
`ADDRESS`	`RAW(4 \| 8)`	Address of the parent cursor
`CHILD_ADDRESS`	`RAW(4 \| 8)`	Address of the child cursor
`CHILD_NUMBER`	`NUMBER`	Child number
`UNBOUND_CURSOR`	`VARCHAR2(1)`	(`Y\|N`) The existing child cursor was not fully built (in other words, it was not optimized)
`SQL_TYPE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The SQL type does not match the existing child cursor
`OPTIMIZER_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The optimizer environment does not match the existing child cursor
`OUTLINE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The outlines do not match the existing child cursor
`STATS_ROW_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The existing statistics do not match the existing child cursor
`LITERAL_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Non-data literal values do not match the existing child cursor
`FORCE_HARD_PARSE`	`VARCHAR2(1)`	(`Y\|N`) For internal use
`EXPLAIN_PLAN_CURSOR`	`VARCHAR2(1)`	(`Y\|N`) The child cursor is an explain plan cursor and should not be shared
`BUFFERED_DML_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Buffered DML does not match the existing child cursor
`PDML_ENV_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) PDML environment does not match the existing child cursor
`INST_DRTLD_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Insert direct load does not match the existing child cursor
`SLAVE_QC_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The existing child cursor is a worker 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 worker cursor)
`TYPECHECK_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The existing child cursor is not fully optimized
`AUTH_CHECK_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Authorization/translation check failed for the existing child cursor
`BIND_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The bind metadata does not match the existing child cursor
`DESCRIBE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The typecheck heap is not present during the describe for the child cursor
`LANGUAGE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The language handle does not match the existing child cursor
`TRANSLATION_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The base objects of the existing child cursor do not match
`BIND_EQUIV_FAILURE`	`VARCHAR2(1)`	(`Y\|N`) The bind value’s selectivity does not match that used to optimize the existing child cursor
`INSUFF_PRIVS`	`VARCHAR2(1)`	(`Y\|N`) Insufficient privileges on objects referenced by the existing child cursor
`INSUFF_PRIVS_REM`	`VARCHAR2(1)`	(`Y\|N`) Insufficient privileges on remote objects referenced by the existing child cursor
`REMOTE_TRANS_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The remote base objects of the existing child cursor do not match
`LOGMINER_SESSION_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) LogMiner Session parameters mismatch
`INCOMP_LTRL_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
`OVERLAP_TIME_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Mismatch caused by setting session parameter `ERROR_ON_OVERLAP_TIME`
`EDITION_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor edition mismatch
`MV_QUERY_GEN_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Internal, used to force a hard-parse when analyzing materialized view queries
`USER_BIND_PEEK_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan
`TYPCHK_DEP_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor has typecheck dependencies
`NO_TRIGGER_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor and child have no trigger mismatch
`FLASHBACK_CURSOR`	`VARCHAR2(1)`	(`Y\|N`) Cursor non-shareability due to flashback
`ANYDATA_TRANSFORMATION`	`VARCHAR2(1)`	(`Y\|N`) Is criteria for opaque type transformation and does not match
`PDDL_ENV_MISMATCH`	`VARCHAR2(1)`	(`Y`\|`N`) Environment setting mismatch for parallel DDL cursor (that is, one or more of the following parameter values have changed: `PARALLEL_EXECUTION_ENABLED`, `PARALLEL_DDL_MODE`, `PARALLEL_DDL_FORCED_DEGREE`, or `PARALLEL_DDL_FORCED_INSTANCES`)
`TOP_LEVEL_RPI_CURSOR`	`VARCHAR2(1)`	(`Y\|N`) Is top level RPI cursor
`DIFFERENT_LONG_LENGTH`	`VARCHAR2(1)`	(`Y\|N`) Value of `LONG` does not match
`LOGICAL_STANDBY_APPLY`	`VARCHAR2(1)`	(`Y\|N`) Logical standby apply context does not match
`DIFF_CALL_DURN`	`VARCHAR2(1)`	(`Y\|N`) If worker SQL cursor/single call
`BIND_UACS_DIFF`	`VARCHAR2(1)`	(`Y\|N`) One cursor has bind UACs and one does not
`PLSQL_CMP_SWITCHS_DIFF`	`VARCHAR2(1)`	(`Y\|N`) PL/SQL anonymous block compiled with different PL/SQL compiler switches
`CURSOR_PARTS_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor was compiled with subexecution (cursor parts were executed)
`STB_OBJECT_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) STB is an internal name for a SQL Management Object Mismatch. A SQL Management Object Mismatch means that either a SQL plan baseline, or a SQL profile, or a SQL patch has been created for your SQL statement between the executions. Because a cursor is a read-only entity, a hard parse is forced to be able to create a new cursor that contains information about the new SQL management object related to this SQL statement.
`CROSSEDITION_TRIGGER_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) The set of crossedition triggers to execute might differ
`PQ_SLAVE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Top-level worker decides not to share cursor
`TOP_LEVEL_DDL_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Is top-level DDL cursor
`MULTI_PX_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor has multiple parallelizers and is worker-compiled
`BIND_PEEKED_PQ_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor based around bind peeked values
`MV_REWRITE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
`ROLL_INVALID_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Marked for rolling invalidation and invalidation window exceeded
`OPTIMIZER_MODE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Parameter `OPTIMIZER_MODE` mismatch (for example, all_rows versus first_rows_1)
`PX_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.
`MV_STALEOBJ_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale when the cursor was built
`FLASHBACK_TABLE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
`LITREP_COMP_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Mismatch in use of literal replacement
`PLSQL_DEBUG`	`VARCHAR2(1)`	(`Y\|N`) Value of the `PLSQL_DEBUG` parameter for the current session does not match the value used to build the cursor
`LOAD_OPTIMIZER_STATS`	`VARCHAR2(1)`	(`Y\|N`) A hard parse is forced to initialize extended cursor sharing
`ACL_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Cached ACL evaluation result stored in the child cursor is not valid for the current session or user
`FLASHBACK_ARCHIVE_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Value of the `FLASHBACK_DATA_ARCHIVE_INTERNAL_CURSOR` parameter for the current session does not match the value used to build the cursor
`LOCK_USER_SCHEMA_FAILED`	`VARCHAR2(1)`	(`Y\|N`) User or schema used to build the cursor no longer exists Note: This sharing criterion is deprecated
`REMOTE_MAPPING_MISMATCH`	`VARCHAR2(1)`	(`Y\|N`) Reloaded cursor was previously remote-mapped and is currently not remote-mapped. Therefore, the cursor must be reparsed.
`LOAD_RUNTIME_HEAP_FAILED`	`VARCHAR2(1)`	(`Y\|N`) Loading of run-time heap for the new cursor (or reload of aged out cursor) failed
`HASH_MATCH_FAILED`	`VARCHAR2(1)`	(`Y\|N`) No existing child cursors have the unsafe literal bind hash values required by the current cursor
`PURGED_CURSOR`	`VARCHAR2(1)`	(`Y\|N`) Child cursor is marked for purging
`BIND_LENGTH_UPGRADEABLE`	`VARCHAR2(1)`	(`Y\|N`) Bind length(s) required for the current cursor are longer than the bind length(s) used to build the child cursor
`USE_FEEDBACK_STATS`	`VARCHAR2(1)`	(`Y\|N`) A hard parse is forced so that the optimizer can reoptimize the query with improved optimizer inputs (for example, cardinality estimates)
`REASON`	`CLOB`	Child number, id, and reason the cursor is not shared. The content of this column is structured using XML. If this column is null, the `FORCE_HARD_PARSE`, `LOAD_RUNTIME_HEAP_FAILED`, and `HASH_MATCH_FAILED` columns may enable you to determine why the child cursor is not shared.
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

See Also: “V$SQL_SHARED_CURSOR_DIAG”