Parent cursor, child cursor, and shared cursor

Cursor is a complex concept in the database field, because it contains shared cursor and session cursor. The two have different concepts and forms.
The concept of shared cursor is easy to confuse with the cursor defined in SQL statements. This article describes the parent cursor, child cursor, and shared cursor in the parsing process, that is, shared cursor.
The lifecycle of the session cursor and the description of the parsing process of the cursor.

For the definition, declaration, and usage of the cursor, see PL/SQL cursor.
For more information about hard parsing and soft parsing, see Oracle hard parsing and soft parsing.

I. Related definitions
Shared cursor
That is, shared cursors are generated by SQL statements in the cursor parsing phase. They are SQL statements located in the library cache or anonymous PL/SQL statements. Its metadata is stored in the view v $ sqlarea
Specific to V $ SQL. If the parent cursor and child cursor in the library cache can be shared, the shared cursor is used. The parent cursor can be shared, that is, the shared parent cursor.
Sub-cursors that can be shared with each other.

Session cursor
That is, the system allocates a buffer for users to store the execution results of SQL statements. You can use this intermediate buffer to retrieve and process records in the cursor one by one
Some cursor records are processed one by one. Session cursor refers to the memory area of the PGA (UGA) of the server process corresponding to this session.
(Or memory structure) that is, its main feature is to locate records one by one and process them one by one. Session cursor metadata is embodied in the V $ open_cursor view. Each
The opened or parsed SQL statements are in this view.

Ii. Session cursor)
Session cursor needs to allocate memory from UGA, so it has its lifecycle. Its lifecycle mainly includes:
Open the cursor (allocate the memory area in UGA according to the name declared by the cursor)
Resolve the cursor (associate the SQL statement with the cursor and load the execution plan to the library cache)
Define output variables (only when the cursor returns data)
Bind the input variable (if the SQL statement associated with the cursor uses the Bind Variable)
Execute the cursor (that is, execute the SQL statement)
Obtain the cursor (that is, obtain the SQL statement record result, and perform corresponding operations on the record as needed. The cursor retrieves the queried records one by one until all records are obtained)
Close the cursor (release the resources occupied by the cursor in the UGA, but the execution plan of the cursor in the library cache is cleared according to the LRU principle to provide the possibility of sharing the cursor)

For session cursor, the cursor can be understood as any DML or dql statement (for personal understanding, to be verified ). That is, an SQL statement is actually a cursor,
Session cursor can be divided into display cursor, implicit cursor, and cursor pointer. From the lifecycle of the cursor above, we can see that any cursor (SQL statement) must go through memory allocation, parsing,
Execution and shutdown processes. Therefore, for implicit cursors, the system automatically completes all lifecycle processes. For all DML and single row queries (Select...,
The system automatically uses an implicit cursor. Dql of a multi-row result set usually uses a display cursor.

Ii. cursor parsing process (generate shared cursor)
Resolution Process:

A. constraints including VPD:
If the SQL statement uses row-level security control for the table, add the constraints generated by the Security Policy to the WHERE clause.

B. Check the syntax, semantics, and access permissions:
Check the correctness of SQL statement writing, object existence, and user access permissions.

C. Parent cursor cache:
Hash the text of the cursor (SQL statement) to obtain the hash value and search for the same hash value in the library cache. If no hash value exists, the parent cursor is saved in the library cache.
In order to complete the D-F step. If a parent cursor exists at this time, the system further checks whether a child cursor exists. If the same sub-cursor exists, the execution plan of the sub-cursor is called directly.
Execute the SQL statement. Otherwise, go to step d for logical optimization.

D. Logical optimization:
Different conversion techniques are used to generate a new SQL statement with the same semantics (SQL statement rewriting). Once this operation is completed, the number of execution plans and search space will increase accordingly.
The main purpose is to find an execution plan that cannot be considered without conversion.

E. Physical optimization:
Generates execution plans for SQL statements in the logical Optimization phase, reads statistics in the data dictionary and statistics of dynamic sampling, calculates overhead, and the Execution Plan with the lowest overhead will be
Selected.

F. Sub-cursor cache:
Allocate memory, generate a sub-cursor (that is, the best execution plan), and associate it with the parent cursor. You can obtain the specific cursor information in V $ sqlarea and V $ SQL, and the parent and child cursor is associated by SQL _id.

For SQL statements that only complete steps A and B, soft resolution is used; otherwise, hard resolution is used.

Iii. Relationship between shared cursor and session cursor and soft Parsing
Link:
One session cursor can correspond to only one shared cursor, while one shared cursor may correspond to multiple session cursor at the same time.

4. Parent and Child cursors and shared cursors
The parsing process of the cursor shows that the parent cursor and the child game tag belong to the shared cursor category.
Parent cursor
It is generated during hard parsing. The parent cursor mainly contains two types of information: SQL text and optimizer goal. The first time the parent cursor is opened, it is locked until other
All sessions are unlocked only after the cursor is closed. When the parent cursor is locked, the library cache cannot be replaced by the LRU algorithm.
Library cache. All child cursors corresponding to the parent cursor are also replaced by the library cache. Each row in V $ sqlarea represents a parent cursor,
Address indicates the memory address.

Child cursor
In case of hard parsing, when a parent cursor is generated, a child cursor will be generated following the parent cursor. At this time, the value of V $ SQL. child_number is 0.
If a parent cursor exists, a new child cursor is generated because of different runtime environments. The child_number of the new Child game subject is accumulated in units of 1 on the basis of the existing child cursor.
Child cursors include all the information about the cursor, such as the specific execution plan, binding variables, objects and permissions, and optimizer settings. The sub-cursor can be replaced by the LRU algorithm at any time.
Library cache. When the sub-cursor is replaced by the library cache, Oracle can use the parent cursor information to re-build a sub-cursor. This process is called reload.
Each row in V $ SQL represents a child cursor, which is associated with parent cursor Based on Hash Value and address.
Child cursor has its own address, that is, V $ SQL. child_address.

Determine the three main fields of a cursor: Address, hash_value, child_number,

V. Demonstration of parent and child cursors

/************************************ First create a table t **************************************/ <br/> SQL> Create Table T as select empno, ename, Sal from EMP where deptno = 10; </P> <p> table created. <br/> ********************************* perform a query ************************************** * **/<br/> SQL> select * from t where empno = 7782; </P> <p> empno ename Sal <br/> ---------- <br/> 7782 Clark 2450 </P> <p> SQL> select * from t where empno = 7782; </P> <p> empno ename Sal <br/> ---------- <br/> 7782 Clark 2450 </P> <p> SQL> select * from t where empno = 7782; </P> <p> empno ename Sal <br/> ---------- <br/> 7782 Clark 2450 </P> <p> SQL> select * from t where empno = 7782; </P> <p> empno ename Sal <br/> ---------- <br/> 7782 Clark 2450 </P> <p> /******** * ************* query by the following (V $ sqlarea) three parent cursors are generated, one of which is 2r6rbdp92kyh9) */<br/> /****************** * *****************************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * *****************/</P> <p> SQL> Col SQL _text format A40 <br/> SQL> select SQL _id, SQL _text, executions from V $ sqlarea <br/> 2 where SQL _text like '% empno = 7782%' and SQL _text not like '% from V $ sqlarea % '; </P> <p> SQL _id SQL _text executions <br/> ------------- certificate ---------- <br/> 4rs2136z084y1 select * from t where empno = 7782 1 <br/> 84w067b4n91h5 select * from t where empno = 7782 1 <br/> 2r6rbdp92kyh9 select * from t where empno = 7782 2 </P> <p>/************ above */<br/> SQL> select SQL _id, hash_value, child_number, plan_hash_value, SQL _text, executions from V $ SQL <br/> 2 where SQL _text like '% empno = 7782%' and SQL _text not like '% from V $ SQL % '; </P> <p> SQL _id hash_value child_number plan_hash_value SQL _text executions <br/> ------------- ---------- ------------ limit ---------- <br/> limit 3187938241 0 1601196873 select * from t where empno = 7782 1 <br/> 84w067b4n91h5 3376711173 0 1601196873 select * from t where empno = 7782 1 <br/> 2r6rbdp92kyh9 1378449929 0 1601196873 select * from t where empno = 7782 2 </P> <p >/****************** adjust the optimizer_index_caching parameter and execute the Aggregate Query ************* * **********/<br/> SQL> alter session set optimizer_index_caching = 40; </P> <p> session altered. </P> <p> SQL> select sum (SAL) from t; </P> <p> sum (SAL) <br/> ---------- <br/> 8750 </P> <p> SQL> alter session set optimizer_index_caching = 100; </P> <p> session altered. </P> <p> SQL> select sum (SAL) from t; </P> <p> sum (SAL) <br/> ---------- <br/> 8750 </P> <p>/**************** the same query varies depending on the Running the environment leads to different child cursors, * the child game is marked with different child_address values ***************************/<br/> SQL> select SQL _id, child_number, SQL _text, optimizer_env_hash_value oehv, child_address <br/> 2 from V $ SQL where SQL _text like '% sum (SAL) % 'and SQL _text not like' % from V $ SQL % '; </P> <p> SQL _id child_number SQL _text oehv child_address <br/> ------------- ------------ ---------- ---------------- <br/> gu68ka2qzx3hh 0 select sum (SAL) from t 3620536549 00000000900006d00 <br/> gu68ka2qzx3hh 1 select sum (SAL) from t 2687219005 then rj93767f58 </P> <p>/*********** query v $ SQL _shared_cursor to track the changes that cause the sub-cursor to be unable to share, in this example, optimizer_mismatch ******************/<br/> SQL> select child_number, optimizer_mismatch <br/> 2 from V $ SQL _shared_cursor <br/> 3 where SQL _id = '& SQL _id'; <br/> enter value for SQL _id: gu68ka2qzx3hh <br/> old 3: where SQL _id = '& SQL _id' <br/> New 3: where SQL _id = 'gu68ka2qzx3hh' </P> <p> child_number o <br/> -------------<br/> 0 n <br/> 1 y <br/> /** * ******************** observe the parent cursor address, hash_value, SQL _id ******************/<br/> /*************** * ******* observe the sub-cursor address, hash_value, child_number, SQL _id, child_address ******************/<br/> /*************** * ******* after 10 Gb of Oracle, SQL _id identifies a parent cursor, SQL _id, and child_number uniquely identifies a subcursor *****/<br/> SQL> select address, hash_value, SQL _id from V $ sqlarea where SQL _id = 'gu68ka2qzx3hh '; </P> <p> address hash_value SQL _id <br/> ---------------- --------------- <br/> limit 9f8cbb58 2919140880 limit </P> <p> SQL> select address, hash_value, child_number, SQL _id, child_address <br/> 2 from V $ SQL where SQL _id = 'gu68ka2qzx3hh '; </P> <p> address hash_value child_number SQL _id child_address <br/> ------------------ -------------- -------------------------- <br/> limit 2919140880 0 limit <br/> limit 2919140880 1 limit <br/> br/>
Vi. Summary
1. Hard Parsing is usually caused by an unshared parent cursor, such as SQL statements that change frequently, dynamic SQL statements, or variables that are not bound.
2. The solution to hard Parsing is usually to bind variables to solve the problem.
3. When the SQL Text of the parent cursor is exactly the same, multiple identical SQL statements can share one parent cursor.
4. When the SQL text and execution environment are completely consistent, the sub-cursor can be shared. Otherwise, a new sub-cursor is generated if the execution environment is inconsistent.