Search notes:

Oracle: SQL statement execution

Processing an SQL statement

In order to process an SQL statement, the statement goes roughly to the following four stages:
During compilation (optimization?), Oracle decides if dynamic statistics will be used to generate an execution plan.
At some point in time (after parsing?), Oracle stores the result of the parsing in the library cache
Additional memory is allocated from the private SQL area during each execution of an SQL statement.

Updating statistics

When Oracle is done executing an SQL statement, it updates some statistical numbers for that statement in v$sql.
For long running statements, these statistics are updated every 5 seconds.

Status of parsing

The X$ table x$kxscc exhibits the current status of SQL statement in the column kxsccsta: 
decode(kxsccsta ,
  0, 'CURNULL'  ,
  1, 'CURSYNTAX',
  2, 'CURPARSE' ,
  3, 'CURBOUND' ,
  4, 'CURFETCH' ,
  5, 'CURROW'   ,
     'ERROR'
)                      status…

Terminating (quarantining) statements

The Resource Manager might terminate the execution of an SQL statement when the statement consumed too many resources.
This is referred to as quarantining the statement.
See also the columns AVOIDED_EXECUTIONS and SQL_QUARANTINE in v$sql and v$sqlarea.

Tracing SQL statement execution

TODO: What is the relationship between monitoring SQL statements to the SQL Tracing Facility?
If an SQL statement runs longer than 5 seconds, or is hinted with the /*+ monitor */ hint, some execution statistics are recorded in v$sql_monitor. The cumulative sum of these statistics are recorded in v$sql.
This statement gets some execution stats from a (preferably erunning) SQL statement from v$sql and and then gets the same performance indicators after an interval of time again and then reports the stats and their differences.
TODO 
select
   value
from
  v$diag_info
where
  name = 'Default Trace File';

See also

optimizer
Parallel SQL execution
The value of a limited set of init parameters can be set for the duration of the executtion of an SQL statement with the opt_param hint.
v$sql_plan_statistics provides actual (not estimated as in v$sql_plan) for every SQL plan operation of an executed SQL statement.
Adaptive query optimization allows to adjust an SQL execution plan at run-time.
When the init parameter parallel_degree_policy is set to auto, Oracle will queue the execution of SQL statements that require parallel execution if the necessary number of parallel execution servers is not available.
An SQL statement being executed can be terminated with the alter system cancel sql statement.
SQL execution can be tracked with auditing.

Index