The life of an SQL statement

What happens when Oracle processes an SQL Statement?

An SQL statement must always be parsed. Then, it can be executed any number of times. If it is a select statement, the result-data needs be fetched for each execution.

One of the goals of the parse phase is to generate a query execution plan (QEP).

Does the statement correspond to an open cursor, ie, does the statement already exist in the library cache. If yes, the statement needs not be parsed anymore and can directly be executed.

If the cursor is not open, it might still be that it is cached in the cursor cache. If yes, the statement needs not be parsed anymore and can directly be executed.

If not, the statement has to be verified syntactically and semantically:

This step checks if the syntax of the statmenet is correct. For example, a statement like select foo frm bar is syntactically not correct (frm instead of from).

A statement might be invalid even if the syntax is correct. For example select foo from bar is invalid if there is no table or view named bar, or if there is such a table, but without a column named foo.

Also, the table might exist, but the user trying to execute the query does not have the necessary object privileges.

If the statement is syntactically and semantically correct, it is placed into the library cache (which is part of the Shared Pool).

A cursor for the statement is opened. The statement is hashed and compared with the hashed values in the sql area. If it is in the sql area, it is a soft parse, otherwise, it's a hard parse.

Only in the case of a hard parse, the statement undergoes the following steps (view merging, statement transformation, optimization):

If the query contains views, the query might be rewritten to join the view's base tables instead of the views.

Transforms complex statements into simpler ones through subquery unnesting or in/or transformations

The CBO uses "gathered??" statistics to minimize the cost to execute the query. The result of the optimization is the query evaluation plan (QEP).

If bind variable peeking is used, the resulting execution plan might be dependant on the first bound bind-value.

Memory for bind variables is allocated and filled with the actual bind-values.

The execution plan is executed.

Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache.

The record(s) that are changed are locked. No other session must be able to change the record while they're updated. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed.

Data is fetched from database blocks. Rows that don't match the predicate are removed. If needed (for example in an order by statement), the data is sorted. The data is then returned to the application.

The query execution plan is also stored in the library cache. EXPLAIN PLAN can be used to see how the statement is executed.

Each session that issues an SQL statement has a private SQL area associated with this statement. The first step for Oracle when it executes an SQL statement is to establish a run time area (within the private SQL area) for the statement.

dbms_sqltune, statspack, ....