Jingjing experiment 15th sharing SQL zone, private SQL zone, and cursor

Shared SQL zone, private SQL zone, and cursor

I. Share the SQL Zone
The shared SQL zone is another way of calling the handle of the cursor object in the Library cace and the data of the Child heap.
Ii. Concept of private SQL Zone
Private SQL zone, which is stored in PGA in dedicated server mode.
(Review the concept of PGA:
PGA is allocated on the server side and records information about processes connected to the Oracle server, such as the username and password at logon. Each session log on to Oracle occupies a PGA on the server. When a session is just logged on, each user's PGA occupies about several hundred KB of memory, and can be extended and reduced according to the operations in the session in the future .)
The private SQL zone is divided into two regions, which have different lifecycles:
· The persistent area cursor is released when it is closed.
· Run-time area, which is released when the cursor execution ends.
1 ).
Persistent area)
When a cursor is opened, the memory in the continuous zone is created. Contains bind information, SQL statement, and other data. This zone will be released only when the cursor is closed.
2 ).
Runtime Time Zone
The cursor is created during execution. The statement is released after execution or cancel.
The running zone or session-specific zone is the status information of the cursor maintained by your session, for example: the SCN at the beginning of your query, the location of the current result set, or other things specific to your session.
In addition, the workspace (for sorting, hash links, Bitmap merge and Bitmap create) is part of the runtime time zone.
If we execute the select name from mytable order by name; Statement, the Oracle statements generally include:
Iii. Relationship between private SQL zone and shared SQL
Summary The above content shows that the information in the continuous data area is the memory used during this period after the cursor is opened and before the cursor is executed. All information that needs to be stored in the memory in this phase uses the memory of the persistent data zone. Therefore, in addition to the binding information and SQL statement mentioned above, there is also an important information used to share the address information associated with an SQL statement in the SQL area.
Why is the private SQL zone associated with the shared SQL zone because execution plans are stored in the shared SQL zone.
The following section is from the document:
Oracle allocates a private SQL zone for sessions that execute SQL statements. Each session that submits an SQL statement has a private SQL zone. Each user who submits the same SQL statement has its own private SQL zone, but they use the same shared SQL zone ). That is, multiple private SQL zones can be associated with the same shared SQL zone.
This official article clearly describes the relationship between the shared SQL zone and the private SQL zone. However, after reading this article, it is estimated that many people with the same qualifications as me will be confused. Let's take a look at Uncle TOM's clearer metaphor:
Shared SQL is like a DLL, a shared library.
An SQL statement is like a running application that points to and uses the shared library DLL. However, each application does not copy its own private binary copy, but they only share it.
An application is an SQL statement, and a DLL shared library is a shared SQL statement.
According to TOM, the execution plan contains only one copy of the related SQL information in the shared pool. The UGA of each session is still a pointer.
That is to say, the pointer to the corresponding DLL library (shared SQL) should be stored in the application (SQL). With this pointer, the private SQL zone should be linked with the shared SQL zone.
This pointer to the shared SQL area and the corresponding SQL statement text are the most important content in the continuity area.
When multiple private SQL zones share a shared SQL zone, the dynamic link library of the operating system is used.
4. How can a user process obtain query results?

Select * from table;
After the above statement is issued, how can the query result be returned to the user?
1.
First, locate the block in the Buffer cache. If the block Buffer cache of the Table does not exist, read part of the block from the disk to the Buffer cache. This is physical read. This process requires the assistance of PGA.
When reading a block from a disk, Oracle tries to read as many blocks as possible to the memory for each read. However, the number of read blocks is limited by multiple read parameters and partition sizes of hardware, OS, and Oracle.
If the Table block can be found in the Buffer cache, it will not be read from the disk. This is called logical read.

2.
Extract a record from the Buffer cache and submit it to the user immediately. Oracle does not copy this record to any location.

3.
Repeat Step 1. If the records that meet the user requirements in the current block in the Buffer cache are read, return to step 2.
In the preceding three steps, the information about the record to be read is recorded in the running time zone. We can also call it a "result set Pointer ".
The following is an example of a static cursor:
Declare

Cursor aa1 is select id from t1 where myid <= 10;

Mx number;
Begin

Open aa1; ------> when the static cursor is opened, the query is executed.

Dbms_output.put_line ('number of queried rows 1: '| aa1 % rowcount); ----> but reading has not started yet, so this Oracle does not know

---- The number of rows returned by the query. Therefore, 0 is displayed here.

For I in 1 .. 10 loop

Fetch aa1 into mx;
---- The user program needs to read the record and run the time zone to indicate reading the first record. The server process is responsible for finding the corresponding block in Buffer ---- cache. If the corresponding block is not in Buffer cache, it reads the corresponding block from the disk to ---- Buffer cache. Then retrieve the first line of record from the buffer cache and immediately pass the value to the user process. User ------- the process uploads the value to the specified variable, which is mx. After reading the first record, run ---- the time zone indicates that the record to be read has changed to the second record.

Dbms_output.put_line ('query result: '| mx );

End loop;

Dbms_output.put_line ('number of queried rows 2: '| aa1 % rowcount );
---- The above loop captures a total of 10 records, so the rowcount

---- The value is 10.

Close aa1;
End;
/
No matter how many rows will be returned by your query, the corresponding block will be read only when the query is executed and the records are captured. Oracle does not read all blocks in advance. It constructs a result set and then returns the queried records from the result set. This can be proved from two points. First, after the cursor is opened, Oracle cannot return the number of rows queried by the cursor. In the preceding example, rowcount is only known to Oracle if you have captured N rows. The original result set contains N rows. You do not crawl a row. The value of rowcount is 0.
In addition, we can use an example to prove that:
1. Release the following statement to test the usage of block T4_1 (many of the following views will be detailed in the Buffer cache Section ):
Scott @ MYTWO> select dbms_rowid.rowid_block_number (rowid) RID, min (rownum), max (rownum)
From t4_1 group by dbms_rowid.rowid_block_number (rowid );

Rid min (ROWNUM) MAX (ROWNUM)
--------------------------------

31508
1
38
-----> 31508 blocks occupied by 1 to 38 records

31509
39
75
-----> 31509 blocks occupied by 39 to 75 records

31510
76
112
-----> And so on

31511
113
150

31512
151
187

31513
188
225

31514
226
262
..................
2.
Check the number of existing T4_1 blocks in the Buffer cache.
Sys @ MYTWO & gt; select count (*) from x $ bh where obj = 7487;

COUNT (*)
----------

1
If T4_1 has many blocks in the Buffer cache, try to release them.
(You can insert or delete a large number of data into a table, for example, delete big_table where rownum <= 240000;
Or select a large table by index: select/* + index (Table Name) */* from table name where index column is not null ;)
3.
Execute the following PL/SQL blocks:
Declare

Cursor aa1 is select id from t4_1;

Mx number;
Begin

Open aa1;

For I in 1 .. 75 loop

Fetch aa1 into mx;

Dbms_output.put_line ('query result: '| mx );

End loop;

Dbms_output.put_line ('number of queried rows: '| aa1 % rowcount );

Close aa1;
End;
/
Table T4_1 has 2000 rows and 64 blocks. The Aa1 cursor Selects all its rows. However, the program only crawls 75 rows. We can see from the query results in step 1, that is, two blocks. Next we will query X $ BH again to see how many data blocks Oracle reads into the memory:
4.
Query X $ BH again:
Sys @ MYTWO & gt; select count (*) from x $ bh where obj = 7487;

COUNT (*)
----------

14
We can see that T4_1 has 64 blocks, but since we only capture some rows, only some of them are sent to the Buffer cache. This means that Oracle does not read all the blocks in advance, but "where to read and capture ".
But we only capture the first 75 rows. We should read only two blocks. Why should we read 14 blocks? This is of course because multiple read parameters: db_file_multiblock_read_count, which is not the content of Library cache. This part focuses on Library cache, such as the content of multiple read parameters, which will be discussed later. Here is a brief introduction. Here I set this multi-block read parameter to 16, that is to say, if possible, the Oracle reads 16 blocks to the Buffer cache. This parameter will be discussed in detail later. Here, only 16 blocks are not read, but 14 are read, probably because of the partition size. Run the following two queries:
Sys @ MYTWO> select EXTENT_ID, FILE_ID, BLOCK_ID from dba_extents where segment_name = 't4 _ 1 ';

EXTENT_ID
FILE_ID
BLOCK_ID
------------------------------

0
5
31505

1
5

31521

2
5
31537

3
5
31553
Sys @ MYTWO & gt; select FILE #, dbablk, state from x $ bh where obj = 7487;

FILE #
DBABLK
STATE
------------------------------

5
31507
1

5
31508
1

:
:
:

:
:
:

5
31520
1
14 rows have been selected.
We can see that the block number of Block 0 in T4_1 ranges from 31505 to 31520, while the block in the Buffer cache ranges from 31507 to 31520. It stops reading a zone.
Summary: Oracle does not pre-construct or cache results. Records are directly read from the buffer and transmitted to the user process. While the cursor records the information of the next row to be crawled when the row is crawled.
Oracle does not cache results. We can see from an example that the following statements are executed:
Scott @ MYTWO> set autotrace traceonly;
Scott @ MYTWO> select * from aa_1;
Unselected row
Execution Plan
----------------------------------------------------------

0
Select statement Optimizer = CHOOSE

1
0
Table access (FULL) OF 'aa _ 1'
Statistics
----------------------------------------------------------

0
Recursive cballs

0
Db block gets

3
Consistent gets

0
Physical reads

0
Redo size

215
Bytes sent via SQL * Net to client

372
Bytes encoded ed via SQL * Net from client

1
SQL * Net roundtrips to/from client

0
Sorts (memory)

0
Sorts (disk)

0
Rows processed
You can execute select * from aa_1 several times, and the obtained data should be the same.
From the above data, we can see that there are three logical reads. This indicates that Oracle has read the Buffer cache three times. The three logical reads actually read the field headers. This indicates that Oracle is re-starting a query, indicating that Oracle is preparing to re-start row capturing. This indicates that Oracle does not cache the result set. If the Oracle cache has a result set, you do not have to read the header information blocks in the Buffer cache and directly return the results based on the result set.
5. cursor
It is time to accurately define the cursor.
Source Document: A cursor is a handle or name for a private SQL area.
That is, the cursor is the handle (or name) of the private SQL zone ).
A cursor can be said to be a general term for various information in all private SQL zones. It does not actually exist. It only consists of components in all private SQL zones. A cursor is synonymous with a private SQL zone.
We can say that the user process executes its own SQL statement through the cursor, and then retrieves the information stored on the server and presents it to the user.
Vi. View of the cursor
Each cursor (Private SQL area) corresponds to an object in the Library cache. In addition, the handle of this object is locked by mode 1.
The V $ open_cursor view is provided by Oracle to view all current cursors. Observe the relationship between the cursor and the Library cache.
Example 10: Observe the relationship between V $ open_cursor and X $ KGLLK:
The v $ fixed_view_definition view defines all the dynamic performance views (V $ view) in Oracle. The following query shows the origins of V $ OPEN_CURSOR.
Select view_definition from v $ fixed_view_definition where view_name = 'v $ OPEN_CURSOR ';
Further query shows the connection between V $ OPEN_CURSOR and X $ KGLLK.
Through the relationship between V $ OPEN_CURSOR and X $ KGLLK, we can see that for Oracle, an opened cursor will surely have a corresponding handle with a LOCK of 1.
Except for V $ open_cursor, Oracle does not provide a dedicated view for displaying cursor information. Because a lot of information in the cursor is stored in PGA. The V $ Series Dynamic Performance views in Oracle are from SGA or control files. The information in PGA can be accessed only by the Service Process of the user session. Because it is not shared memory, it cannot be centrally managed by the background process like the X $ view.
While V $ open_cursor is for cursors, it uses the private SQL zone in PGA to share the SQL zone with SGA, and displays information in SGA.