Oracle Scratchpad

November 27, 2006

Autotrace

Filed under: Execution plans,Troubleshooting — Jonathan Lewis @ 9:48 am BST Nov 27,2006

A quick tip if you use autotrace in versions of Oracle prior to 10.2.

There are a number of column definitions that affect the output of autotrace, and you might want to set these in a login.sql, or glogin.sql script so that you have a better chance of getting a tidy output. For example, my typical login.sql holds the lines:

set linesize 132 

column parent_id_plus_exp	format 999 
column id_plus_exp		format 990 
column plan_plus_exp 		format a90 
column other_plus_exp		format a90 
column other_tag_plus_exp	format a29 
column object_node_plus_exp	format a14

The last one on the list (object_node_plus_exp) covers a multitude of sins. My setting here reflects its use for parallel execution – you may want to increase the length if you are investigating distributed queries.

In passing, the traceonly option for autotrace does cause confusion. Its purpose is to display only the output of the autotrace command – in general your SQL*Plus session will still run the SQL statement.

There is just one special case where the SQL statement does not run; when the statement is a query (i.e. select), and you have issued:

set autotrace traceonly explain

In this case, the autotrace facility simply does an explain plan, and reports the results of the plan_table.

5 Comments »

  1. Could you post something to prove the truth of that last sentence?

    For a very long time, Oracle courseware has taught that autotrace always executes, its one drawback compared with, say, explain plan (but also its advantage, in that the plan you see is the one that was actually used, not a hypothetical one).

    I’ve gone along with that without thinking about it for years… and now you make me do a double-take!

    In the nicest possible way: evidence please!

    Comment by Howard Rogers — November 29, 2006 @ 5:39 am BST Nov 29,2006 | Reply

  2. Howard, I have created a separate page which I hope demonstrates this point.
    Of course, 10gR2 does give us the option to get the “real” execution plan very easily – but at the expense of running the query – by making a call to dbms_xplan.display_cursor().

    Comment by Jonathan Lewis — November 29, 2006 @ 9:31 am BST Nov 29,2006 | Reply

  3. Thank you very much for that Jonathan!

    You live and learn, I guess.

    At least my comfort levels are restored to some degree of normality by noting the very special circumstances under which the ‘hypothetical only’ stuff happens.

    Thanks for taking the time to indulge me.

    Comment by Howard Rogers — November 29, 2006 @ 9:57 pm BST Nov 29,2006 | Reply

  4. Hi,

    I want to know is it possible to store the traces in a file. so that I can use auto trace for many procedures and I’ll see all the traces in one file.

    Please help me.

    Thanks in advance,
    Amarnath

    Comment by Amarnath — March 12, 2007 @ 4:28 am BST Mar 12,2007 | Reply

  5. An oldie, but a goodie. There are still 9.2.0.6 databases out there – working on one now.
    Thanks Jonathan.

    Comment by Jared — December 2, 2011 @ 6:52 pm BST Dec 2,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,003 other followers