$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
$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;
SQL> select * from all_users where rownum<3;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 28-SEP-08
SYSTEM 5 28-SEP-08
------------------------------ ---------- ---------
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);
...