Select Statement Work Flow

Select Statement Work Flow :

-->User fire the sql statement for firsttime the process is called "Hard parsing'.
-->User fire the sql statement for second time the process is called "Soft parsing".


->User process will create the user process cannot communicate to the server directly .
->Server process will communicate to user process and create the session called session sort (private
global area)

->Only server process can access the PGA(Private Gobal Area)

library cache create a

     1.SQL ID /Statement ID -- Alpha numeric

     2.Hash Value will generate for the sql statement -- Numeric

     3.Parsing:
    
     Syntax check  -- command is correct or not
     Sematic Check -- It will check the users,Priviliges,tables present in DB or not

     4.Optimization:

        A.Query Transformer -- Rewrite the given query to different ways )
        B.Cost Estimator       -- It will calculate the CPU usage ,Memory usage ,I/O, Time
                                              to execute for all the queries
        C.Plan generator        -- It generate some plan (execution plan for the given
                                              sql statement to get the data from disk).
        D.Optimizer               -- It will choose the best execution plan based on Cost estimator calc.                                                  
  The Whole process is called "Hard Parsing".

  If the second time user fire the same query is called the "Soft Parsing".