<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to get an Oracle execution plan with all important information

Published on
6,081 Points
2,581 Views
Last Modified:
You have a query that takes too long and you want help to analyze the execution plan?

Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.

Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.
 

Method 1 - Without Tuning Pack

I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:
 
 
set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Open in new window

 
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:
 
 
-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Open in new window

 
Finally I get the execution plan to a text file:
 
 
spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
spool off

Open in new window

 
The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.
 

Method 2 - With Tuning Pack


When you have tuning pack, you have access to the great SQL monitoring feature.
 
SQL> show parameter pack
 
 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 control_management_pack_access       string      DIAGNOSTIC+TUNING

Open in new window

 
I set the sqlplus environment to nicely spool to html file:
 
 
set pagesize 0 linesize 10000 trimspool on serveroutput off long 1000000000 longc 1000000000 echo off feedback off

Open in new window

 
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables. One difference here: the MONITOR hint to force SQL Monitoring.
 
 
-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT /*+ monitor */ * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Open in new window

 
Finally, get the execution plan to a html file:
 
 
spool plan.htm
select dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'active') from dual;

off

Open in new window

 
The html file is very small but will be rendered by an online flash automatically loaded from the oracle.com website. You can see both output, plan.txt, and plan.htm from the attatched xplans.zip
 
Here is how they look like (but please never send me screenshots of execution plans...):

plan from dbms_xplan 
And the colorful active report from SQL Monitoring:
 
Plan from SQL Monitor 
So now, when asking a question about an Oracle  SQL query performance, please include the execution plan.
xplans.zip
0
Comment
2 Comments
LVL 67

Expert Comment

by:Jim Horn
When I add the first two blocks (without parameters) above and below my query and then execute, I receive the set in the below image, with no mention of where the .txt file is, so it's not clear how I should  proceed. Screen-Shot-2016-01-28-at-3.18.13-PM.pngOracle SQL Developer v4.1.2.20.
0

Expert Comment

by:Jacob Forest
You can do it with dbForge Studio for Oracle (https://www.devart.com/dbforge/oracle/studio/). It provides execution plan, step-by-step code execution, breakpoints, watches, a call stack and other useful features.
0

Featured Post

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month