Oracle SQL : SQL Statement Processing Phase

In SQL Statement processing the four phase are important Parse,Bind,Execute,Fetch.
The reverse arrow indicates the processing flow Fetch-->Rebind-->Execute-->Fetch.
The Fetch phase applies to the quires and DML statement with return clause.

I.  Parse Phase.
=============

 * Checks the syntax
 * Checks the semantics and privileges.

 Hard Parse.

1. Merges view definitions and  sub-quires  and determine the execution plan for the query.
   Hard parse happens in two conditions
       ==> First time SQL statement submitted to execute
      ==> When no execution plan is found in the  shared pool.

Soft Parse .
1. Searches for the execution plan in the shared pool (Memory area where the Execution plans stored )for the SQL statement to execute. Soft parse will reduce the overhead of determining the
execution plan , and hence boost the performance , still it requires to check the syntax and security checking.

II. BIND Phase
===============

This phase intended to share the execution plan in the SQL statement by using the Bind Variable concept , so that soft parse will take place and boost the performance.
    Bind Phase:

• The Oracle Database checks the statement for references to bind variables.
•  The Oracle Database assigns or reassigns a value to each variable.

III. Execute Phase :
==============
    
 The execution plan is a series of steps that the server process uses to access and to identify the
required rows of data from the data buffers. Multiple users can share the same execution plan. The
Oracle Database performs physical reads or logical reads/writes for DML statements and also sorts
the data when needed.

Note: Physical reads are disk reads; logical reads are blocks already in memory in the database
buffer cache. Physical reads use more resources and time because they require I/O from disk
    
IV. FETCH Phase :
===============

The Oracle Database retrieves rows for a SELECT statement during the fetch phase. Each fetch
typically retrieves multiple rows, using an array fetch. Array fetches can improve performance by
reduce network round trips. Each Oracle tool offers its own ways of influencing the array size; For
example, in SQL*Plus, you can change the fetch size by using the ARRAYSIZE setting:
SQL> show arraysize
arraysize 15
SQL> set arraysize 50
SQL*Plus processes 15 rows at a time by default. Very high array sizes provide little or no advantage.


Uptake From this post As Developer:
==============================.

1. Use the bind variable in the application instead of constant so that the execution plan can share and hit the soft parse.
2. Use the API that leverage the bind variable e.g.  Prepared Statement in JDBC.
3.Set Appropriate Arraysize so that it will reduce the physical I/O ,network round-trip  .