Auto Trace

Submitted by dave on Mon, 03/01/2010 - 11:58

Setup:
If you do not have DBA rights to the database you want to trace on then you will need to contact your DBA about creating and assigning the permissions required to Auto Trace. 

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 14 14:30:26 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

< ... script output here ... >

SQL> GRANT PLUSTRACE TO SCOTT;

Grant succeeded.

Auto Trace Operation:

$ sqlplus scott@db

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 14 14:35:13 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET LINESIZE 3800
SQL> SET TIMING ON
SQL> SET AUTOTRACE TRACEONLY

Now feed SQLPlus the queries you want to get Trace information on. After every query finishes you will get:
1) Elapsed time for query execution (note since we are not returning results to a client this time is not included, consider it the time to produce the first row of results)
2) Explain Plan for the query with cost information
3) Resource Usage Statistics including consistent gets

Here is a ludicrously simple example:

SQL> select * from dual;

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        224  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

If you would like to dive further into the Explain Plan and the Estimated Costs vs the Actual Costs, check out the instructions on how to Gather Plan Statistics.