SQL parsing steps, hard parsing and soft Parsing

We all know that every SQL statement in Oracle needs to be parsed before execution, which is divided into soft parsing and hard parsing. There are two types of SQL statements in Oracle: DDL statements (Data Definition Language), which are never shared, that is, hard Parsing is required for each execution. Another type is DML statements (data manipulation language). They choose either hard parsing or soft parsing based on the situation.

DML: insert, update, delete, select

DDL: Create, drop, alter

SQL statement parsing steps. After an SQL statement is passed from a client process to a server process, perform the following steps:
1. Search for existing SQL statement copies in the Shared Pool
2. Verify that the SQL statement syntax is correct.
3. Execute data dictionary search to verify the definition of tables and columns.
4. Obtain the analysis lock of the object so that the definition of the object does not change during the statement analysis.
5. Check the user's permission to access the referenced solution object.
6. Determine the statement execution plan.
6. Load statements and execution plans into the shared SQL Zone

Originally, I think hard Parsing is the above steps. Compared with hard parsing, the soft parsing step is to find a copy of the existing SQL statement in the first step above, you only need to verify whether the user has the permission to execute it, so that the above steps are omitted, performance overhead is very small for hard parsing.

Later, I realized that the above real parsing steps were not like this.

In fact, the SQL statement parsing steps in Oracle are as follows:
1. syntax check. Determine whether the syntax of an SQL statement complies with the SQL specifications, such as SQL> selet * from EMP. We can see that the select keyword is missing a "C ", this statement cannot pass the syntax test.

2. semantic check. The second step of parsing a SQL statement with correct syntax is to determine whether the table and column accessed by the SQL statement are accurate? Does the user have the permission to access or change the corresponding table or column?
For example, 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 for access, the SQL statement cannot pass the semantic check.

3. Check whether the same statement exists in the shared pool. If the executed SQL statement already has the same copy in the shared pool, the SQL statement will be soft resolved, that is, the execution plan and optimization scheme of the resolved statement can be reused, we can ignore the resource-consuming steps in the statement parsing process, which is why we have always stressed that hard parsing should be avoided.

This step can be divided into two steps:
(1) Verify that SQL statements are completely consistent.
In this step, Oracle will calculate the hash value of the passed SQL statement using the hash function, and compare it with the hash value of the existing statement in the shared pool to see if it is one-to-one. The hash value of SQL statements in the existing database can be obtained by accessing the hash_value column in the data dictionary such as V $ SQL, V $ sqlarea, and V $ sqltext.
If the hash values of SQL statements are consistent, Oracle needs to re-check the semantics of SQL statements to determine whether the values are consistent. So why does Oracle need to check the statement text again? Which of the following statements does not correspond to the hash value of the SQL statement? In fact, even if the hash value of the SQL statement has been matched, it cannot be said that these two SQL statements can be shared.

First, let's refer to the following example:
If user a has his own table EMP, he needs to execute the query statement: Select * from EMP; user B also has an EMP table, and also needs to query select * from EMP; in this way, their two statements are identical in the text, and their hash values are the same. However, they cannot be shared because the tables involved in the query are different. If user C needs to query the same statement again at this time, the table He queries is a public synonym under Scott, and Scott also queries the same EMP table. What is the situation?

Code:
--------------------------------------------------------------------------------
Code:

SQL> connect a/aConnected.SQL> create table emp ( x int );Table created.SQL> select * from emp;no rows selectedSQL> connect b/bConnected.SQL> create table emp ( x int );Table created.SQL> select * from emp;no rows selectedSQL> conn scott/tigerConnected.SQL> select * from emp;SQL> conn c/cConnected.SQL> select * from emp;SQL> conn/as sysdbaConnected.SQL> select address,hash_value, executions, sql_text  2    from v$sql  3   where upper(sql_text) like 'SELECT * FROM EMP%'  4  /ADDRESS  HASH_VALUE EXECUTIONS SQL_TEXT-------- ---------- ---------- ------------------------78B89E9C 3011704998       select * from emp78B89E9C 3011704998       1 select * from emp78B89E9C 3011704998       2 select * from emp...

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

We can see that the statement text and hash values of these four queries are the same, but because the query objects are different, only the following two statements can be shared, statements in different situations still need to be hard resolved. Therefore, when checking the Shared Pool's common SQL statements, it depends on the specific situation.

We can further query v $ SQL _shared_cursor to find out why SQL cannot be shared:

Code:
--------------------------------------------------------------------------------
Code:

SQL> select kglhdpar, address,  2         auth_check_mismatch, translation_mismatch  3    from v$sql_shared_cursor  4   where kglhdpar in  5   ( select address  6       from v$sql  7      where upper(sql_text) like 'SELECT * FROM EMP%' )  8  /KGLHDPAR ADDRESS  A T-------- -------- - -78B89E9C 786C9D78 N N78B89E9C 786AC810 Y Y78B89E9C 786A11A4 Y Y

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

Translation_mismatch indicates that the data objects involved in the SQL cursor are different; auth_check_mismatch indicates that the conversion of the same SQL statement does not match.

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

For example, for the same SQL statement, a query session is added with the/* + first_rows */hint, and another user is added with the/* + all_rows */hint, they will generate different execution plans, even though they query the same data. The following is an example to illustrate the impact of the SQL Execution Environment on Parsing. We can view the impact on the execution of the same SQL statement by changing the workarea_size_policy of the session:

Code:
--------------------------------------------------------------------------------
Code:

SQL> alter system flush shared_pool;System altered.SQL> show parameter workarea_size_policyNAME                                 TYPE        VALUE------------------------------------ ----------- --------------workarea_size_policy                 string      AUTOSQL> select count(*) from t;  COUNT(*)----------      5736SQL> alter session set workarea_size_policy=manual;Session altered.SQL> select count(*) from t;  COUNT(*)----------      5736SQL> select sql_text, child_number, hash_value, address  2    from v$sql  3   where upper(sql_text) = 'SELECT COUNT(*) FROM T'  4  /SQL_TEXT                       CHILD_NUMBER HASH_VALUE ADDRESS------------------------------ ------------ ---------- --------select count(*) from t                    0 2199322426 78717328select count(*) from t                    1 2199322426 78717328...

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

We can see that even the same SQL statement cannot be shared because workarea_size_policy of different sessions is set differently. By further querying v $ SQL _shared_cursor, we can find that the two session optimizer environments are different:

Code:
--------------------------------------------------------------------------------
Code:

SQL> select optimizer_mismatch  2    from v$sql_shared_cursor  3   where kglhdpar in  4   ( select address  5       from v$sql  6      where upper(sql_text) = 'SELECT COUNT(*) FROM T' );O-NY...

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

After checking the preceding steps, if the SQL statements are consistent, the execution plan and optimization scheme of the original SQL statement will be reused, which is also called soft parsing. If the SQL statement does not find the same copy, you need to perform hard parsing.
4. Oracle queries the data object for statistics based on the submitted SQL statement. If statistics are available, the CBO will use these statistics to generate all possible execution plans (up to thousands) and corresponding cost, and finally select the execution plan with the lowest cost. If no statistical information is available for the queried data objects, select an execution plan according to the default RBO rule. This step is also the most resource-consuming in parsing, so we should try our best to avoid hard parsing. So far, all the parsing steps have been completed. Oracle will execute SQL statements and extract the corresponding data according to the execution plan generated by the parsing.