Sqlplus Analysis Execution Plan

Reprint http://xm-koma.iteye.com/blog/1048451

For oracle9i, you need to manually set the Plustrace role, as follows:
1, in Sql>connect sys/password as SYSDBA (password: the password of the server where the database resides)

Run $oracle_home/sqlplus/admin/plustrce.sql under SYS user
sql>@ $ORACLE _home/sqlplus/admin/plustrce.sql

The actual content of this SQL is as follows:
Set echo on
Drop role Plustrace;
Create role Plustrace;
Grant SELECT on V_$sesstat to Plustrace;
Grant SELECT on V_$statname to Plustrace;
Grant SELECT on V_$mystat to Plustrace;
Grant Plustrace to DBA with admin option;
Set echo off

The above produces the Plustrace role

2, under the SYS user to give this role to the general user
Sql> Grant Plustrace to user name; (User name: The user name of the current database you logged into, such as: Bbass)
3. Then run $oracle_home/rdbms/admin/utlxplan.sql under the current user
sql>@ $ORACLE _home/rdbms/admin/utlxplan.sql It creates a plan_table that stores the results of parsing SQL statements.
4. Sql> set Timing on
To see when SQL statement execution takes
Sql> set autotrace on;
To view SQL execution plan analysis.

Notes on several common options for autotrace:
SET AUTOTRACE OFF----------------does not generate a AUTOTRACE report, which is the default mode
SET AUTOTRACE on EXPLAIN------AUTOTRACE only show optimizer execution path report
SET AUTOTRACE on STATISTICS--Displays only execution statistics
SET AUTOTRACE on-----------------contains execution plans and statistics
Set AUTOTRACE traceonly------Same set AUTOTRACE on, but does not display query

5, turn off the above features, run the following command in the Sql/plus window

Set time off; (Description: Turn off time display)
Set Autotrace off;

Sqlplus Analysis Execution Plan