how to Processing SQL in Oracle

The following steps show how Oracle processes SQL
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that
represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash
table where it maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one
sitting in the cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL
statement, an entry is made in the library cache hash table for newly
arrived code, and it is placed in the library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
· During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
· Determines whether user has privilege to execute the statement
· Determines optimal execution plans for statement
· Finds a shared SQL area for the statement
· In execution stage, for UPDATE and DELETE statement, Oracle
locks the affected rows, looks for data blocks in DB buffer cache,
if found, executes becomes faster, if not then Oracle has to read
from physical data files to buffer cache. For SELECT and INSERT
statements, locking is not necessary.
· During fetch operation, rows are fetched to user process.