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.

There is just one special case where the SQL statement does not run; when the statement is a query, 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.