Oracle Execution Plan

PHASES OF SQL Execution  (SQL Engine will drive)

PARSE

• Syntax Checking
• Semantic Checking (SQL statements and PL/SQL blocks, the Pro*C/C++ Precompiler helps you quickly find and fix coding mistakes)
• Dividing into Literals (Ex:- Select |*| from |emp|;

EXECUTION

• Convert to ASCII format
• Compilation
• Running or Execution

FETCH

• Physical or Logical read

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

2.       The 1^st phase of sql execution i.e. Parsing will be done in library cache

3.       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)

4.       Server process will send the parsed statement with its execution plan to PGA and 2^nd phase i.e. EXECUTION will be done.

5.       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

6.       In such cases, server process will copy data from datafiles to MRU end of LRU list of database buffer cache

7.       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