How Select Statement Works ?

1. When user process submits the select query, It will be received by the server process.

2. Server process will handover the statements to “library Cache” (L.C) of shared pool.

3. A pre-check will be done for the existence of parsed statement.

4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of parsing ( First phase in SQL execution).

5. Parsing will be done in three steps.

       a. Dividing the SQL statement into literals.
       b. Checking for syntax error.
       c. Checking for symantic error.

6. Server process will perform symantic checking (checking whether the table is existing or not, checking for necessary priviliges etc.)

7. Server process will checking using dictionary information in data dictionary cache (DDC). Note: If the information is not availiable server process will copy that from database.

8. Optimizer ( Brain of oracle) will generate many execution plans ( a plan specifies how fast SQL statement get executed and choose the best one based on time and cost. Note: Time represents the last response time and cost represents least CPU utilization.

9. The statement will get executed in PGA (using the best execution plan provided by the optimizer) in three spets.

    a. Conversion of charaters into ASCII values.
    b. Compilation.
    c. Running.

Note: oracle will performs compilation and run its internal algorithms.

10. After execution server process will collect some more Dictionary information from DDC (data Dictionary cache).

11. Server process will start searching for data from LRU END (LRU means Least recently used) in LRU LIST of database buffer cache.

12. The search will continue till it reaches MRU end or the data is found.

Note: If the data is not availiable in LRU LIST then server process will copy the data from database and places a copy at MRU END of LRU LIST of Database buffer Cache.