A SQL statement can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
PLANresults let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN button on the worksheet. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. SQL Developer looks at the table and displays the tree operations. Looking at the example, we can see the query is doing a Cartesian product
However, even though we can show you what a plan looks like, it cannot differentiate between well-tuned statements and those that perform poorly.
For example, an
PLANoutput that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
- The columns of the index being used
- Their selectivity (fraction of table being accessed)
It is best to use
PLANto determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.
And from the doc!:
In addition to running the
PLANcommand and displaying the plan, you can use the
V$SQL_PLANviews to display the execution plan of a SQL statement. After the statement has executed, you can display the plan by querying the
V$SQL_PLANcontains the execution plan for every statement stored in the cursor cache.
V$SQL_PLAN_STATISTICSview provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs. The statistics in
V$SQL_PLAN_STATISTICSare available for cursors that have been compiled with the
STATISTICS_LEVELinitialization parameter set to
V$SQL_PLAN_STATISTICS_ALLview enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both
V$SQL_PLAN_STATISTICSinformation for every cursor.