Both parent cursor and child curor are stored in the library cache during the parse phase. Key parent cursor information is the SQL text itself. And child cursor is assosicated with parent cursor and contain execution plan and execution envrionment.
Below lab shows how to identity parent and child cursor and find out why they are not sharing.
Flush shared pool and exeute 4 querys, first 3 queries are different in lower/upper cases, and extra blank space. The forth sql statement is identical to first statement.
SQL> conn donghua/donghua
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t;
no rows selected
SQL> SELECT * FROM T;
no rows selected
SQL> select * from t;
no rows selected
SQL> select * from t;
no rows selected
Now check how many parent cursor has been created.
SQL> col sql_text for a30
SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 2 1
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1
We also need to understand that not every parent cursor has a unique sql plan. From below, they share the same sql execution plan.
SQL> select sql_id, sql_text, plan_hash_value from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';
SQL_ID SQL_TEXT PLAN_HASH_VALUE
------------- ------------------------------ ---------------
89km4qj1thh13 select * from t 1601196873
6k1fc899x4ud2 SELECT * FROM T 1601196873
3k2ncgn581pdv select * from t 1601196873
Execution envrionment changing will create new child child cursor, for example, new optimizer_mode value.
SQL> alter session set optimizer_mode='FIRST_ROWS_1';
Session altered.
SQL> select * from t;
no rows selected
SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 3 2
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1
Using synonym will not create new child cursor, as long as they refer to the same based object.
SQL> conn donghua1/donghua1@orcl
Connected.
SQL> create synonym t for donghua.t;
Synonym created.
SQL> select * from t;
no rows selected
SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 4 2
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1
Enable sql trace will cause sql statement parse again. And new child cursor will created. (Sometimes this is the reason we can not reproceduce some performance issue related to binding variable peeking by turn on sql trace.)
SQL> conn donghua/donghua@orcl
Connected.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from t;
no rows selected
SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 5 3
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1
To find out why child cursor is not shared by querying v$sql_shared_cursor.
SQL> select sql_text, child_number, optimizer_mode, plan_hash_value
2 from v$sql where sql_id='89km4qj1thh13';
SQL_TEXT CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------------------------ ------------ ---------- ---------------
select * from t 0 ALL_ROWS 1601196873
select * from t 1 FIRST_ROWS 1601196873
select * from t 2 ALL_ROWS 1601196873
QL> select child_number, child_address, stats_row_mismatch, optimizer_mode_mismatch
2 from v$sql_shared_cursor where sql_id='89km4qj1thh13';
HILD_NUMBER CHILD_AD S O
----------- -------- - -
0 2AD9B784 N N
1 2AC7B1B4 N Y
2 2ACA0C9C Y N
Possible reasons for a child cursor incompatible with anonther one listed below, (63 reasons in 11gR2).
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.0.1 - Beta
PL/SQL Release 11.2.0.0.1 - Beta
CORE 11.2.0.0.1 Beta
TNS for 32-bit Windows: Version 11.2.0.0.1 - Beta
NLSRTL Version 11.2.0.0.1 - Beta
SQL> desc v$sql_shared_cursor
Name Null? Type
----------------------------------------- -------- ----------------
SQL_ID VARCHAR2(13)
ADDRESS RAW(4)
CHILD_ADDRESS RAW(4)
CHILD_NUMBER NUMBER
UNBOUND_CURSOR VARCHAR2(1)
SQL_TYPE_MISMATCH VARCHAR2(1)
OPTIMIZER_MISMATCH VARCHAR2(1)
OUTLINE_MISMATCH VARCHAR2(1)
STATS_ROW_MISMATCH VARCHAR2(1)
LITERAL_MISMATCH VARCHAR2(1)
FORCE_HARD_PARSE VARCHAR2(1)
EXPLAIN_PLAN_CURSOR VARCHAR2(1)
BUFFERED_DML_MISMATCH VARCHAR2(1)
PDML_ENV_MISMATCH VARCHAR2(1)
INST_DRTLD_MISMATCH VARCHAR2(1)
SLAVE_QC_MISMATCH VARCHAR2(1)
TYPECHECK_MISMATCH VARCHAR2(1)
AUTH_CHECK_MISMATCH VARCHAR2(1)
BIND_MISMATCH VARCHAR2(1)
DESCRIBE_MISMATCH VARCHAR2(1)
LANGUAGE_MISMATCH VARCHAR2(1)
TRANSLATION_MISMATCH VARCHAR2(1)
BIND_EQUIV_FAILURE VARCHAR2(1)
INSUFF_PRIVS VARCHAR2(1)
INSUFF_PRIVS_REM VARCHAR2(1)
REMOTE_TRANS_MISMATCH VARCHAR2(1)
LOGMINER_SESSION_MISMATCH VARCHAR2(1)
INCOMP_LTRL_MISMATCH VARCHAR2(1)
OVERLAP_TIME_MISMATCH VARCHAR2(1)
EDITION_MISMATCH VARCHAR2(1)
MV_QUERY_GEN_MISMATCH VARCHAR2(1)
USER_BIND_PEEK_MISMATCH VARCHAR2(1)
TYPCHK_DEP_MISMATCH VARCHAR2(1)
NO_TRIGGER_MISMATCH VARCHAR2(1)
FLASHBACK_CURSOR VARCHAR2(1)
ANYDATA_TRANSFORMATION VARCHAR2(1)
INCOMPLETE_CURSOR VARCHAR2(1)
TOP_LEVEL_RPI_CURSOR VARCHAR2(1)
DIFFERENT_LONG_LENGTH VARCHAR2(1)
LOGICAL_STANDBY_APPLY VARCHAR2(1)
DIFF_CALL_DURN VARCHAR2(1)
BIND_UACS_DIFF VARCHAR2(1)
PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1)
CURSOR_PARTS_MISMATCH VARCHAR2(1)
STB_OBJECT_MISMATCH VARCHAR2(1)
CROSSEDITION_TRIGGER_MISMATCH VARCHAR2(1)
PQ_SLAVE_MISMATCH VARCHAR2(1)
TOP_LEVEL_DDL_MISMATCH VARCHAR2(1)
MULTI_PX_MISMATCH VARCHAR2(1)
BIND_PEEKED_PQ_MISMATCH VARCHAR2(1)
MV_REWRITE_MISMATCH VARCHAR2(1)
ROLL_INVALID_MISMATCH VARCHAR2(1)
OPTIMIZER_MODE_MISMATCH VARCHAR2(1)
PX_MISMATCH VARCHAR2(1)
MV_STALEOBJ_MISMATCH VARCHAR2(1)
FLASHBACK_TABLE_MISMATCH VARCHAR2(1)
LITREP_COMP_MISMATCH VARCHAR2(1)
PLSQL_DEBUG VARCHAR2(1)
LOAD_OPTIMIZER_STATS VARCHAR2(1)
ACL_MISMATCH VARCHAR2(1)
FLASHBACK_ARCHIVE_MISMATCH VARCHAR2(1)
LOCK_USER_SCHEMA_FAILED VARCHAR2(1)
REMOTE_MAPPING_MISMATCH VARCHAR2(1)
LOAD_RUNTIME_HEAP_FAILED VARCHAR2(1)
HASH_MATCH_FAILED VARCHAR2(1)
PURGED_CURSOR VARCHAR2(1)
BIND_LENGTH_UPGRADEABLE VARCHAR2(1)
