Home / Oracle DBA / PHASES OF SQL EXECUTION

PHASES OF SQL EXECUTION

PHASES OF SQL EXECUTION

  1. Any SQL statement will undergo following phases to get executed
    1. PARSING : This phase will perform following actions
      1. Syntax checking of the SQL statement
      2. Semantic checking of the statement i.e. checking for the privileges using base tables
      3. Diving the statement into literals
    2. EXECUTION : This phase will perform following actions
      1. Converting the statement into ASCII format
      2. Compiling the statement
      3. Running or executing the statement
    3. FETCH : Data will be retrieved in this phase
Note: For a PL/SQL program, BINDING will happen after PARSING phase (so it will have 4 phases to go)
SELECT STATEMENT PROCESSING
  1. Server process will receive the statement sent by user process on server side and will handover that to library cache of shared pool
  1. The 1st phase of sql execution i.e. Parsing will be done in library cache
  1. Then, OPTIMIZER (brain of oracle sql engine) will generate many execution plans, but chooses the best one based on time & cost (time – response time, cost – cpu resource utilization)
  1. Server process will send the parsed statement with its execution plan to PGA and 2nd phase i.e. EXECUTION will be done there
  2. After execution, server process will start searching for the data from LRU end of LRU list and this search will continue till it founds data or reaches MRU end. If it found data, it will be given to the user. If it didn’t found any data, it means data is not there in database buffer cache
  1. In such cases, server process will copy data from datafiles to MRU end of LRU list of database buffer cache
  1. From MRU end again blocks will be copied to PGA for filtering required rows and then it will be given to user (displayed on user’s console)
Note: server process will not start searching from MRU end because there may be a chance of missing the data by the time it reaches LRU end in searching.
Note: for statements issued for the second time, parsing and fetch phases are skipped, subject to the availability of data and parsed statement in the instance.
SELECT STATEMENT PROCESSING
Read More:
Partitioned Tables and Indexes
Performance Tuning Using AWR, ASH and ADDM
Performance Tuning
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe