Understanding SQL Execution Cycle

A normal SQL statement go through below phase before producing results.

1) PARSE PHASE

Parsing stage will tell what does the statement mean and how best to run it

There are two type of parses:

a) Soft Parse

• check syntax, semantics and permissions
• search the library cache for matching statement

If it finds the statement in library cache then it will not do the hard parse.

b) Hard Parse

• also called optimization step
• necessary on first execution
• take parse locks on referenced object
• develop a decision tree of possible plans
• select the lowest cost plan for execution
• store the plan in the library cache of the shared pool

Important: hard parse takes time!

GENERAL GOAL : PARSE ONCE AND EXECUTE MANY TIMES

2) BIND PHASE

This phase explain any variables to be used by SQL

• not necessary if the statement uses no variables
• retrieves values for variables from the user process
• usually occurs after parsing
• make possible re-use of existing optimized code
• exception – first execution will “peek” the binds
• Different binds may warrant different execution plans — can be tackled by adaptive cursor sharing

3) EXECUTE PHASE

This phase works in buffer cache

• Most of the work for DML occurs here
• shared locks on tables and exclusive locks on rows are taken
• generate redo – write change vectors to the log buffer
• write changes to the buffer cache by generating Undo OR writing new values to the table and index blocks

Data I/O during Execution phase can be:

a) Logical I/O

search the buffer cache for necessary data blocks and get it

Latch is light weight lock which is required to access Logical I/O. So logical I/O is not free. REMEMBER that Latches and Locks reduce scalability.

b) Physical I/O

copy blocks of table, index and undo segments into cache cause Physical I/O and it is costly operation.

 
 

4) FETCH PHASE

This phase prepare and return the result set.

• Most of the work for SELECT occurs here
• I/O from disk into memory if required
• Search the buffer cache for required data blocks or do direct/indirect reads.

        Direct Reads: Copy Blocks into PGA. It is private area to user so no latch/lock contention

        Indirect Reads: Copy Blocks into Buffer Cache.  It is a shared area so latches will come into play.

• Performs join,aggregation (group by), projections, sorts (order by)
• return the result set to the user process