Nov 1, 2008

How to switch the Autotrace and Explain Plan functions of Oracle

$cd $ORACLE_HOME/sqlplus/admin
$ls
$sqlplus /nolog
sql> @connect sys/passwd as sysdba
sys@ora9i> @plustrce.sql
sys@ora9i> grant plustrace to public;
sys@ora9i> exit
$ cd $ORACLE_HOME/rdbms/admin
sql> @connect sys/passwd as sysdba
sys@ora9i> @utlxplan.sql
sys@ora9i> exit
 
SQL> set autotrace on
SQL> select * from all_users where rownum<3;
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 28-SEP-08
SYSTEM                                  5 28-SEP-08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'USER$'
   5    3         TABLE ACCESS (CLUSTER) OF 'TS$'
   6    5           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
   7    2       TABLE ACCESS (CLUSTER) OF 'TS$'
   8    7         INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
 

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        717  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
SQL> explain plan for select * from all_objects where rownum<3;
 
         Explained.
SQL> select * from table(dbms_xplan.display);
...