Basic Functions of the Oracle EXPLAIN PLAN Utility

More than any other tool, the EXPLAIN PLAN utility is the most indispensable. In fact, all of the other Oracle tools rely on the EXPLAIN PLAN utility to allow the DBA to view the access path to the data. We have no hope of tuning a SQL query until we can see how it is accessing the database, and the EXPLAIN PLAN utility makes the access path visible. The EXPLAIN PLAN utility can be invoked directly on the UNIX server with the EXPLAIN PLAN command syntax, or it can be invoked from Oracle SQL analyze client software. As we noted in an earlier lesson, it is never clear from looking at an SQL statement how the database will be accessed. To illustrate, consider the following SQL statement presented in the following series of images.

1) The first section deletes all rows from the plan table

set echo off
set verify off 
delete from plan_table;
commit;

2) The main part of the SQL populates the plan table with the access path information.

col operation format a30
col options format a15
col object_name format a20
col table_name format a10
col column_name format a20
col constraint_name format a20

set echo on

explain plan
set statement_id='TBST'
for
--select /*+ first_rows */
--select /*+ use_hash */
select
a.db_name db,
a.owner owner,
a.table_name tab_name,
a.extents old_extents,
b.extents new_extents

This script configures the column display formats for various types of output in SQL*Plus, sets up to echo commands, and prepares to explain a plan for a SQL query that compares the number of extents between two instances of table data, possibly before and after an operation. The commented parts indicate the option to use optimizer hints like `first_rows` and `use_hash` which can influence how Oracle executes the query.

Step3: The final step display the access information from the plan table.

set echo off
select
lpad(' ', 2*(level-1)) ||
operation ||'  '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from plan_table
START WITH id=0
connect by prior id= parent_id;

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;

Oracle's query optimizer uses an execution plan for each query submitted. By and large, although the optimizer does a good job of selecting the execution plan, there may be times when the performance of the database suggests that it is using a less-than-optimal execution plan. The only way you can really tell what path is being selected by the optimizer is to see the layout of the execution plan. You can use two Oracle character-mode utilities to examine the execution plan chosen by the Oracle optimizer. These tools allow you to see the successive steps used by Oracle to collect, select, and return the data to the user.

• First Utility: The first utility is the SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table. You can subsequently run a query on that table to get the execution plan, as shown in SQL*Plus in Figure 2-5.
  

  SQL> EXPLAIN PLAN FOR
    2  SELECT DNAME, ENAME FROM EMP, DEPT
    3  WHERE EMP.DEPTNO = DEPT.DEPTNO
    4  ORDER BY DNAME;
  
  Explained.
  
  SQL> SELECT OBJECT_NAME, OPERATION, OPTIONS FROM PLAN_TABLE ORDER BY ID;
  
  OBJECT_NAME                      OPERATION                        OPTIONS
  -----------------------------    -----------------------------    -----------------------------
                                    SELECT STATEMENT                 ORDER BY
                                    SORT                             ORDER BY
                                    NESTED LOOPS
  EMP                              TABLE ACCESS                     FULL
  DEPT                             TABLE ACCESS                     BY INDEX ROWID
  SYS_CO04911                      INDEX                            UNIQUE SCAN
  
  6 rows selected.
  

  
  Figure 2-5. Results of a simple EXPLAIN PLAN statement in SQL*Plus
  We will now take a deeper look into SQL tuning by examining the optimizer modes.