SQL Processing

Here's an overview about the SQL processing which will help appreciate the need for the better-written SQL statements.

SQL Processing Phases

1. Open
2. Parse
3. Bind
4. Execute
5. Fetch (Select statements)
6. Close

($$) The PARSE phase
Checks to see if the statement already exists in the shared pool.
Checks to see if the statement is valid SQL language.
Oracle will then look to see if the referenced objects exist.
Oracle will see if the parsing user has permission on all underlying objects.
Explodes any queries on views and attempts to simplify the statement by using a join.
Picks an execution plan and stores it with the cursor

($$) The BIND phase
Searches statement for bind variables
Assigns a value to the parsed statement.

($$) The execute phase
Oracle will then run the execution plan against the block buffers.
Oracle performs all the required input and output calls to gather the data for the DMLstatement. This phase may require Oracle to perform sorting operations.

($$) The FETCH phase
Retrieve all the rows for the select statement.
Possibly sort the retrieved records.
All returned rows are retrieved using an array fetch.