Oracle SQL hard parsing and soft Parsing

We all know thatOracleEach entrySQLThe statement must be parsed before execution, which is divided into soft parsing and hard parsing. InOracleThere are two typesSQLStatement.DDLStatement(Data Definition Language)They are never shared, that is, each execution requires hard parsing. Another type isDMLStatement(Data manipulation language)They will choose either hard resolution or soft resolution based on the situation.

 

DML: INSERT, UPDATE, DELETE, SELECT

DDL: CREATE, DROP, ALTER

 

 

I. SQLParsing process

 

OracleThisSQLThe processing steps are as follows:

 1, Syntax check(Syntax check):Check thisSQLWhether the spelling is a syntax.

2Semantic check(Semantic check):Such as checkSQLWhether the access object exists in the statement and whether the user has the corresponding permissions.

3, PairSQLStatement Parsing(Prase):Using Internal AlgorithmsSQLParse to generate a resolution tree(Parse tree)And execution plan(Execution plan).

4, ExecutionSQL, Return results(Execute and return)

 

 

II.Detailed explanation of the parsing process

 

2.1Syntax Detection

JudgeSQLStatement syntaxSQLSuch as execution:

SQL> selet * from emp;

We can see thatSelectThe keyword"C", This statement cannot pass the syntax test steps.

 

 

2.2Semantic check

Correct syntaxSQLThe second step in parsing a statement is to determineSQLIs the table and column accessed by the statement 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 accessEmpObject, soSQLThe statement cannot pass the semantic check.

 

 

2.3Analysis(Parse)

 

2.3.1ParseThere are three main types:

1,Hard Parse(Hard Parsing)

2,Soft Parse(Soft resolution)

3,Soft Parse(It seems that this is not included in some documents.)

 

Hard Parse: IsSQLCompletely re-Parse from scratch(WhenShared PoolThis operation will be performed if it cannot be found.), A total5Steps:

1: Syntax analysis

2: Permission and object check

3: Check whether there are identical ones in the shared pool..If yes, skip4And5, RunSQL,CalculateSoft parse.

4: Select execution plan

5: Generate execution plan

 

Note:Create a resolution tree and generate an execution planSQLExecution is expensive, so we should try our best to avoid hard parsing and try to use soft parsing. In many projects, this is why developers strive to maintain code consistency for code with the same functions and to use variable binding in programs.

 

Soft Parse: IfShared PoolAnd found exactly the sameSQLThe parsed result is skipped.Hard Parse.

 

 Soft Parse: In fact, when you setSession_cursor_cacheAfter this parameter,CursorDirectlyCacheIn the currentSessionOfPGAWhen parsing, you only need to analyze its syntax and permission object, and then you can goPGAIf the sameCursorYou can directly get the result, that is, the implementation.Soft Parse. 

1 2 Next Page