What are the stages of SQL Statment Processing ?
Assume that connection to database is established and a SQL Statement is executed.
Source : http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/img/cncpt250.gif
- SQL Statement Parsing
- Application makes the call to parse the SQL Statement by issuing the statement.
- This call opens a cursor which is also known as HANDLE for session specific private SQL area that holds a parsed SQL Statement and other processing information.
- Cursor and Private SQL area are in PGA.
- During parsing SQL Statement is broken into pieces of data structure that can be processed by other routines. It performs the following checks.
- Syntax Check
- Check for the validity of the syntax.
- If there is an error in the syntax check, SQL Statement fails and error is returned.
- Semantic Check
- After syntax check is passed it verifies whether the statement referring to tables (objects) and columns exist
- Note : An object might exist in the database but the user executing the SQL might not have permissions to access/execute that object, semantic check will fail.
- If there is an error in the semantic check, SQL Statement fails and error is returned.
- Shared Pool Check
Source : http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/img/cncpt251.gif
- Once SQL Statement passes above two checks (Syntax and Semantic) a hash value of every SQL Statement is generated.
- Shared SQL area is searched for the same hash value.
- Memory address and Hash value of the execution plan for the statement can differ.
- During parsing based on the result of hash value check
- It might go for either of the two parse operations
- Hard Parse
- Libary Cache and Data Dictionary Cache is accessed numerous times to check the data dictionary.
- During this process a latch (serialization device) is acquired on the object. so that their definition does not change.
- LATCH CONTENTION increases statement execution time and decreases concurrency.
- If existing code cannot be used a new executable version is created.
- This is called Hard Parse or LIBRARY CACHE MISS
- Library Cache Miss since it could not find/reuse the existing code in the shared pool.
- For DDL there is always an Hard Parse.
- Soft Parse
- SQL Statement submitted in the shared pool and if it is reusable, results in Soft Parse or LIBRARY CACHE HIT.
- Soft parses are generally preferred since it reduces the amount of latching and also it (the database) skips the optimization and row source generation step and goes straight to execution.
- Optimization
- This is the process where the most efficient path of execution of SQL Statement is selected based on the statistics collected.
- There is at least one hard parse for every DML.
- DDL is never optimized unless it uses DML as part of subquery
- for example :
- create table test as (query here...)
- Row Source Generation
- This is the process where optimizer passes on the optimal execution plan called the query plan. (Explain plan does help in getting the query plan)
- Every step of the plan returns a row set which is used by next step.
- Last step of the execution returns the result set.
- Execution
- In this step SQL engine executes each step.
- If an execution plan is generated using EXPLAIN PLAN, it should be read from bottom up, since the execution of is in reverse order, one step at a time.
Reference or Source : Various : Oracle documentation, Internet, Personal experience.
Posts
