Oracle DBA: SET AUTOTRACE Command

Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!

Code:
The SET AUTOTRACE Command

In SQL*Plus 3.3 there is a little known command (at least I didn't know about it until recently) called SET AUTOTRACE. It is documented in the newest SQL*Plus document set, but who really reads the whole document set for changes? Well I did not. It is very simple to use. Just type the command:

SET AUTOTRACE ON

And then run your select statement. Example:

SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 /

DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
30 SALES 7900 JAMES
30 SALES 7521 WARD

14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

There are also some other options, for example there is a TRACEONLY option which supresses the SQL output. See the
SQL*Plus 3.3 manual for a full description.

Some setup issues:

If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to
make this work:

1.Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like
$ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
2.You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:

$ORACLE_HOME/plus33/Plustrce.sql

It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.