Database Reference
In-Depth Information
It's interesting to note that the default plan table is a global temporary table that stores data up to the end of the
session.
1
In this way, several concurrent users working with it don't interfere with each other.
To use a plan table with the
EXPLAIN PLAN
statement, you need at least
INSERT
and
SELECT
privileges. Even
though you can perform basic operations without it, the
DELETE
privilege is usually granted as well.
I don't describe the plan table fully here for the simple reason that you usually don't need to query it directly.
For a detailed description of its columns, refer to the
Performance Tuning Guide
(up to and including version 11.2), or
the
SQL Tuning Guide
(beginning with version 12.1).
Querying the Plan Table
It may be obvious that you can obtain the execution plan by running queries against the plan table directly. However,
it's easier to use the
display
function in the
dbms_xplan
package, as shown in the following example. As you can see,
its utilization is simple. In fact, it's enough to call the function in order to display the execution plan generated by
the
EXPLAIN PLAN
statement. Notice how the return value of the function, which is a collection, is converted with the
table
function:
SQL> EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10 ORDER BY ename;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 114 | 3 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
The
display
function isn't limited to being used without parameters. For this reason, later in this chapter I cover
the
dbms_xplan
package, exploring all the possibilities, including a description of the generated output.
