Oracle Database Administration: The Essential Refe

SQL*Plus AUTOTRACE

The SQL*Plus AUTOTRACE facility was implemented effective with SQL*Plus 3.3 (shipped with Oracle7 Release 7.3). It provides an easier interface to get EXPLAIN PLAN information. AUTOTRACE still requires you to have a PLAN_TABLE, and it requires you to have the PLUSTRACE role. The PLUSTRACE role and its grants are defined in the file $ORACLE_HOME/sqlplus/admin/plustrce.sql, which must be run from SYS.

The full syntax for enabling AUTOTRACE is:

SET AUTOTRACE {OFF | ON | TRACEONLY} [EXP[LAIN]] [STAT[ISTICS]]

OFF

Turns off AUTOTRACE

ON

Turns on AUTOTRACE

TRACEONLY

Forces AUTOTRACE to not display the query output

EXPLAIN

Forces AUTOTRACE to only show the execution plan

STATISTICS

Forces AUTOTRACE to only show the statistics

An example of running AUTOTRACE follows:

SQL> set AUTOTRACE TRACEONLY
SQL> SELECT emp.ename,emp.empno,emp.job,dept.dname
  2  FROM emp,dept
  3  WHERE emp.deptno = dept.deptno;

14 rows selected.

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=CHOOSE                       
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'EMP'                        
   3    1     TABLE ACCESS (BY ROWID) OF 'DEPT'                   
   4    3       INDEX (RANGE SCAN) OF 'DEPTI' (NON-UNIQUE)        

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