Oracle Scratchpad

November 27, 2006


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.

[Update: I’ve published a proof of this special case in response to a comment from Howard Rogers below.]


  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,

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

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

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

  6. […] range scans of the indexes – and assuming they don’t run one after the other, since the deletes will actually take place). I’ve hinted the query for the function-based index because the OP said that Oracle always […]

    Pingback by FBI Delete | Oracle Scratchpad — February 22, 2018 @ 10:30 am BST Feb 22,2018 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by