Oracle - Stages in Processing Queries, changes and commits

Oracle has its own process of retrieving or performing actions on data from system. User cannot define or say such process for the Oracle server to do action but instead use SQL functional programming language as opposed to procedural programming language like C, COBOL.
RDBMS (Relational Data Base Management System) translates the outcome defined in SQL statement into a process by which Oracle can obtain.

These are the steps that go on in Oracle server when a “select” statement is issued by the user to the Oracle server.

1) Search Shared pool :

RDBMS will first attempt to determine if a copy of this parsed SQL statements exists in library cache (shared pool).

2) Validate Statement :
This step is by RDBMS is to check if the syntax of the statement is valid?

3) Validate Data sources :

RDBMS validates the data sources that are being used by the statement exist?
Like tables, columns referred by the statement.


4) Acquire Locks :

RDBMS locks the objects that are been referred by the statement to avoid the changes made on their definitions while the statement is parsed.

5) Check privileges :

RDBMS ensures that the user attempting to execute this SQL statement has enough privileges in data base to do so.

6) Parse statement :

This step is to prepare an execution plan or parse tree for the statement and place in library cache. Oracle considers this execution plan as optimized for executing the SQL statement. This is a list of operations the RDBMS uses to obtain data.
If execution plan or parse tree already exists in library cache then RDBS will omit this step.

7) Execute statement :

RDBMS performs all processing to execute the select statement. At this point, server process fetches data from disk to buffer cache.

8) Fetch values from cursor :

After the select statement is executed all data returned from Oracle is stored in cursor. That data is then placed into bind variables, row by row, and returned to the user process.


After the above 8 steps the execution plan is made and stored in library cache, data in buffer cache. Just in case if the same user or another user wants to execute the same select statement (multiuser environments), performance is said to be achieved as RDBMS skips the steps of preparing execution plan or parse tree and server process to fetch data from disk to buffer cache.