Wednesday , July 26 2017
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

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

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