AUTOTRACE options and examplesThis is a featured page


Platform: Oracle App: Tool: SQL*Plus
DB Ver: 9.2 App Ver: Tool Ver: 9.2.0.8
Revision Date: 29-May-2006 Keywords: REF, REFERENCE, AUTOTRACE OPTIONS
Reference Notes
The following are options for the autotrace utility:

  1. SET AUTOTRACE OFF - This is the default. No report is generated.
  2. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows the optimizer execution path.
  3. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows SQL execution stats.
  4. SET AUTOTRACE ON - The AUTOTRACE report shows both the execution path and the stats.
  5. SET AUTOTRACE TRACEONLY - Like AUTOTRACE ON, except suppresses the query output.
Examples are shown below:

apps@VIS> set autotrace off
apps@VIS> select count(*) from big_table;

COUNT(*)
----------
266811
apps@VIS> set autotrace on explain
apps@VIS> select count(*) from big_table;

COUNT(*)
----------
266811


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=547 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=547 Card=255965
)
apps@VIS> set autotrace on statistics
apps@VIS> select count(*) from big_table;

COUNT(*)
----------
266811


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3557 consistent gets
3532 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
apps@VIS> set autotrace on
apps@VIS> select count(*) from big_table;

COUNT(*)
----------
266811


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=547 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=547 Card=255965
)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3557 consistent gets
3532 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
apps@VIS> set autotrace traceonly
apps@VIS> select count(*) from big_table;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=547 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=547 Card=255965
)





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


consultium
consultium 		Latest page update: made by consultium , Oct 9 2009, 11:44 AM EDT (about this update About This Update consultium Edited by consultium

266 words added

view changes
- complete history)
Keyword tags: AUTOTRACE OPTIONS REF REFERENCE
More Info: links to this page

Threads for this page

There are no threads for this page.  Be the first to start a new thread.

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)
Analytical Functions (1) Index Of Oracle SQL Commands Standard Utility scripts
Login Script Raw Trace Data Stat meanings Structure of an Extended SQL Trace file
Effective MULTIBLOCK READ COUNT values used in cost calculations What IS the cost of a query and what does it mean? Oracle timing units of measure
Creating Reports