Oralce中的set autotrace

Autotrace Setting	Result
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	Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Setups Required for the Autotrace Report

To use this feature, the PLUSTRACE role must be granted to the user, such as HR. DBA privileges are required to grant the PLUSTRACE role.

Additionally, a PLAN_TABLE table must be created in the user's schema, such as the HR schema. For information on creating the PLAN_TABLE, see .

To create the PLUSTRACE role and grant it to the DBA, run the commands in from a SQL*Plus session.

Example 11-1 Creating the PLUSTRACE Role

CONNECT / AS SYSDBA 
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL 
drop role plustrace; 
Role dropped. 
create role plustrace; 
Role created. 
grant plustrace to dba with admin option; 
Grant succeeded.  

To grant the PLUSTRACE role to the HR user, run the commands in from a SQL*Plus session.

Example 11-2 Granting the PLUSTRACE Role

CONNECT / AS SYSDBA 
GRANT PLUSTRACE TO HR; 
Grant succeeded.

Database Statistic Name	Description
recursive calls	Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets	Number of times a CURRENT block was requested.
consistent gets	Number of times a consistent read was requested for a block.
physical reads	Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
redo size	Total amount of redo generated in bytes.
bytes sent via SQL*Net to client	Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client	Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client	Total number of Oracle Net messages sent to and received from the client.
sorts (memory)	Number of sort operations that were performed completely in memory and did not require any disk writes.
sorts (disk)	Number of sort operations that required at least one disk write.
rows processed	Number of rows processed during the operation.