Autotrace

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.

Warning

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.]