Shulou(Shulou.com)06/01 Report--

Cursor literally translates to "cursor", which is the carrier of SQL parsing and execution in Oracle database. Oracle database is written in C language, and Cursor can be understood as a structure of C language (Structure).

There are two types of Cursor in the Oracle database: one is Shared Cursor; and the other is Session Cursor. This article first introduces Shared Cursor.

Shared Cursor in 1 Oracle.

1.1 the meaning of Shared Cursor

Shared Cursor refers to a kind of library cache object cached in the library cache. To put it bluntly, it refers to the library cache object corresponding to the SQL statement and anonymous PL/SQL statement cached in the library cache. Shared Cursor is one of dozens of library cache objects cached by Oralce in Library Cache. The value of the Namespace property of the name handle of the library cache object is CRSR (that is, the abbreviation for Cursor). The Shared Cursor stores information such as the SQL text of the target SQL, the parse tree, the object definition involved in the SQL, the type and length of binding variables used by the SQL, and the execution plan of the SQL.

The Shared Cursor in the Oracle database is subdivided into two types: Parent Cursor (parent cursor) and Child Cursor (child cursor). We can view the Parent Cursor and Chile Cursor currently cached in the library cache by querying the views V$SQLAREA and V$SQL respectively, where V$SQLAREA is used to view Parent Cursor,V$SQL and Child Cursor.

Parent Cursor and Child Cursor have the same structure (they are both cached in the library cache as the library cache object name handle, and the value of the Namespace attribute is CRSR). The difference between them is that the SQL text of the target SQL is stored in the property Name of the library cache object handle corresponding to its Parent Cursor (the Name property value of the library cache object name handle corresponding to Child Cursor is empty, which means that the corresponding Child Cursor can only be found through Parent Cursor) The parsing tree and execution plan of the SQL will be stored in Heap 6 of the library cache object handle corresponding to its Child Cursor, and the Oracle will store the library cache object name handle address of all Child Cursor subordinate to the Parent Cursor in the Chhild table of the Heap 0 of the Parent Cursor corresponding to the SQL (which means that the Oracle can sequentially access all Child Cursor subordinate to the Parent Cursor by accessing the Child table in Heap 0 of the Parent Cursor).

This structure of Parent Cursor and Child Cursor determines that in the Oracle database, any target SQL must correspond to two Shared Cursor at the same time, one of which is Parent Cursor, and the other is that Child Cursor,Parent Cursor stores the SQL text of the SQL, while the real reusable parse tree and execution plan of the SQL are stored in Child Cursor.

The result of the coexistence of Parent Cursor and Child Cursor designed by Oracle is that Oralce looks for a matching library cache object handle in the linked list of library cache object handles in the corresponding Hash Bucket according to the hash value of the SQL text of the target SQL, but the hash value corresponding to different SQL text may be the same, and the same SQL (the hash value at this time is naturally the same) may also have different resolution rights and execution plans. As you can imagine, if they are all in the library cache object handle chain list in the same Hash Bucket, then the length of the library cache object handle is not the optimal length (which means that it will increase the time and effort required for Oracle to search the library cache object handle list from beginning to end), in order to minimize the length of the library cache object handle chain list in the corresponding Hash Bucket. Oracle designed this nested structure where Parent Cursor and Child Cursor coexist.

Let's look at an example of Parent Cursor and Child Cursor:

Sys@MYDB > conn zx/zxConnected.zx@MYDB > select empno,ename from emp; EMPNO ENAME- 7369 SMITH. Omit the partial output of 14 rows selected.

When a SQL is executed for the first time, the Oracle generates both a Parent Cursor and a Child Cursor. The above SQL is executed for the first time, so now Oracle should generate both a Parent Cursor and a Child Cursor. Verify using the following statement:

Select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select empno,ename%'

Notice that the original target SQL has only one matching record in V$SQLAREA, and that the column VERSION_COUNT of this record has a value of 1 (VERSION_COUNT represents the number of all Child Cursor owned by this Parent Cursor), indicating that Oracle did produce a Parent Cursor and a Child Cursor when executing the target SQL.

The SQL_ID corresponding to the above SQL is "78bd3uh5a08av". With this SQL_ID, you can query the information of all the Child Cursor corresponding to the SQL in the V$SQL:

Zx@MYDB > col sql_text for a50zx@MYDB > select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select empno,ename%' SQL_TEXT SQL_ID VERSION_COUNT -- select empno Ename from emp 78bd3uh5a08av 1

Notice that the target SQL_ID has only one matching record in the V$SQL, and that the value of the CHILD_NUMBER for this record is 0 (CHILD_NUMBER represents the child cursor number corresponding to a Child Cursor), indicating that the Oracle did produce only one Child Cursor with the child cursor number 0 when executing the original target SQL.

Change the table name in the original target SQL from lowercase to uppercase EMP before executing:

Zx@MYDB > select empno,ename from EMP; EMPNO ENAME- 7369 SMITH. Omit the partial output of 14 rows selected.

Oracle will find the matching Parent Cursor in the corresponding Hash Bucket according to the hash value of the SQL text of the target SQL, and the hash operation is case-sensitive, so when we execute the target SQL after the above rewriting, it is very likely that the Hash Bucket corresponding to the uppercase EMP and the Hash Bucket corresponding to the lowercase emp are not the same Hash Bucket (even the same Hash Bucket does not matter. Because Oracle also continues to compare the Name attribute value of the object handle to the library where Parent Cursor is located, the name value of lowercase Parent Cursor is "select empno,ename from emp" and the name value of uppercase EMP pair is "select empno,ename from EMP", which is obviously not equal. In other words, the Parent Cursor corresponding to the lowercase emp is not the Parent Cursor that the uppercase EMP is looking for, and the two cannot be shared, so the Oracle will definitely generate a new pair of Parent Cursor and Child Cursor at this time.

Let's verify this:

Zx@MYDB > select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select empno,ename%' SQL_TEXT SQL_ID VERSION_COUNT -- select empno Ename from emp 78bd3uh5a08av 1select empno,ename from EMP 53j2db788tnx9 1zx@MYDB > select plan_hash_value,child_number from v$sql where sql_id='53j2db788tnx9' PLAN_HASH_VALUE CHILD_NUMBER--3956160932 0

As can be seen from the above results, Oracle does generate a new Parent Cursor and a Child Cursor for the target SQL (uppercase EMP) corresponding to the uppercase EMP.

Now construct an instance with different Child Cursor under the same Parent Cursor:

Log in as the scott user and execute the target SQL for the lowercase emp again:

Zx@MYDB > conn scott/tigerConnected.scott@MYDB > select empno,ename from emp; EMPNO ENAME- 7369 SMITH. Omit the partial output of 14 rows selected.

Oracle looks for a matching Parent Cursor in the corresponding Hash Bucket according to the hash value of the SQL text of the target SQL, and after finding the matching Parent Cursor, it has to traverse all the Child Cursor subordinate to the Parent Cursor (because the resolution weight and execution plan that can be reused are stored in the Child Cursor).

For the above SQL (lowercase emp), because the same SQL text has been executed under ZX users before, and the corresponding Parent Cursor and Child Cursor have been generated in Library Cache, so here Oracle can definitely find matching records when looking for matching Parent Cursor in Library Cache according to the hash value of SQL text of the above SQL. However, when traversing all the Child Cursor subordinate to the Parent Cursor, Oracle will find that the resolution right and execution plan stored in the corresponding Child Cursor cannot be reused at this time, because the resolution tree and execution plan stored in the Child Cursor are aimed at the table EMP under the ZX user, and the above-mentioned SQL is aimed at the table EMP under the SCOTT user with the same name. The target table to be queried is not the same table at all, so the resolution right and the execution plan cannot be shared. This means that Oracle has to parse the above SQL from scratch, store the parsed tree and execution plan in a newly generated Child Cursor, and then hang the Child Cursor under the above Parent Cursor (that is, add the newly generated Child Cursor in the library cache object handle address to the Child table of Heap 0 of the above Parent Cursor). That is to say, once the above SQL is executed, there will be two Child Cursor under the Parent Cursor corresponding to the SQL. One Child Cursor stores the parsing tree and execution plan of the following table EMP for ZX users, and the other Child Cursor stores the parsing tree and execution plan of the table EMP with the same name under the SCOTT user.

Verify using the following statement:

Scott@MYDB > select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select empno,ename%' SQL_TEXT SQL_ID VERSION_COUNT -- select empno Ename from emp 78bd3uh5a08av 2select empno,ename from EMP 53j2db788tnx9 1

Notice that the column VERSION_COUNT of the matching record in the above SQL (lowercase emp) V$SQLAREA has a value of 2, indicating that Oracle did produce one Parent Cursor and two Child Cursor when executing the SQL.

Use the following statement to query the information of the Child Cursor corresponding to the above SQL:

Scott@MYDB > select plan_hash_value,child_number from v$sql where sql_id='78bd3uh5a08av';PLAN_HASH_VALUE CHILD_NUMBER--3956160932 0 3956160932 1

Notice that the above SQL has two matching records in V$SQL, and the CHILD_NUMBER values of these two records are 0 and 1, respectively, indicating that Oracle did produce two Child Cursor when executing the above SQL, and their sub-cursor numbers are 0 and 1, respectively.

Oracle actually performs the following steps in sequence when parsing the target SQL to find a matching Shared Cursor in the library cache:

(1) go to the library cache to find the matching Hash Bucket according to the hash value of the SQL text of the target SQL. Note that, more precisely, the hash operation here is based on the values of the attributes Name and Namespace of the corresponding library cache object handle, but for the SQL statement, the value of the attribute Name of the corresponding library cache object handle is the SQL text of the SQL, and the value of the attribute Namespace is the constant "CRSR", so it can be approximately regarded as hashing only based on the SQL text of the target SQL.

(2) then look for the matching Parent Cursor in the database cache object list of the matching Hash Bucket, of course, in the process of finding the matching Parent Cursor, it will certainly compare the SQL text of the target SQL (because the hash value calculated by different SQL text may be the same).

(3) step 2 if a matching Parent Cursor is found, the Oracle then traverses all the Child Cursor subordinate to that Parent Cursor to find a matching Child Cursor.

(4) if no matching Parent Cursor is found in step 2, it also means that there is no parsing tree and execution plan that can be shared at this time. Oracle will parse the above target SQL from scratch, generate a new Parent Cursor and a Child Cursor, and hang them in the corresponding Hash Bucket.

(5) step 3 if a matching Child Cursor is found, the Oracle will directly reuse the parse tree and execution plan stored in the Child Cursor instead of parsing from scratch.

(6) if no matching Child Cursor is found in step 3, it means that there is no parsing tree and execution plan that can be shared. Then Oracle will parse the above target SQL from scratch, generate a new Child Cursor, and hang the Child Cursor under the corresponding Parent Cursor.

1.2 hard parsing

Hard parsing (Hard Parse) means that when Oracle executes the target SQL, it cannot find a reusable parsing tree and execution plan in the library cache, but has to parse the target SQL and generate the corresponding Parent Cursor and Child Cursor from scratch.

There are actually two types of hard parsing, one is that the matching Parent Cursor cannot be found in the library cache, and the Oracle parses the target SQL from scratch, generates a new Parent Cursor and Child Cursor, and hangs them in the corresponding Hash Bucket; the other is that a matching Parent Cursor is found but no matching Child Cursor is found, and the Oracle parses the target SQL from scratch, generates a new Child Cursor, and hangs the Child Cursor under the corresponding Parent Cursor.

Hard parsing is very bad, and its harmfulness is mainly reflected in the following aspects:

Hard parsing can lead to contention for Shared Pool Latch. No matter what type of hard parsing it is, you need to generate at least a new Child Cursor, load the parse tree and execution plan of the target SQL into that Child Cursor, and then store the Child Cursor in the library cache. This means that Oracle must allocate a memory area in Shared Pool to store the above Child Cursor, and the action of allocating memory in Shared Pool is to hold Shared Pool Latch (one of the functions of Latch in Oracle database is to protect the allocation of shared memory), so if there is a certain amount of concurrent hard parsing, it may lead to Shared Pool Latch contention, and once a large number of Shared Pool Latch contention occurs The performance and scalability of the system can be seriously affected (often characterized by a high occupancy rate of CPU, close to 100%).

Hard parsing can lead to contention between library cache-related Latch (such as Library Cache Latch) and Mutex. No matter what type of hard parsing it is, you need to scan the library cache object handle linked list in the relevant Hash Bucket, and the action of scanning the library cache object handle linked list is to hold Library Cache Latch (another function of Latch in Oracle database is to share the concurrent access control of SGA memory structure), so if there is a certain amount of concurrent hard parsing, it may lead to Library Cache Latch contention. Like Shared Pool Latch contention, once a large number of Library Cache Latch contention occurs, the performance and scalability of the system will be seriously affected. Starting with 11gR1, Oracle replaces library cache-related Latch with Mutex, so in Oracle 11gR1 and subsequent versions, library cache-related Latch emergencies will no longer exist and will be replaced by Mutex contention (Mutex can be simply understood as a lightweight Latch,Mutex that is also used to share SGA memory results concurrency access control), so Oracle introduces a series of new wait events to describe this Mutex contention. For example: Cursor: pin S, Cursor: pin X, Cursor: pin S wait on X, Cursor:mutex S, Cursor:mutex X, Library cache:mutex X and so on.

It is also important to note that when Oracle does hard parsing, the holding process of Shared Pool Latch and Library Cache Latch is roughly as follows: Oracle first holds Library Cache Latch, scans the handle list of library cache objects in the relevant Hash Bucket in the library cache to see if there is a matching Parent Cursor, and then releases the Library Cache Latch (the reason is that no matching parent Cursor was found). Next is the second half of hard parsing, which first holds the Library Cache Latch, and then holds the Shared Pool Latch without releasing the Library Cache Latch to apply for memory allocation from the Shared Pool. After a successful application, the Shared Pool Latch will be released, and finally the Library Cache Latch will be released. For more information, please see http://www.laoxiong.net/shared-pool-latch-and-library-cache-latch.html.

For OLTP-type systems, hard parsing is the root of all evil.

1.3 soft parsing

Soft parsing (Soft Parse) means that when Oracle executes the target SQL, it finds the matching Parent Cursor and Child Cursor in Library Cache, and reuses the parsing tree and execution plan stored in Child Cursor directly, without having to start parsing from scratch.

Compared with hard parsing, the advantages of soft parsing are mainly shown in the following aspects:

Soft parsing does not cause contention for Shared Pool Latch. Because soft parsing can find matching Parent Cursor and Child Cursor in the library cache, it does not need to generate new Parent Cursor and Child Cursor. This means that soft parsing does not need to hold Shared Pool Latch at all to apply for a shared memory area in Shared Pool. Since there is no need to hold Shared Pool Latch, there will be no Shared Pool Latch contention, that is, the system performance and scalability problems caused by Shared Pool Latch contention do not exist for soft parsing.

Although soft parsing may also lead to contention between library cache-related Latch (such as Library Cache Latch) and Mutex, soft parsing holds library cache-related Latch less often, and soft parsing holds some Latch (such as Library Cache Latch) for a shorter time than hard parsing, which means that even if there is a contention for library cache-related Latch, the contention of soft parsing is not as serious as hard parsing. That is, the system performance and scalability problems caused by the contention of Latch and Mutex related to library cache are much less for soft parsing than for hard parsing.

Based on the above two reasons, if OLTP-type systems can widely use soft parsing when performing target SQL, the performance and scalability of the system will be significantly improved compared with all hard parsing, and the system resources consumed when executing the target SQL (mainly reflected in CPU) will also be significantly reduced.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.