SQL Statement Processing in Oracle Database

Hi,

I will explain SQL Statement Processing in Oracle Database in this article.

 

You should read the following article before this to learn Oracle database architecture detailed.

https://ittutorial.org/oracle-database-architecture-3/

 

 

SQL statements processing are as follows.  This steps are very important to understand architecture of Oracle database.

 

 

SQL Statement Processing Steps are as follows.

1. Create a cursor. ( It contains information for statement processing)
2. Parse the statement. ( Representation of SQL created and moved into the shared SQL area if there is no identical SQL in the shared SQL area )
3. Describe query results. ( This step provides information about the select list items )
4. Define query output.  (it defines location, size, and data type information required to store fetched values in variables )
5. Bind variables.
6. Parallelize the statement.
7. Execute the statement.
8. Fetch rows of a query.
9. Close the cursor.

 

When any SQL is sent Oracle for the first time, Oracle will create a plan by following the steps below. Or, if the plan has been created, the existing plan will be used or new plan will be created even if the SQL is the same (different literal values, or not using bind variable ).

 

 

 

If a new SQL is not exists in the Shared pool, it must be parsed from scratch.

The cost of creating the plan for the first time is very high for Oracle database.

 

SQL Statement implementation figure is as follows.

 

 

If any SQL will run for the first time, Oracle has to do a hard parse. If this SQL has been run at least once before, that is, Oracle does a Soft parse for this execution.

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/