AUTOTRACE Option in sqlplus

we can control the report by setting the AUTOTRACE system variable.

SET AUTOTRACE OFF : No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN : The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS : The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON : The AUTOTRACE report includes both the
optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY : Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.



The user must have the PLUSTRACE role granted and a PLAN_TABLE table created in your schema. For creating the table we can execute the following script,
@$ORACLE_HOME/rdbms/admin/utlxplan.sql