Introduction
The purpose of this blog post is to present what I consider to be the best ‘default’ methods for collecting SQL execution plans.
There is no single ‘right way’, but I want to distill things down to a few cases that will be good-to-go in most scenarios.
I have not listed the output of all the examples below because it would take up too much space, but I’ve uploaded some self-contained scripts to GitHub.
Examples
Here are my suggestions …
Example A
If you can run the query stand-alone using (for example) SQL Plus or SQLcl:
select e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL +OUTLINE'));
Or, if you don’t want to execute the query:
explain plan for
select e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
SELECT *
FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));
The important feature of this example is that I am using FORMAT=>’ALL +OUTLINE’. Some of you might have come across the undocumented option, FORMAT=>’ADVANCED’. I am not using it here because the content of its output has the potential to be different between releases, but there’s no fundamental reason why you can’t use it. The ‘ALL’ format is documented and ‘OUTLINE’ is mentioned briefly; its basic content is unlikely to change between releases.
Example B
If you cannot run a query stand-alone, you can still get plan information from the cursor cache using a query like this:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR( SQL_ID=>'the_SQL_ID', CURSOR_CHILD_NO=>the_child_number, FORMAT=>'ALL +OUTLINE'));
You will need the SQL_ID and CHILD_NUMBER of the query you want. There are many ways of doing this, but if you have DBA privilege then you can search for the statement in V$SQL:
select /* MY_TEST_QUERY */
e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
select sql_id, child_number, sql_text
from v$sql
where sql_text like '%MY_TEST_QUERY%'
and sql_text not like '%v$sql%';
In cases like this, a useful FORMAT option you can add is ‘+PEEKED_BINDS.’ It will show you what values were seen for any bind variables when the SQL statement was parsed.
The plans above do not include any runtime information, so you will not see how long each part of the plan took to execute or how many rows were actually processed. For example, ‘Rows’ is an estimate; it does not tell you how many rows were actually processed. If you gather and examine runtime information, it is likely that your level of understanding will be enhanced significantly. How do you go about getting it?
Example C
You can use a hint to gather runtime information:
select /*+ gather_plan_statistics */
e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));
This will show you statistics such as the actual number of rows processed (A-Rows), rather than just the estimates (E-Rows). It also includes a column called Starts, which tells you how many times each step was executed. A-Rows, E-Rows and Starts are all incredibly useful if you want to understand a plan. Be careful, though! The additional monitoring we add to monitor plan statistcs will slow some queries down quite a bit.
Example D
If you don’t want to change the query text to add the hint, there is a parameter you can set instead:
alter session set statistics_level='ALL';
select e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));
Example E
DBMS_XPLAN ‘ALLSTATS LAST’ does not give you a continuous view of runtime statistics while a query is executing, but SQL Monitor solves this problem. It requires the Oracle Tuning Pack, so always check the licence user guide for your database version. This tool is fantastic for generating plans and monitoring SQL, and it is available via Enterprise Manager in the Performance Hub. Before I cover that, you can use it on the command line too (a fact that is often missed or forgotten for some reason):
select /*+ MONITOR */
e.ename,r.rname
from employees e
join roles r on (r.id = e.role_id)
join departments d on (d.id = e.dept_id)
where e.staffno <= 10
and d.dname in ('Department Name 1','Department Name 2');
-- Get the SQL ID of the query we just executed
select prev_sql_id
from v$session
where sid=userenv('sid')
and username is not null
and prev_hash_value <> 0;
PREV_SQL_ID
-------------
an05rsj1up1k5
set linesize 250 pagesize 0 trims on tab off long 1000000
column report format a220
select
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
(sql_id=>'an05rsj1up1k5',report_level=>'ALL') report
from dual;
Output:
The SQL_ID parameter is optional, but I usually set it explicitly because there might be multiple long-running queries in the system, so the default report will sometimes pick up a different SQL statement to the one I am experimenting with. The database automatically makes long-running queries available to SQL Monitor, but I used a MONITOR hint in this case because the query is very fast and wouldn’t normally show up.
It can be useful to monitor a query while it is executing because you can watch its progress and learn from that. This is where SQL Monitor is really useful because you can watch a query in another session and see its statistics updating continuously. You don’t necessarily have to wait for it to complete to figure out what part of the query is taking a long time, so you can sometimes avoid having to wait for completion. Note that you can get ‘ALL +OUTLINE’ plan details while a query is executing – just use Example B, above.
You can even generate an active HTML report using the command line! This is a great way to capture a SQL execution plan and explore it interactively later on. Just run the report like this:
-- spool output to a file, then…
select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
(sql_id =>'an05rsj1up1k5',
report_level =>'all',
type =>'ACTIVE') report
from dual;
If you spool the output and open it in a browser, you get an interactive HTML page like this:
Bear in mind that the browser requires Internet access because the HTML report downloads some external assets.
Example F
I know that many of you love the command line (and I am the same) but you should check out using SQL Monitor in the Oracle Enterprise Manager Performance Hub. It’s much easier to access interactive SQL Monitor reports and they will refresh continuously as query execution progresses. In addition, it is easy to save these reports and send them to others. Just use the Save button (circled in red, below).
If you hit the ‘Plan’ tab, it can be enlightening to look at a graphical view if the plan is not too large. I like to select ‘Rotate’ to give me a tree that is oriented vertically. Aha – now I can see what the left side and right side of a join actually means! Very broadly speaking, you read trees from the bottom left up. I might blog about this later. In the following example, and in common with the examples above, the database reads DEPARTMENTS first, then joins the rows with EMPLOYEES and then joins these rows with ROLES.
Example G
Finally, there is SQL Developer too!
With DBMS_XPLAN:
Bind Peeking
If the SQL statement has bind variables, it can be useful to acqure the bind values peeked at parse time for a particular SQL statement. Here’s an example:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR( SQL_ID=>'the_SQL_ID', CURSOR_CHILD_NO=>the_child_number, FORMAT=>'ALL +OUTLINE +PEEKED_BINDS'));
You will see a new ‘Peeked Binds’ section in the plan report. For example:
Peeked Binds (identified by position): -------------------------------------- 1 - :B1 (NUMBER): 0 2 - :B2 (NUMBER): 200
This is particularly useful if you’ve identified a bad plan in the cursor cache and you want to reproduce it independently in SQLcl, SQL Plus or SQL Developer by supplying matching bind variable values for the SQL statement in the cursor cache.
Summary
If you want to save and share a plan, then…
Add PEEKED_BINDS if you want bind variable information.
More
Check out the self-contained test scripts for this post.
If you want more detail and more options for looking at plans, then check out Maria’s blog posts on DBMS_XPLAN and SQL Monitor.
If you want to generate plans and send a runnable test case to someone else, then check out Test Case Builder and the Oracle Support tool SQLT.
Comments and @vldbb welcome!