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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *