:

The Oracle Explain Plan

The Oracle explain plan is one of the most useful SQL tuning utilities that you are going to use when tuning SQL queries within your Oracle database. From novice to expert users, you should always know how to generate an explain plan for a given piece of SQL.

As with most things in Oracle, there are many ways to generate an explain plan and there are also different formats you can produce the output in. A common way to generate an explain plan for a query is something like this:

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM TABLEA;

Then, if you are on a machine that has an Oracle client installed, you can run @?\RDBMS\ADMIN\utlxpls.sql to get the explain plan output. Alternatively, you can use a select statement and the DBMS_EXPLAN package to get the results back:

SELECT *
FROM TABLE(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

Explain Plan Parameters

The procedure DBMS_XPLAN.DISPLAY accepts 4 input parameters.

  • Table_name – Where the plan is stored
  • Statement_id – Specify is statement_id was set for the  statement in the plan table
  • Format – Controls the level of details in the plan:

BASIC: Displays the minimum information in the plan–the operation ID, the object name, and the operation option.

TYPICAL (Default): Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.

ALL: Maximum level. Includes information displayed with the TYPICAL level and adds the SQL statements generated for parallel execution servers (only if parallel). SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

There are also additional format keywords which can be used to customize the output further, which can be found in the Oracle documentation.

  • filter_preds – Used to restrict the set of rows selected from the plan table.

Oracle Explain Plan Example

Here is a very basic query that I made up on a large table to give you an example of how you would generate an explain plan and how the output should look

SQL> explain plan for
 2 select userid,tmstamp,count(*)
 3 from user1.policy
 4 group by userid,tmstamp
 5 order by count(*) desc;

Explained.

SQL> set lines 120
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1276582769
--------------------------------------------------------------------------------
| Id | Operation         | Name    |Rows| Bytes | TempSpc|Cost(%CPU)| Time     |
--------------------------------------------------------------------------------
| 0  | SELECT STATEMENT  |         | 72M| 1242M |        | 1016K (4)| 01:24:43 |
| 1  | SORT ORDER BY     |         | 72M| 1242M | 3886M  | 1016K (4)| 01:24:43 |
| 2  | HASH GROUP BY     |         | 72M| 1242M | 3886M  | 1016K (4)| 01:24:43 |
| 3  | TABLE ACCESS FULL | POLICY  | 72M| 1242M |        | 272K (6) | 00:22:43 |
--------------------------------------------------------------------------------

Oracle Autotrace

Aside from using the above method for generating an explain plan, it is also possible to do it without the need to run as many commands, and the explain plan can be viewed on screen straight after you have executed the query. You can use the AUTOTRACE functionality to do this. If you have errors trying to turn it on, you can read my article on errors with autotrace.

And if you are having trouble getting the explain plan results back from the plan_table, especially when you are facing the ORA-22905 Oracle error, you can have a read about it in my other article ORA-22905: cannot access rows from a non-nested table item

Like it, share it...

Category: SQL Tuning


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *