How Oracle Database Processes SQL Statements

<<Back to PT Main Page

Processing of SQL Statements By Oracle Database Engine

As soon as the SQL query arrives to the database, Database engine starts preparing the SQL statement to execute. Oracle Processes a SQL statement in following 4 stages.
Parsing
Parsing of a SQL statement is, breaking down the SQL statements into executable version of code, for execution by database engine.
In this stage oracle performs following task

1. Cursor is created
2. Syntax Check is performed
3. Semantic Check is performed
4. Security check is performed (Privileges on object/table)
5. Hash value for SQL statement is generated  (Hash Value = V$SQL.SQL_ID)
6. Shared Pool Check for the generated hash value is done

Based on the outcome of 5 either a soft parse or a Hard parse is done.

Soft Parse: 

Once the Hash Value is generated, Database searches the Shared Pool for this Hash Value, If oracle finds the Hash Value in shared pool (also known as cache hit ) it just executes the already available parse code and returns the result to the client.

Hard Parse: 

If the database does not find the Hash Value in shared pool (also known as cache miss) It performs Hard Parsing. During Hard parsing oracle performs following tasks.

1. Transformation of SQL statement into executable version code
2. Optimization
3. Row Source Generation

During the hard parse, the database accesses the library cache and data dictionary cache numerous times using latch which makes it really resource intensive.

Cursor:  Memory area allocated in PGA, that holds a parsed SQL statement, hash value of SQL statement etc. The statement hash value is the SQL ID shown in V$SQL.SQL_ID

Optimization:
In this step oracle Optimizer generates multiple execution plans based on the statistics available.
It choses then the lowest cost Plan.
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 rest of the
database.
SQL Execution:
During execution, the SQL engine executes each row source in the tree produced by
the row source generator.

Find Hard Parse vs Soft Parse

 SQL> select s.name, m.value
 from   v$statname s, v$mystat m
 where  s.statistic# = m.statistic#
 and    s.name like 'parse%(%';

Find the Soft Parses of a Query

SQL> select sql_id,sql_text,PARSE_CALLS from v$sql
where sql_text like '%sql statement%'
and parsing_schema_name='Username'