A wrapper sqlplus script for dbms_xplan.display_cursor

24 Sep

When producing SQL execution plans with rowsource statistics, one has a choice whether or not specify the sql_id. It’s simpler to run dbms_stats.display_cursor without specifying sql_id, but it can be tricky, because then the plan for the last statement executed by this session will be displayed, and it can be not the statement you expected, especially when running the statement from a GUI like Toad. But even with sqlplus you can still get unexpected results, e.g. a plan for a hidden call to a dbms_output routine instead of the last statement you ran.

Below I describe a method to bypass this difficulty.

Method

Copy your SQL into a file (let’s call it slow_query.sql). Then in the same directory open a sqlplus session, and in it run the wrapping script as shown below:


set termout off
set serverout off
set linesize 200
set pagesize 9999
alter session set statistics_level = all;
@slow_query.sql;
spool slow_query_plan.lst
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
spool off

You can save it to a script and used it repeatedly with different queries (all that needs to be changed is the name of the script with the query on line 6).

The script produces a text file with nicely formatted plan with rowsource statistics, ready for analysis or posting. Note that since the output is suppressed

(for the reasons explained in the next section), in case of a SQL error you won’t see one — if that happens, just temporarily re-enable the output (set termout on,

set serverout on) to find out the cause of the error.

Explanation

If you’re not interested in how it’s working, then you can stop reading — just copy-paste the script below, and you’re all set.

For those who are more curios, I’ll describe the purpose of individual instructions in the script:

set termout off — suppress output of query rows on the screen. This is necessary, if the query produces many rows of output: displaying them would slow down the script considerably

set serverout off — I’m not entirely sure how, but this line takes care of wrapped sqlplus calls to dbms_output.disable or dbms_output.get_lines. If you don’t add this line, then you may end up having the plan for one of such call instead of your statement

set linesize 200 — this makes sure that lines aren’t wrapped

set pagesize 9999 — prevents headers of plan columns from appearing on each screen

alter session set statistics_level = all – this is the key to obtaining rowsource statistics (i.e. how many rows were produced at each step and how long the step took), without it the execution plan is almost useless for tuning

The rest of the script should be fairly self-explanatory.

About these ads

5 Responses to “A wrapper sqlplus script for dbms_xplan.display_cursor”

  1. Mladen Gogala September 26, 2012 at 9:07 pm #

    Unnecessarily bombastic title, providing nothing but the trivial content in return. Please, try not to waste my time with a beginner level stuff like this. Thanks.

    • savvinov September 27, 2012 at 5:14 am #

      Hi Mladen,

      thanks for your feedback. I’ve changed the title of the post — perhaps this way it’s more accurate. With all due respect, I don’t agree that it’s “trivial” as you describe it.

      If you don’t mind my asking — did you find any of my other blog posts useful? If so, I would appreciate if you pointed to some positive examples so that I could try and produce more content like that.

      Thanks!

      • Mladen Gogala September 27, 2012 at 7:50 pm #

        Thanks, I appreciate this. Dom Brooks also warned me that I am being unfair. That wasn’t my intention, thanks for changing the title.

      • savvinov September 28, 2012 at 3:31 am #

        No problem. Thanks for stopping by!

Trackbacks/Pingbacks

  1. Troubleshooting stuck queries « Oracle Diagnostician - November 13, 2012

    [...] my earlier post, I described a method of troubleshooting slow queries using dbms_xplan. While this method is all [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 344 other followers

%d bloggers like this: