Oracle: SQL Execution Plan

The optimizer's main goal is to generate the optimal execution plan for SQL statements.

When the optimizer formulates a plan, it bases its decision on statistical numbers, commonly referred to as optimizer statistics.

Such statistical numbers include:

• Table related statistics such as numbers of rows
• Column statistics such as number of nulls
• Index related statistics such as B-tree levels
• System statistics such as CPU and I/O performance

Hints allow a developer to override the execution plan that Oracle would have chosen otherwise.

If an SQL statement is not modifiable (for example because it is exectued from a 3rd party application), it is still possible to inject hints into the statement with SQL patches.

Different SQL statements with the same execution plan (or at least the same plan hash value).

SQL execution plans for subqueries in where clauses.

With adaptive query optimization, the statement executor can adjust an SQL execution plan (which was created at compile-time) at run-time.

SQL plan directives store statistical information about past executions of an SQL statement. They're used to (hopefully) create a more optimal plan for a future execution of the SQL statement.

v$sql_plan and v$sql_plan_monitor

dbms_xplan

The STAT lines in a trace file.

The fixed tables x$xplton, x$xpltoo and x$kqlfxpl.

sys.sqlobj

nls_sort.

PLW-07204: conversion away from column type may result in sub-optimal query plan

Execution plans in SQL Server.

The SQL statements that are executed as part of a trigger is not recorded for the statement that fires the trigger.

Execution plan tests.

dba_hist_sql_plan

Different SQL statements with the same execution plan (or at least plan hash value).