Different phases of a query processing in oracle:

Sometimes I wonder how a query is processed when we fire it against an oracle database. Let’s discover the same…

  

Actually a query is different from other types of SQL statement because, if successful, it returns data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. There are three main stages in the processing of a query:

1- Parse

2. Bind

3- Execute

4- Fetch

During the parse stage, the SQL statement is passed from the user process to the server process, and a parsed representation of the SQL statement is loaded into a shared SQL area. During the parse, the server process performs the following functions:

- Searches for an existing copy of the SQL statement in the shared pool

- Validates the SQL statement by checking its syntax - Performs data dictionary lookups to validate table and column definitions

2) Bind: After parsing, the oracle server knows the meaning of the oracle statement but still may not have enough info(values for variables) to execute the statement. The process of obtaining these value is called as bind values.

The execute executes the statement using the best optimizer approach and fetch retrieves the rows back to the user.

There are two types of parse operations:

1- Hard parsing:

A SQL statement is submitted for the first time, and no shareable match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a

parse.

2- Soft parsing:

A SQL statement is submitted, and a match is found in the shared pool. The match can be the result of a previous execution by another user. The SQL statement is shared, which is good for performance. However, soft parses still require syntax and security checking, which consume system resources.

When bind variables are used properly, more soft parses are possible, thereby reducing hard parses and keeping parsed statements in the library cache for a longer period. This is very important for an Oracle application to be scalable.

The optimizer is the part of the Oracle Database that creates the execution plan for a SQL statement. The determination of the execution plan is an important step in the processing of any SQL statement and can greatly affect execution time. The execution plan is a series of operations that are performed in sequence to execute the statement. The optimizer considers many factors related to the objects referenced and the conditions specified in the query. The information necessary to the optimizer includes:

- Statistics gathered for the system (I/O, CPU, and so on) as well as schema objects (number of rows, index, and so on)

- Information in the dictionary

- WHERE clause qualifiers

- Hints supplied by the developer

When we use diagnostic tools such as Enterprise Manager, EXPLAIN PLAN, and SQL*Plus AUTOTRACE, we can see the execution plan that the optimizer chooses.

The Oracle query optimizer (Cost Based Optimize-CBO) determines the most efficient execution plan and is the most important step in the processing of any SQL statement.

The optimizer: -

- Evaluates expressions and conditions

- Uses object and system statistics

- Decides how to access the data

- Decides how to join tables

- Decides which path is most efficient

A good understanding of SQL processing is essential for writing optimal SQL statements.

:-)