Procedures for parsing SQL statements in Oracle

This process is called the parsing process in order to translate the SQL text written by the user into an Oracle-aware and executable statement. Parsing is divided into hard parsing and soft parsing. A SQL statement must be hard resolved the first time it is executed.

When a client issues an SQL statement (or can be a stored procedure or an anonymous pl/sql block) into the shared pool (note that we know from the foregoing that Oracle is not called SQL statements for these SQL, but is called cursors.) Because Oracle needs a lot of relevant ancillary information when it processes SQL, which is a cursor with the SQL statement, Oracle first converts the SQL text into an ASCII value and then calculates its hash value (Hash_value) based on the hash function. The corresponding bucket is found in the library cache according to the computed hash value, and then the SQL statement is compared in bucket. If it does not exist, you need to get the shared pool latch as we described earlier, then find an available chunk on the available chunk linked list (that is, bucket) in the shared pool, and then release the shared pool latch. After obtaining the chunk, this piece of chunk can be considered to enter the library cache. Next, perform the hard parsing process. Hard parsing includes the following steps.

Check the SQL statements to see if there are any grammatical errors. For example, there is no write from, select spelling errors, and so on. If there are grammatical errors, exit the parsing process. Verify that the objects and columns involved in the SQL statement are present in the data dictionary. If it does not exist, exit the parsing process. This process will load the dictionary cache. Converts an object to name conversion. such as translating the same nouns into actual objects. For example, in select * from T, T is a word with the same name, pointing to Hr.t1, so Oracle converts T to HR.T1. If the conversion fails, the parsing process exits. Check that the user who issued the SQL statement has permission to access the object referenced in the SQL statement. If there is no permission, the parsing process exits.

Create an optimal execution plan through the optimizer. This process calculates the optimal execution plan based on the statistical information of the objects recorded in the data dictionary. This step involves a large number of mathematical operations, is the most CPU-intensive resources. Loads the execution plan, SQL text, etc. generated by the cursor into the library cache heap. In the process of hard parsing, the process will always hold the library cache latch until the end of hard parsing. After hard parsing ends, two cursors are generated for the SQL statement, one is the parent cursor and the other is a child cursor. The parent cursor contains two main types of information: SQL text and optimization objectives (optimizer goal). The parent cursor is locked the first time it is opened, until all other sessions have closed the cursor before being unlocked. When the parent cursor is locked, it cannot be exchanged for the library cache, only after the unlock can be exchanged out of the library cache. When the parent cursor is swapped out of memory, all child cursors corresponding to the parent cursor are also swapped out of the library cache. A child cursor includes all information about a cursor, such as a specific execution plan, binding variables, and so on. A child cursor can be swapped out of the library cache at any time, and when a child cursor is swapped out of the library cache, Oracle can reconstruct a child cursor using the information of the parent cursor, a process called reload. You can use the following method to determine the reload ratio:

Select 100*sum (reloads)/sum (Pins) reload_ratio from V$librarycache;

A parent cursor can correspond to multiple child cursors. The specific number of child cursors can be reflected from the Version_count field in the View V$sqlarea. Each specific child cursor is reflected in the view V$sql. When the value of a specific binding variable is significantly different from the value of the last bound variable (for example, the last executed binding variable has a length of 6 digits, this time, when the value of the binding variable is 200 bits long, or when the SQL statement is exactly the same, but the referenced table belongs to a different user, a new child cursor will be created. If the SQL statement is found in the bucket, the SQL statement is previously run, and then a soft resolution is performed. Soft parsing is relative to hard parsing, if the parsing process, you can remove one or more of the steps from the hard parsing, such an analysis is a soft resolution. Soft parsing is divided into the following three types.

The first is that a session-issued SQL statement is consistent with the SQL statements issued by other sessions in the library cache. At this point, the parsing process can be removed from the and in hard parsing, but still need to be in the hard parsing process,,, that is, table name and column name check, name conversion and permission check.

The second is that a session-issued SQL statement is a previously executed SQL statement issued before the session. In this case, you can remove the,,, and these four steps in hard parsing, but still have permission checks because the session user's permissions may be changed by Grant.

The third is when the initialization parameter session_cached_cursors is set, when a session executes the same SQL for the third time, the cursor information of the SQL statement is transferred to the PGA of that session. In this way, when the session executes the same SQL statement later, the execution plan is removed directly from the PGA to jump through all the steps that are hard to parse. In this case, it's the most efficient way to parse, but it consumes a lot of memory.

We give an example to illustrate the process of parsing an SQL statement. In this test, the binding variable name is the same, but the variable type is not the same, and the resolution occurs. as shown below.

First, execute the following command to empty all the SQL statements in the shared pool:

Sql> alter system flush Shared_pool;

Then, after defining a numeric binding variable and assigning a numeric value to the bound variable, execute the specific query statement.

sql> variable v_obj_id number;

sql> exec:v_obj_id: = 4474;

Sql> Select Object_id,object_name from Sharedpool_test

where object_id=:v_obj_id;

object_id object_name

----------    ---------------------------

4474 Aggxmlimp

Next, you define a character-binding variable with the same variable name as before, assigning a character value to the bound variable, and executing the same query:

sql> variable v_obj_id varchar2 (10);

sql> exec:v_obj_id: = ' 4474 ';

Sql> Select Object_id,object_name from Sharedpool_test

where object_id=:v_obj_id;

object_id object_name

----------    ---------------------------

4474 Aggxmlimp

We then went to the view V$sqlarea to find information about the SQL's parent cursor, and went to the view V$sql to find information about all of the SQL's child cursors.

Sql> Select Sql_text,version_count from V$sqlarea where

Sql_text like '%sharedpool_test% ';

Sql_text

Version_count

-------------------------------------------------------

Select Object_id,object_name from Sharedpool_test where

OBJECT_ID=:V_OBJ_ID 2

Sql> Select sql_text,child_address,address from V$sql

where Sql_text like '%sharedpool_test% ';

Sql_text

Child_address Address

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/