How is SQL Statement Processed ?

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.