Enable Autotrace

Autotrace is a very useful feature that used to trace the cost of a sql quarry and execution plane oracle used for that quarry.

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To solve this do the followings:

1. Run plustrce.sql through SYS schema (user: system) if it did not run before. the location of plustrce.sql is: ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql . ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql sql> @ .../plustrce.sql
   
2. Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;

Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table , execure UTLXPLAN.sql location of the file is:

1. ON NT :- $ORACLE_HOME\rdbms\admin\utlxplan.sql
2. ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql

sql> @../utlxplan.sql


A User can use the AUTOTRACE options as follows:

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


Meaning Statistics
----------------------------------------------------------
0 '''recursive calls''' - The recursive calls Oracle metric maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.
0 '''db block gets''' -The db block gets Oracle metric statistic tracks the number of blocks obtained in CURRENT mode, directly from the RAM data block buffer.
504 '''consistent gets''' - number of logical read
0 physical reads
0 redo size
727 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed