Oracle cursor sharing, parent cursor and child game subject concept

One of the main purposes of the sharedpool Memory Design in Oracle is statement sharing. Through statement sharing, the statement parsing time is reduced to improve the performance. If the sharedpool memory is too heavy, it is just a very simple demonstration here. To view statement sharing, you can use two data dictionaries: V $ SQLAREA and V $ SQLV $ SQLAREA.

One of the main purposes of the shared pool Memory Design in Oracle is statement sharing. Through statement sharing, the statement parsing time is reduced to improve the performance. If it is explained that the shared pool memory is too heavy, here is a very simple demonstration. To view statement sharing, you can use two data dictionaries: V $ SQLAREA and V $ SQL V $ SQLAREA.

One of the main purposes of the shared pool Memory Design in Oracle is statement sharing. Through statement sharing, the statement parsing time is reduced to improve the performance. If it is explained that the shared pool memory is too heavy, here is a very simple demonstration.

Two data dictionaries are available for viewing statement sharing: V $ SQLAREA and V $ SQL.
V $ SQLAREA: retains the parent cursor information of an SQL statement. It can be identified by SQL _ID. The VERSION_COUNT column indicates the number of child game targets.
V $ SQL : Retains the subcursor information of an SQL statement, which can be identified by SQL _ID and CHILD_NUMBER.

V $ SQL _SHARED_CURSOR: the reason why a sub-game is generated by a statement.

First, confirm the parameter cursor_sharing. The default value is EXACT. That is to say, the statement can be shared only when no variable is bound, including case and space carriage return.
SQL> conn/as sysdba
Connected.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
-----------------------------------------------------------------------------
Cursor_sharing String EXACT

Clear the shared_pool memory. This command can be used in the experiment. Exercise caution when using the production system library.

SQL> alter system flush shared_pool;
System altered.

There is a similar table named demo under SCOTT and TJ. This is an experiment scenario I have prepared. The following operations are performed. The light color indicates the first window or session, dark color indicates querying dynamic performance view information verification in another window or session

In the first window:
SQL> conn scott/tiger
Connected.
SQL> select * from demo;

EMPNO ENAME SAL
------------------------------
7369 SMITH 1200
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975

SQL & gt; select empno, ename from demo where empno = 7369;

EMPNO ENAME
--------------------
7369 SMITH

Since the above statement is the first execution after the share pool is cleared, Oracle needs to perform hard parsing to generate a cursor, Which is exactly a parent cursor and a child cursor, you can use V $ SQLAREA and V $ SQL to find relevant information.

In the second window:
[Oracle @ asm11g workshop] $ sqlplus/as sysdba
SQL * Plus: Release 11.2.0.3.0 Production on Sun Sep 16 21:20:24 2012
Copyright (c) 1982,201 1, Oracle.All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
And Real Application Testing options

SQL> get qs. SQL
1Col SQL _text for a50
2Set linesize 120
3Col exec for 999
4Col invalid for 99
5Col loads for 999
6Select SQL _text,
7 SQL _id,
8 Hash_value,
9 Executions exec,
10 Loads,
11 Invalidations invalid
12From v $ sqlarea
13 * where SQL _text like '& text %'
SQL> @ qs
Enter value for text: select empno
Old 9: where SQL _text like '& text %'
New 9: where SQL _text like 'select empno %'

SQL _TEXT SQL _ID HASH_VALUE EXEC LOADS INVALID
-----------------------------------------------------------------------------------------
Select empno, ename from demo where empno = 7369 Dhdkpzyv9b1w73063252871 1 1 0

SQL> select SQL _id, child_number, executions, loads from v $ SQL where SQL _id = 'dhdkpzyv9b1w7 ';

SQL _ID CHILD_NUMBER EXECUTIONS LOADS
----------------------------------------
Dhdkpzyv9b1w7 0 1 1

Through the query, we can see that the V $ SQLAREA data dictionary contains the parent cursor information, the statement is parsed once (LOADS), and the statement is executed once (EXEC, of course, similar information can be seen in V $ SQL.

To the first window:
SQL & gt; select empno, ename from demo where empno = 7369;

EMPNO ENAME
--------------------
7369 SMITH

When you run the previous statement again, the statement is shared because the cursor already exists in the share pool memory.

To the second window:
SQL> @ qs
Enter value for text: select empno
Old 9: where SQL _text like '& text %'
New 9: where SQL _text like 'select empno %'

SQL _TEXT SQL _ID HASH_VALUE EXEC LOADS INVALID
-----------------------------------------------------------------------------------------
Select empno, ename from demo where empno = 7369 Dhdkpzyv9b1w73063252871 2 1 0

SQL> select SQL _id, child_number, executions, loads from v $ SQL where SQL _id = 'dhdkpzyv9b1w7 ';

SQL _ID CHILD_NUMBER EXECUTIONS LOADS
----------------------------------------
Dhdkpzyv9b1w7 0 2 1

Through the above query, we can see that the statement is parsed once (LOADS), the statement is executed twice (EXEC), and the same information can be seen in the sub-cursor.

To the first window:
SQL & gt; select empno, ename from demo where empno = 7499;

EMPNO ENAME
--------------------
7499 ALLEN

The difference between this statement and the preceding statement is that I changed the condition to 7499, which is a brand new statement. Oracle needs to perform hard parsing and apply for a new parent and child cursor in the memory.

The second window:

SQL> @ qs
Enter value for text: select empno
Old 9: where SQL _text like '& text %'
New 9: where SQL _text like 'select empno %'

SQL _TEXT SQL _ID HASH_VALUE EXEC LOADS INVALID
-----------------------------------------------------------------------------------------
Select empno, ename from demo where empno = 7499 0m3wzw5mrdg8z1735834911 1 1 0
Select empno, ename from demo where empno = 7369 Dhdkpzyv9b1w73063252871 2 1 0

To the first window:
Switch the user to tj. The tj user also has the same table demo. Execute select empno, ename from demo where empno = 7369;
SQL> conn tj/tj
Connected.
SQL & gt; select empno, ename from demo where empno = 7369;

EMPNO ENAME
--------------------
7369 SMITH

To the second window:
SQL> @ qs
Enter value for text: select empno
Old 9: where SQL _text like '& text %'
New 9: where SQL _text like 'select empno %'

SQL _TEXT SQL _ID HASH_VALUE EXEC LOADS INVALID
-----------------------------------------------------------------------------------------
Select empno, ename from demo where empno = 7499 0m3wzw5mrdg8z1735834911 1 1 0
Select empno, ename from demo where empno = 7369 Dhdkpzyv9b1w73063252871 3 2 0

SQL> select SQL _id, child_number, executions, loads from v $ SQL where SQL _id = 'dhdkpzyv9b1w7 ';

SQL _ID CHILD_NUMBER EXECUTIONS LOADS
----------------------------------------
Dhdkpzyv9b1w7 0 2 1
Dhdkpzyv9b1w7 1 1 1

Although the statements written by tj users are the same as those written by scott users, the semantics is obviously different. The two demo tables belong to different users, so we can see that the Oracle parent cursor parsing (LOADS) add 1, holdThe number of rows is also increased by 1. From the perspective of the parent cursor, the statements are shared, but in the child game tag, there is a difference, a new child cursor CHILD_NUMBER is 1, resolution (LOADS) and executed once.

To the first window:

Execute statement again
SQL & gt; select empno, ename from demo where empno = 7369;

EMPNO ENAME
--------------------
7369 SMITH

To the second window:
SQL> @ qs
Enter value for text: select empno
Old 9: where SQL _text like '& text %'
New 9: where SQL _text like 'select empno %'

SQL _TEXT SQL _ID HASH_VALUE EXEC LOADS INVALID
-----------------------------------------------------------------------------------------
Select empno, ename from demo where empno = 7499 0m3wzw5mrdg8z1735834911 1 1 0
Select empno, ename from demo where empno = 7369 Dhdkpzyv9b1w73063252871 4 2 0

SQL> select SQL _id, child_number, executions, loads from v $ SQL where SQL _id = 'dhdkpzyv9b1w7 ';

SQL _ID CHILD_NUMBER EXECUTIONS LOADS
----------------------------------------
Dhdkpzyv9b1w7 0 2 1
Dhdkpzyv9b1w7 1 2 1

We can see that the number of times the statement parent cursor is parsed does not increase, and the number of executions is increased by 1. For a child cursor, the number of executions of a child cursor whose CHILD_NUMBER is 1 is increased by 1.

There are many reasons for creating sub-game targets, such as the above user solution (SCHEMA) change examples. Of course, there are many other reasons that can also lead to the generation of sub-game targets, such as the change of the optimizer mode, or, if you want to determine the cause, you need to check v $ SQL _shared_cursor.
For the previous example, the verification/transaction check does not match

SQL> select SQL _id, CHILD_NUMBER, AUTH_CHECK_MISMATCH from v $ SQL _shared_cursor where SQL _id = 'dhdkpzyv9b1w7 ';

SQL _ID CHILD_NUMBER
--------------------------
Dhdkpzyv9b1w7 0 N
Dhdkpzyv9b1w7 1 Y