Hard parsing and soft parsing of Oracle SQL

We all know that every SQL statement in Oracle needs to be parsed before it executes, where it is divided into soft parsing and hard parsing. There are two types of SQL statements in Oracle, one for DDL statements (data definition language), and they are never shared, that is, each execution requires hard parsing. There is also a DML statement (data manipulation language) that chooses either hard parsing or soft parsing depending on the situation.

Dml:insert,update,delete,select

Ddl:create,drop,alter

I. SQL parsing process

Oracle will process this SQL in a few steps:

1, grammar check (syntax check): Check the spelling of this SQL is syntax.

2, semantic check (semantic check): such as checking the existence of the Access object in the SQL statement and whether the user has the appropriate permissions.

3. Parsing SQL statements (prase): Parsing SQL using internal algorithms, generating parse tree and execution plan (execution plan).

4. Execute SQL, return result (execute and return)

Two Detailed analysis Process

2.1 Syntax Detection

Determine whether the syntax of an SQL statement conforms to the SQL specification, such as execution:

Sql> Selet * from EMP;

We can see that because a "C" is missing from the SELECT keyword, this statement is not able to pass the syntax validation steps.

2.2 Semantic Checking

The second step in parsing a syntactically correct SQL statement is to determine whether the tables and columns accessed by the SQL statement are accurate? Does the user have permission to access or change the corresponding table or column? such as the following statement:

Sql> select * from EMP;

SELECT * FROM emp

*

ERROR at line 1:

Ora-00942:table or view does not exist

Because the query user does not have an EMP object available for access, the SQL statement cannot pass the semantic check.

2.3 Parsing (parse)

The 2.3.1 parse is divided into three main types:

1. Hard Parse

2. Soft Parse (soft parse)

3. Soft Soft Parse (as if some of the data did not include this in it)

Hard parse: The above mentioned SQL is completely re-parsed from the beginning (when it is not found in the shared pool), there are a total of 5 execution steps:

1: Syntax analysis

2: Permission and object checking

3: Check in the shared pool if there is exactly the same before the fully parsed well. If present, skip 4 and 5 directly, run SQL, and this is soft parse.

4: Select execution Plan

5: Generate execution plan

Note: Creating a parse tree, generating an execution plan is a costly action for SQL execution, so you should try to avoid hard parsing and use soft parsing as much as possible. This is the reason why, in many projects, advocates for developing designers to work with the same code to keep the code consistent and to use the binding variable more often in the program.

Soft Parse: If you find the exact same SQL parsed result in the shared pool, you will skip the next two steps in hard Parse.

Soft Soft Parse: In fact, after setting the Session_cursor_cache parameter, the cursor is directly cache in the current session of the PGA, in the parsing only need to its syntax analysis, After the permission object analysis can go to the PGA to find, if found the exact same cursor, you can go directly to the results, it is the implementation of Soft Soft Parse.

The steps of 2.3.2 parsing can be divided into two steps:

1) Verify that the SQL statement is fully consistent.

In this step, Oracle will use the hash function to calculate the hash value for the incoming SQL statement, and then compare it with the hash value of the existing statement in the shared pool to see if one by one corresponds. The hash value of the SQL statement in the existing database can be obtained by accessing the Hash_value column in the V$sql, V$sqlarea, V$sqltext and other data dictionaries.

If the hash value of the SQL statement is consistent, then Oracle will actually need to re-detect the semantics of the SQL statement to determine whether it is consistent. So why does Oracle need to detect the statement text again? is not the hash value of the SQL statement already in the corresponding? In fact, the hash value of the SQL statement already corresponds, and does not indicate that the two SQL statements can already be shared.

For example: If the user sys has its own table EMP, he will execute the query statement: SELECT * from EMP; The user system also has an EMP table, which also queries the SELECT * from EMP, so that their two statements are identical on the text, their hash values will be the same, but because the related tables involved in the query are not the same, they are not actually shared.

Sql> Conn/as SYSDBA

is connected.

Sql> Show User

USER is "SYS"

Sql> CREATE TABLE EMP (x int);

The table is created.

Sql> select * from EMP;

Row not selected

Sql> Conn System/admin;

is connected.

Sql> CREATE TABLE EMP (x int);

The table is created.

Sql> select * from EMP;

Row not selected

Sql> Select Address,hash_value, executions, sql_text from V$sql where Upper (sql_text) like ' select * from emp% ';

ADDRESS hash_value Executions Sql_text

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

2769ae64    1745700775     1          select * from emp                                                                                                                                

2769ae64 1745700775 1 SELECT * from emp

2 rows selected.

From the results can be seen that the statement text and hash value of the 2 queries are the same, but because the object of the query is not shared, the statements of different situations need to be hard-resolved. Therefore, when examining shared pool common SQL statements, it is necessary to do so depending on the situation.

You can further query v$sql_shared_cursor to learn why SQL cannot be shared:

Sql>select Address,auth_check_mismatch,translation_mismatch,optimizer_mismatch

From V$sql_shared_cursor where to address in (

Select address from V$sql where Upper (sql_text) like ' SELECT * from emp% ')

ADDRESS A T O

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

2769ae64 n n N

2769ae64 y y N

Translation_mismatch indicates that the data objects involved in the SQL cursor are different;

Auth_check_mismatch indicates that the conversion to the same SQL statement is mismatched.

Optimizer_mismatch indicates that the optimizer environment for the session is different.

2) Verify that the SQL statement execution environment is the same

For example, the same SQL statement, a query session with/*+ First_rows */hint, another user plus/*+ all_rows/hint, they will produce a different execution plan, although they are querying the same data.

If the SQL statements are consistent after the above checks, then the execution Plan and optimization scheme of the original SQL statement is reused, which is what we usually call soft parsing. If the SQL statement does not find the same copy, then it needs to be hard-parsed.

Oracle then queries the corresponding data object for statistics based on the SQL statement submitted. If there is statistical information, then the CBO will use these statistics to generate all possible execution plans (possibly up to thousands) and the corresponding cost, and ultimately choose the lowest cost execution plan. If the queried data object has no statistics, the corresponding execution plan is selected according to the default rule of Rbo. This step is also the most resource-intensive in parsing, so we should try our best to avoid the production of hard parsing. Now that the resolved steps are complete, Oracle executes the SQL statement and extracts the corresponding data based on the execution plan that was generated by the resolution.

2.4 Execute SQL, return result (execute and return)

Three. Binding variables

The use of BIND Var can improve performance primarily because it saves time by avoiding unnecessary hard parse and saves a lot of CPU resources.

When a client submits a SQL to Oracle, Oracle first parses it, and then submits the parse result to the optimizer (optimiser) for optimization and obtains the optimal query Plan that Oracle considers Then follow this optimal plan to execute the SQL statement (which is, of course, a few steps if only soft parsing is required).

However, when Oracle receives a client-submitted SQL, it will first look in the shared pool to find out if there is a SQL that is exactly the same as the one that was just received (note that this is exactly the same, requiring exactly the same character level on the statement, Also requires that the objects involved must be exactly the same). When the parser is found to have the same later, it will no longer parse the new SQL directly before using the results. This saves the parsing time and the CPU resources consumed during parsing. In particular, the amount of short SQL that runs in OLTP is more noticeable. Because a two of SQL time may not have a lot of feeling, but the equivalent of large after the more obvious feeling.

Hard parsing and soft parsing of Oracle SQL