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.
| 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) |
??? |
SEC_DEPTH_MISMATCH |
VARCHAR2(1) |
(Y|N) Security level does not match the existing child cursor |
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 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 |
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 |
ROW_LEVEL_SEC_MISMATCH |
VARCHAR2(1) |
(Y|N) The row level security policies do not match |
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) |
Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME |
EDITIONS_MISMATCH |
VARCHAR2(1) |
??? |
SQL_REDIRECT_MISMATCH |
VARCHAR2(1) |
SQL redirection mismatch |
MV_QUERY_GEN_MISMATCH |
VARCHAR2(1) |
Internal, used to force a hard-parse when analyzing materialized view queries |
USER_BIND_PEEK_MISMATCH |
VARCHAR2(1) |
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) |
Cursor has typecheck dependencies |
NO_TRIGGER_MISMATCH |
VARCHAR2(1) |
Cursor and child have no trigger mismatch |
FLASHBACK_CURSOR |
VARCHAR2(1) |
Cursor non-shareability due to flashback |
ANYDATA_TRANSFORMATION |
VARCHAR2(1) |
Is criteria for opaque type transformation and does not match |
INCOMPLETE_CURSOR |
VARCHAR2(1) |
Cursor is incomplete: typecheck heap came from call memory |
TOP_LEVEL_RPI_CURSOR |
VARCHAR2(1) |
Is top level RPI cursor |
DIFFERENT_LONG_LENGTH |
VARCHAR2(1) |
Value of LONG does not match |
LOGICAL_STANDBY_APPLY |
VARCHAR2(1) |
Logical standby apply context does not match |
DIFF_CALL_DURN |
VARCHAR2(1) |
If Slave SQL cursor/single call |
BIND_UACS_DIFF |
VARCHAR2(1) |
One cursor has bind UACs and one does not |
PLSQL_CMP_SWITCHS_DIFF |
VARCHAR2(1) |
PL/SQL anonymous block compiled with different PL/SQL compiler switches |
CURSOR_PARTS_MISMATCH |
VARCHAR2(1) |
Cursor was compiled with subexecution (cursor parts were executed) |
STB_OBJECT_MISMATCH |
VARCHAR2(1) |
STB has come into existence since cursor was compiled |
EDITION_FOR_TRIGGERS_MISMATCH |
VARCHAR2(1) | ??? |
PQ_SLAVE_MISMATCH |
VARCHAR2(1) |
Top-level slave decides not to share cursor |
TOP_LEVEL_DDL_MISMATCH |
VARCHAR2(1) |
Is top-level DDL cursor |
MULTI_PX_MISMATCH |
VARCHAR2(1) |
Cursor has multiple parallelizers and is slave-compiled |
BIND_PEEKED_PQ_MISMATCH |
VARCHAR2(1) |
Cursor based around bind peeked values |
MV_REWRITE_MISMATCH |
VARCHAR2(1) |
Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view |
ROLL_INVALID_MISMATCH |
VARCHAR2(1) |
Marked for rolling invalidation and invalidation window exceeded |
OPTIMIZER_MODE_MISMATCH |
VARCHAR2(1) |
Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1) |
PX_MISMATCH |
VARCHAR2(1) |
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) |
Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built |
FLASHBACK_TABLE_MISMATCH |
VARCHAR2(1) |
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) |
Mismatch in use of literal replacement |