A “cursor” is a memory area in the library cache that is allocated to the SQL statement which users execute. This memory area stores key information about the SQL statement like SQL text, SQL execution plan, statistics etc.
WHY TWO KINDS OF CURSOR?
This is by Oracle database design that you have two kinds of cursors: Parent and Child. For each SQL statement that you execute, Oracle engine will generate two cursors: parent and child cursor. Two cursors are generated because for the same SQL statement, there could be other differences like there can be different bind values or two different schema or different literals values, etc. The parent Cursor will hold the SQL statement and the child cursor will hold the information related to the differences. This essentially makes child cursor as deciding factor as to SQL statement will go for hard or soft parse.
PARENT CURSOR
- It stores the SQL text of the cursor. When two statements are identical word-by-word, they will share the same parent Cursor.
- Every parent cursor would execute with at least one child cursor created for it.
- Parent cursors are represented in the view V$SQLAREA. VERSION_COUNT column in the v$sqlarea can tell us how many child cursors does this parent cursor have.
CHILD CURSOR
- Each parent has at least one child cursor and can have more than 1 child cursors also
- While parent cursor stores the SQL Text, the child cursor stores other important information related to SQL statement like: Environment details, Statistics details, Bind Variables details, Execution Plan details Bind Variables details.
- Child Cursor takes less memory space as SQL Text is not stored in child cursor
- Every child cursor must belong to a parent
- Child cursor decides whether a query will undergo a hard parse or a soft parse. You may find situation that SQL query is same for two statements so Parent cursors are same but the child cursor is not shareable to SQL goes for hard parse (re-compile).
- Parent cursors are represented in the view V$SQL
- V$SQL_SHARED_CURSOR is very useful view as it provides the reasons why the optimizer decided mark the cursor as un-shared. So anytime you see that SQL statement was same and still hard parse happened, look at this view.
V$SQL_SHARED_CURSOR
This view explains why a particular child cursor is not shared with existing child cursors which caused more than one child cursor to be created for same Parent cursor. Each column in this view identifies a specific reason why the cursor cannot be shared. The columns describe the various reasons with “Y” or “N” for the value. You should focus on the column which has value as ‘Y’. A specific child may have failed sharing for several reasons – ie: a different reason for trying to use different existing child cursors.
CONCEPT OF cursor_sharing DATABASE PARAMETER
Since we are discussing parent and child cursors, it is very important that we discuss a bit about the cursor_sharing database parameter. CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
cursor_sharing database parameters can have three different values:
EXACT
Only allows statements with identical text to share the same cursor.
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
The default cursor_sharing criteria is EXACT which means that each different SQL statement a new parent cursor is created. Example, below are two different SQL statements:
select * from EMP WHERE EMP_ID=1; select * from EMP where EMP_ID=1;
These are two different SQLs although both will produce same result. “where” is written in capital letter in first statement while in the second statement it is written in small letters.
Even below two statements are two different SQLs.
select * from EMP where EMP_ID=1; select * from EMP where EMP_ID=2;
These are different SQLs as literal values ( 1 and 2) are different. Executing above will create two Parent cursors if cursor_sharing parameter is EXACT.
Whereas, if you put cursor_sharing criteria is FORCE or SIMILAR, executing above two SQLs will generate single Parent cursor. When we do this, Oracle strips out all the literals from the query and replaces them with bind variables in the optimization phase.Please keep in mind that making cursor_sharing is not always an advantage. It can prove bad for SQL performance also as we will discuss in further posts.
This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021
Post says “Parent cursors are represented in the view V$SQL”, do you mean that for Child cursors instead?
Yes….That must be a typo….v$sql is for child cursor info
Thanks for pointing out the same.
“Parent cursors are represented in the view V$SQL ” is not right should be “Child cursors are represented in the view V$SQL”