SQL Processing

 SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement.

SQL Parsing:

The first stage of SQL processing is parsing.

Syntax Check: 

Oracle Database must check each SQL statement for syntactic validity.

Semantic Check:  

Validates the object names, column names and user privileges.

Shared Pool Check:  

    The database uses a hashing algorithm to generate a hash value for every SQL Statement. This hash value is deterministic with in a version of Oracle Database. so, the same statement in a single instance or in different instances has the same SQL ID. (SQL ID like 3662827837

Hard parse:  

Generating a new execution plan. This process involves checking for query correctness and optimizing the execute plan.

Soft parse: 

Reuse the existing parse tree and execution plan.

Optimization:

Generate the multiple execution plan. Oracle Database must be generated at least one execution plan for every unique DML statements.

The Database does not optimize the DDL statement. The only exception is when the DDL includes a DML statement.

SQL Row Source Generation

The row source generator is software that receives the optimal execution plan from the optimizer and produces an iterative execution plan that is usable by the database.

The row source generator produces a row source tree, The row source tree for a SQL statement shows information such as table order, access methods, join methods, and data operations such as filters and sorts.

This example shows the execution plan of a SELECT statement when AUTOTRACE is enabled.

SQL Execution

The SQL engine executes each row source in the tree produced by the row source generator.

Data Retrieval: 

Data is retrieved from the Database Buffer Cache or from disk if it is not in the cache.

Join Operations: 

If the query involves multiple tables, join operations are performed according to the execution plan.

Filtering and Sorting: 

Rows are filtered and sorted based on the query criteria.