Autotrace in SQLPLUS

Autotrace in SQLPLUS
What is AUTOTRACE
In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.How to configure AUTOTRACE in SQLPLUS for database user
1. Change directory path and connect with SYSTEM user to databaseC:\>cd c:\oracle\product\10.1.0\db_1\rdbms\adminC:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nologSQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:08:20 2007(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL> conn system/managerConnected.2. run UTLXPLAN.SQL script for plan table.SQL> @utlxplan.sql3. Create public synonym for plan table or grant all privilege.SQL> create public synonym plan_table for plan_table;SQL> grant all on plan_table to public;Grant succeeded.4. Exit and again change directory path and connect with SYS user to databaseSQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - ProductionWith the Partitioning optionJServer Release 9.0.1.1.1 - ProductionC:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>cd\C:\>cd c:\oracle\product\10.1.0\db_1\sqlplus\adminC:\Oracle\product\10.1.0\Db_1\sqlplus\admin>sqlplus /nologSQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:12:07 2007(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL> conn sys as sysdbaEnter password:Connected.5. run plustrce script and script must be run with SYS user.SQL> @plustrceSQL>SQL> drop role plustrace;Role dropped.SQL> create role plustrace;Role created.SQL>SQL> grant select on v_$sesstat to plustrace;Grant succeeded.SQL> grant select on v_$statname to plustrace;Grant succeeded.SQL> grant select on v_$mystat to plustrace;Grant succeeded.SQL> grant plustrace to dba with admin option;Grant succeeded.SQL> set echo off6. Grant plustrace role to public.SQL> grant plustrace to public;Grant succeeded.Configuring the Autotrace Report
SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is thedefault. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizerexecution path. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQLstatement execution statistics. SET AUTOTRACE ON - The AUTOTRACE report includes both theoptimizer execution path and the SQLstatement execution statistics. SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses theprinting of the user's query output, if any.Database Statistics for SQL Statements
recursive callsNumber 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 getsNumber of times a CURRENT block was requested.consistent getsNumber of times a consistent read was requested for a block.physical readsTotal number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.redo sizeTotal amount of redo generated in bytes.bytes sent via SQL*Net to clientTotal number of bytes sent to the client from the foreground processes.bytes received via SQL*Net from clientTotal number of bytes received from the client over Oracle Net.SQL*Net roundtrips to/from clientTotal 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 processedNumber of rows processed during the operation.EXAMPLE
SQL> set autotrace onSQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONExecution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'DEPT'Statistics----------------------------------------------------------0 recursive calls2 db block gets2 consistent gets0 physical reads0 redo size702 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)4 rows processedSQL> set autotrace traceonlySQL> select * from dept;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'DEPT'Statistics----------------------------------------------------------0 recursive calls2 db block gets2 consistent gets0 physical reads0 redo size702 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)4 rows processedSQL> set autotrace trace explainSQL> select * from dept;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'DEPT'SQL> set autotrace trace statisticsSQL> select * from dept;Statistics----------------------------------------------------------0 recursive calls2 db block gets2 consistent gets0 physical reads0 redo size702 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)4 rows processed