Oracle: SQL statement execution

In order to process an SQL statement, the statement goes roughly to the following four stages:

• Parsing the SQL text
• Optimizing
• Row source generation
• Execution

During compilation (optimization?), Oracle decides if dynamic statistics will be used to generate an execution plan.

Additional memory is allocated from the private SQL area during each execution of an SQL statement.

decode(kxsccsta ,
  0, 'CURNULL'  ,
  1, 'CURSYNTAX',
  2, 'CURPARSE' ,
  3, 'CURBOUND' ,
  4, 'CURFETCH' ,
  5, 'CURROW'   ,
     'ERROR'
)                      status…

The Resource Manager might terminate the execution of an SQL statement when the statement consumed too many resources.

See also the columns AVOIDED_EXECUTIONS and SQL_QUARANTINE in v$sql and v$sqlarea.

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

• dbms_session.session_trace_disable
• dbms_session.session_trace_enable
• dbms_session.set_sql_trace
• xxx_trace_disable and xxx_trace_enable procedures in dbms_monitor
• Trace file analyzer, $ORACLE_HOME/suptools/tfa/release/tfa_home/bin/ftactl
• oradebug
• Trace files

select
   value
from
  v$diag_info
where
  name = 'Default Trace File';

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.