In this post I will discuss about parent and child cursors – what do they contain, when are they created and how and why can we ensure that they can be shared.
(Please click
here for the video of this post)
A cursor is a memory area in library cache allocated to a SQL statement which stores various info about the SQL statement like its text, execution plan, statistics etc.
Each SQL statement has
- One Parent cursor
- One or more child cursors
PARENT CURSOR
- It stores the sql text of the cursor. When two statements are identical textually, they will share the same parent Cursor.
- Externalised by V$SQLAREA: Contains one row for each parent cursor
CHILD CURSOR
- Each parent requires at least one child cursor but can have more than one child cursors
- The Child Cursor holds other required information, like: the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used..etc.
- Child cursor contains
. Environment
. Statistics
. Execution Plan
. Bind variables
- Externalised by V$SQL : Contains one row for each child cursor
- A child cursor takes up less space in the cursor cache. A child cursor doesn’t contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.
Since we want to economize on the memory consumption, we would like that equivalent SQL statements should use the same cursor e.g. select * from employees and SELECT * FROM EMPLOYEES achieve the same objective and have the same execution plan and hence only one cursor should be created and should be used when either of the statements is issued. But it won’t be so and two parent and hence two child cursors will be created since the two statements are textually different .
If we have two textually identical statements, only one parent cursor will be created but multiple child cursors and hence multiple execution plans can be created if for example bind variables have different values/sizes for different executions of the same statement.
When you have the same statement that has several versions (children), the view v$sql_shared_cursor shows the reason why the statement cannot be shared. You may be able to find that for each child cursor except the first one, why it was not possible to share a previously created child cursor. For several types of incompatibility there is a column that is set to either N (not a mismatch) or Y (mismatch).
The following table lists various columns which represent different types of incompatibilities which could lead to non sharing of the child cursors:
|
ANYDATA_TRANSFORMATION
|
Is criteria for opaque type transformation and does not match
|
|
AUTH_CHECK_MISMATCH
|
Authorization/translation check failed for the existing child cursor
|
|
BIND_MISMATCH
|
The bind metadata does not match the existing child cursor. Likely a difference in bind variable definition.
|
|
BIND_PEEKED_PQ_MISMATCH
|
Cursor based around bind peeked values
|
|
BIND_UACS_DIFF
|
One cursor has bind UACs and one does not
|
|
BUFFERED_DML_MISMATCH
|
Buffered DML does not match the existing child cursor
|
|
CURSOR_PARTS_MISMATCH
|
Cursor was compiled with subexecution (cursor parts were executed)
|
|
DESCRIBE_MISMATCH
|
The typecheck heap is not present during the describe for the child cursor
|
|
DIFF_CALL_DURN
|
If Slave SQL cursor/single call
|
|
DIFFERENT_LONG_LENGTH
|
Value of LONG does not match
|
|
EXPLAIN_PLAN_CURSOR
|
The child cursor is an explain plan cursor and should not be shared
|
|
FLASHBACK_CURSOR
|
Cursor non-shareability due to flashback
|
|
FLASHBACK_TABLE_MISMATCH
|
Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
|
|
INCOMP_LTRL_MISMATCH
|
Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
|
|
INCOMPLETE_CURSOR
|
Cursor is incomplete: typecheck heap came from call memory
|
|
INST_DRTLD_MISMATCH
|
Insert direct load does not match the existing child cursor
|
|
INSUFF_PRIVS
|
Insufficient privileges on objects referenced by the existing child cursor
|
|
INSUFF_PRIVS_REM
|
Insufficient privileges on remote objects referenced by the existing child cursor
|
|
LANGUAGE_MISMATCH
|
The language handle does not match the existing child cursor
|
|
LITERAL_MISMATCH
|
Non-data literal values do not match the existing child cursor
|
|
LITREP_COMP_MISMATCH
|
Mismatch in use of literal replacement
|
|
LOGICAL_STANDBY_APPLY
|
Logical standby apply context does not match
|
|
LOGMINER_SESSION_MISMATCH
|
LogMiner Session parameters mismatch
|
|
MULTI_PX_MISMATCH
|
Cursor has multiple parallelizers and is slave-compiled
|
|
MV_QUERY_GEN_MISMATCH
|
Internal, used to force a hard-parse when analyzing materialized view queries
|
|
MV_REWRITE_MISMATCH
|
Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
|
|
MV_STALEOBJ_MISMATCH
|
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
|
|
NO_TRIGGER_MISMATCH
|
Cursor and child have no trigger mismatch
|
|
OPTIMIZER_MISMATCH
|
A change to any of 33 supported parameters such as SORT_AREA_SIZE or OPTIMIZER_INDEX_COST_ADJUSTMENT and 151 unsupported parameters such as _unnest_subquery that change the optimizer environment.
|
|
OPTIMIZER_MODE_MISMATCH
|
Optimizer mode has changed (for example, ALL_ROWS vs CHOOSE)
|
|
OUTLINE_MISMATCH
|
The outlines do not match the existing child cursor
|
|
OVERLAP_TIME_MISMATCH
|
Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME
|
|
PDML_ENV_MISMATCH
|
PDML environment does not match the existing child cursor
|
|
PLSQL_CMP_SWITCHS_DIFF
|
PL/SQL anonymous block compiled with different PL/SQL compiler switches. See DBMS_WARNING page of the library. |
|
PQ_SLAVE_MISMATCH
|
Top-level slave decides not to share cursor
|
|
PX_MISMATCH
|
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.
|
|
REMOTE_TRANS_MISMATCH
|
The remote base objects of the existing child cursor do not match
|
|
ROLL_INVALID_MISMATCH
|
Marked for rolling invalidation and invalidation window exceeded
|
|
ROW_LEVEL_SEC_MISMATCH
|
The row level security policies do not match
|
|
ROW_SHIP_MISMATCH
|
Session does not support row shipping, but cursor built in one that did
|
|
SEC_DEPTH_MISMATCH
|
Security level does not match the existing child cursor
|
|
SLAVE_QC_MISMATCH
|
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)
|
|
SQL_REDIRECT_MISMATCH
|
SQL redirection mismatch
|
|
SQL_TYPE_MISMATCH
|
The SQL type does not match the existing child cursor
|
|
STATS_ROW_MISMATCH
|
The existing statistics do not match the existing child cursor. May be caused by tracing
|
|
STB_OBJECT_MISMATCH
|
STB has come into existence since cursor was compiled
|
|
TOP_LEVEL_DDL_MISMATCH
|
Is top-level DDL cursor
|
|
TOP_LEVEL_RPI_CURSOR
|
Is top level RPI cursor
|
|
TRANSLATION_MISMATCH
|
The base objects of the existing child cursor do not match. For example objects in different schemas with the same name.
|
|
TYPCHK_DEP_MISMATCH
|
Cursor has typecheck dependencies
|
|
TYPECHECK_MISMATCH
|
The existing child cursor is not fully optimized
|
|
UNBOUND_CURSOR
|
The existing child cursor was not fully built (in other words, it was not optimized)
|
|
USER_BIND_PEEK_MISMATCH
|
Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan
|
Let us see how multiple parent parent/child cursors can be created for SQL statements.
Multiple Parent Cursors
- Created because of differences in SQL statement text
- The following statements all require separate parent cursors:
. SELECT COUNT(*) FROM employees;
. Select count(*) from employees;
. SELECT COUNT(*) FROM EMPLOYEES;
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>SELECT COUNT(*) FROM employees;
Select count(*) from employees;
SELECT COUNT(*) FROM EMPLOYEES;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that
.there is one record for each statement in v$sqlarea as one parent cursor is created for each sql statement since each of these statements differ in their text.
. Each statement has different SQL_ID/HASH_VALUE
.There is one child per parent cursor (version_count=1)
SYS>col sql_text for a30 word_wrapped
SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
—————————— ————- ————- ———-
SELECT COUNT(*) FROM 2ybtna401c2x2 1 1444770
employees
SELECT COUNT(*) FROM 4ba5cdz25czfq 1 3294002646
EMPLOYEES
Select count(*) from 99a1j2spyr81k 1 736862258
employees
– Let’s check v$SQL for child cursors for above statements.
- Note that
. Each statement has one child (CHILD# = 0)
. Each statement uses same execution plan (same PLAN_HASH_VALUE)
SYS>col child_number for 99
SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE
FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE
—————————— ————- ———- —————— ————————-
SELECT COUNT(*) FROM 2ybtna401c2x2 0 1444770 3580537945
employees
SELECT COUNT(*) FROM 4ba5cdz25czfq 0 3294002646 3580537945
EMPLOYEES
Select count(*) from 99a1j2spyr81k 0 736862258 3580537945
employees
Although same execution plan is being used by the 3 statements still they were individually parsed and their parent cursors are consuming memory.
Hence to enable sharing of the parent cursors, we should follow standard formatting of the sql statements.
Multiple Child Cursors for the same parent cursor
- Can be created for a number of reasons . In this post, I will discuss creation of multiple child cursors due to differences in:
. System / Session parameters
. Object translation
. Bind variables (Name and value)
. NLS parameters
-- Difference in System / Session Parameters
Multiple c hild cursors maybe creatd for the same SQL due to difference in system / session parameters
- Let’s verify that different optimizer modes require separate child cursors
- Issue same SQL statement with different values of the parameter optimizer_mode
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>ALTER SESSION SET optimizer_mode = CHOOSE;
SELECT COUNT(*) FROM EMPLOYEES;
- Change optimizer mode to ALL_ROWS
HR>ALTER SESSION SET optimizer_mode = ALL_ROWS;
SELECT COUNT(*) FROM EMPLOYEES;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that
.there is ONLY one record for both the statements in v$sqlarea as one parent cursor is created for both sql statements since each of these statements are identical in their text.
. Both the statements have same SQL_ID/HASH_VALUE
.There are two childs per parent cursor (version_count=2)
SYS>col sql_text for a30 word_wrapped
SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
—————————— ————- ——————— —————–
SELECT COUNT(*) FROM 4ba5cdz25czfq 2 3294002646
EMPLOYEES
- Let’s check v$SQL for child cursors for above statements.
- Note that
. The statement has two children (CHILD# = 0,1)
. OPTIMIZER_MODE is stored in V$SQL for each child cursor
SYS>col child_number for 99
SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
child_address, OPTIMIZER_MODE OPT_MODE
FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
AND LOWER(SQL_TEXT) NOT LIKE ‘%hash%';
SQL_TEXT SQL_ID CHILD# HASH_VALUE CHILD_AD OPT_MODE
—————————— ————- ———- —————– ———— ———-
SELECT COUNT(*) FROM 4ba5cdz25czfq 0 3294002646 4C202DB0 CHOOSE
EMPLOYEES
SELECT COUNT(*) FROM 4ba5cdz25czfq 1 3294002646 4C1FEBF8 ALL_ROWS
EMPLOYEES
– Let’s find out from V$SQL_SHARED_CURSOR why the child cursor was not shared
Note that in second child cursor was created because of mismatch in optimizer mode
(OPTIMIZER_MODE_MISMATCH = Y)
SQL>col OPTIMIZER_MODE_MISMATCH for a25
SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, C.OPTIMIZER_MODE_MISMATCH
FROM V$SQL S, V$SQL_SHARED_CURSOR C
WHERE LOWER(S.SQL_TEXT) LIKE ‘%select count(*) from employees’
AND LOWER(S.SQL_TEXT) NOT LIKE ‘%hash%’
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT CHILD# CHILD_AD OPTIMIZER_MODE_MISMATCH
—————————— ———– ——– ———————————
SELECT COUNT(*) FROM 0 4C202DB0 N
EMPLOYEES
SELECT COUNT(*) FROM 1 4C1FEBF8 Y
EMPLOYEES
OBJECT TRANSLATIONS
If a statement references different objects with the same name then multiple child cursors can be generated
For example, we will create two tables with same name t1 with same structure in HR and SCOTT schema and issue identical SQl statement referring to the two tables.
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
SCOTT>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
The statement SELECT c1 FROM t1 will have a shared parent cursor, but multiple child cursors
Parent (select c1 from t1;)
+
+————–+————-+
| |
Child I Child II
(HR.t1) (SCOTT.t1)
– Let’s check v$SQLAREA for parent cursors for above statements.
It can be seen that only one parent cursor has been created with 2 child cursors (version_count=2)
SYS>col sql_text for a30 word_wrapped
SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
————————————————- ————- ————- ————————–
SELECT COUNT(*) FROM EMPLOYEES 4ba5cdz25czfq 2 3294002646
- Let’s check v$SQL for child cursors for above statements.
SYS>col child_number for 99
SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
PLAN_HASH_VALUE, USERNAME
FROM V$SQL S, DBA_USERS U
WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%’
AND U.USER_ID = S.PARSING_USER_ID;
SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE USER
—————————— ————- ———- ———- ————— —————– ——-
SELECT c1 FROM t1 68buhgvjct1pg 0 3805054639 3617692013 HR SELECT c1 FROM t1 68buhgvjct1pg 1 3805054639 3617692013 SCOTT
- Note that
. The statement has two children (CHILD# = 0,1) because they refer to different objects
. ID of the user executing the statement is stored in V$SQL (parsing_user_id)
for each child cursor
– Find out why the child cursor was not shared
SQL>col TRANSLATION_MISMATCH for a25
SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, C.TRANSLATION_MISMATCH
FROM V$SQL S, V$SQL_SHARED_CURSOR C
WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
AND LOWER(S.SQL_TEXT) NOT LIKE ‘%HASH%’
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT CHILD_NUMBER CHILD_AD TRANSLATION_MISMATCH
—————————— —————— ————- ———————————
SELECT c1 FROM t1 0 4C3181C8 N
SELECT c1 FROM t1 1 4C3EE02C Y
Note that in second child cursor was created because of translation mismatch
(TRANSLATION_MISMATCH = Y)
BIND VARIABLES (SIZE AND VALUE)
Multiple child cursors are created for different lengths and values of bind variables .
For example , we will issue the same statement containing bind variable twice but with different size of the bind variable.
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>DROP TABLE T1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(50),c2 NUMBER);
VARIABLE v1 VARCHAR2(30);
SELECT c2 FROM t1 WHERE c1 = :v1;
VARIABLE v1 VARCHAR2(40);
SELECT c2 FROM t1 WHERE c1 = :v1;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that two child cursors are created (VERSION_COUNT = 2)
SYS>col sql_text for a35 word_wrapped
SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
———————————– ————- ————- ———————–
SELECT c2 FROM t1 c0w72qhanq1zs 2 357238776
WHERE c1 = :v1
- Let’s check v$SQL for child cursors for above statements.
- Note that
. The statement has two children (CHILD# = 0,1) because size of bind variables is different although the execution plan is same (same PLAN_HASH_VALUE)
SYS>col child_number for 99
SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
PLAN_HASH_VALUE
FROM V$SQL
WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %';
SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE
———————————– ————- ———- ———- ——————————
SELECT c2 FROM t1 c0w72qhanq1zs 0 357238776 3617692013
WHERE c1 = :v1
SELECT c2 FROM t1 c0w72qhanq1zs 1 357238776 3617692013
WHERE c1 = :v1
– Find out why the child cursor was not shared
Note that in second child cursor was created because of bind variable length mismatch
(BIND_LENGTH_UPGRADEABLE = Y)
SQL>col BIND_LENGTH_UPGRADEABLE for a25
SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS,BIND_LENGTH_UPGRADEABLE
FROM V$SQL S, V$SQL_SHARED_CURSOR C
WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %’
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT CHILD_NUMBER CHILD_AD BIND_LENGTH_UPGRADEABLE
———————————– —————– ——– —————————————–
SELECT c2 FROM t1 0 4C26D7E0 N
WHERE c1 = :v1
SELECT c2 FROM t1 1 4C252500 Y
WHERE c1 = :v1
NLS PARAMETERS
Multiple child cursors are created for the same statement for different values of NLS parameters. Only a subset of SQL statements are affected which use
Dates
Currency
Ordering
For example, we will issue the same SQL statement with different values of NLS_LANGUAGE
HR>VAR b1 VARCHAR2(30);
EXECUTE :b1 := SYSDATE;
ALTER SESSION SET NLS_LANGUAGE = ‘AMERICAN‘;
SELECT TO_CHAR (TO_DATE (:b1,’DD-MON-YYYY’),’yyyymmdd’) FROM dual;
ALTER SESSION SET NLS_LANGUAGE = ‘GERMAN';
SELECT TO_CHAR (TO_DATE (:b1,’DD-MON-YYYY’),’yyyymmdd’) FROM dual;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that two child cursors are created (VERSION_COUNT = 2)
SYS>col sql_text for a35 word_wrapped
SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
—————————————– ————- —————————- ———-
SELECT TO_CHAR (TO_DATE 5588uyghqp14w 2 3781854364
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
- Let’s check v$SQL for child cursors for above statements.
- Note that
. The statement has two children (CHILD# = 0,1) because of the difference in NLS_LANGUAGE parameter
SYS>col child_number for 99
SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
PLAN_HASH_VALUE
FROM V$SQL
WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%';
SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE
———————————– ————- ———- ———- ————————————–
SELECT TO_CHAR (TO_DATE 5588uyghqp14w 0 3781854364 1388734953
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
SELECT TO_CHAR (TO_DATE 5588uyghqp14w 1 3781854364 1388734953
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
– Find out why the child cursor was not shared
Note that in second child cursor was created because oflanguage mismatch
LANGUAGE_MISMATCH = Y)
SQL>col LANGUAGE_MISMATCH for a25
SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS,LANGUAGE_MISMATCH
FROM V$SQL S, V$SQL_SHARED_CURSOR C
WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%’
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT CHILD_NUMBER CHILD_AD LANGUAGE_MISMATCH
—————————————- ———— —————————————————
SELECT TO_CHAR (TO_DATE 0 4A0EFF20 N
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
SELECT TO_CHAR (TO_DATE 1 4A0EFADC Y
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
Conclusion:
- To enable sharing of parent cursors we should follow code formatting standards
- To enable sharing of child cursors we should ensure that
. Optimizer mode is not changed in sessions.
. Length of bind variables used should be same.
. Values of NLS parameters should be same.
————————————————————————
Related links :
Home
Database Index
Tuning Index
Cursor Sharing Demystified
Library Cache Lock And Pin Demonstrated
Latches, Locks, Pins And Mutexes
Shared Pool Architecture
————————-