Skip to content

Understanding SQL Execution Cycle

Published May 30, 2015 By Brijesh Gogia

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

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 130

Related posts:

  1. Performance Tuning Basics 2 : Parsing
  2. Performance Tuning Basics 4 : Bind Variables
  3. Performance Tuning Basics 3 : Parent and Child Cursors
  4. Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
  5. Key Reasons for Database Performance Issues
  6. Performance Tuning Basics 15 : AWR Report Analysis
  7. Performance Tuning Basics 12 : Dynamic Performance Views
  8. Data Guard Physical Standby Database Processes and Data Flow
  9. Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
  10. Performance Tuning Basics 14 : Active Sessions History (ASH) Basics

Categories:TUNING Tuning Basics

2 Comments

  1. Sachin Chavan Sachin Chavan

    Thanks a lot Brijesh! Very informative blog. It is helping me a lot to clear performance tuning Concepts.

    December 10, 2018 Reply
  2. Vimal Rathinasamy Vimal Rathinasamy

    This is an excellent Article. Thank you Brijesh.

    December 9, 2023 Reply
Leave a Reply to Sachin Chavan Cancel reply