Oracle EXPLAIN PLAN Utility

Oracle 13c Cloud Control, as the latest version of Oracle's comprehensive management solution for the Oracle technology stack, provides extensive facilities for monitoring and optimizing database performance. The EXPLAIN PLAN utility is a function of the Oracle Database itself, not specifically a feature of the Oracle 13c Cloud Control.
The EXPLAIN PLAN utility is a fundamental component of Oracle SQL. It is used to comprehend the execution plan chosen by the Oracle optimizer for SQL statements. The tool works by displaying the chosen path by the optimizer to execute a SQL query, giving database administrators crucial insights into the performance of individual SQL commands.
Oracle 13c Cloud Control provides a user interface that allows administrators to interact with Oracle databases, which inherently includes the capability to execute EXPLAIN PLAN commands. Moreover, Oracle 13c Cloud Control includes powerful features to support performance analysis and SQL tuning, such as

1. SQL Tuning Advisor and
2. SQL Performance Analyzer,

which utilize underlying capabilities like EXPLAIN PLAN. Oracle 13c Cloud Control does not directly contain the EXPLAIN PLAN utility. However, it provides a sophisticated environment that facilitates the use of EXPLAIN PLAN, as part of its comprehensive suite of tools for managing, monitoring, and tuning Oracle databases.

The EXPLAIN PLAN statement enables you to examine the execution plan that the optimizer chose for a SQL statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Issue the EXPLAIN PLAN statement and then query the output table.

The basics of using the EXPLAIN PLAN statement are as follows:

1. Use the SQL script CATPLAN.SQL to create a sample output table called PLAN_TABLE in your schema.
2. Include the EXPLAIN PLAN FOR clause before the SQL statement.
3. After issuing the EXPLAIN PLAN statement, use a script or package provided by Oracle Database to display the most recent plan table output.
4. The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
SELECT last_name FROM employees;