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:
- 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
- Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;
- ON NT :- $ORACLE_HOME\rdbms\admin\utlxplan.sql
- ON UNIX :-$ORACLE_HOME/rdbms/admin/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.
- 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
No comments:
Post a Comment