9.5 Autotrace

 < Day Day Up > 



The AUTOTRACE command is a SET command option in SQL*Plus. Every SQL statement issued in SQL*Plus can get some of the information that can be found using SQL Trace, TKPROF, and the EXPLAIN PLAN command. The syntax for AUTOTRACE is as follows:

SET AUTOTRACE { OFF | ON EXPLAIN | ON STATISTICS | ON |    TRACEONLY }

I find that if I am using AUTOTRACE I should probably be using the TRACEONLY option, which suppresses display of returned rows. Also AUTOTRACE will not function without the PLAN_TABLE in place. Here is an example of what AUTOTRACE produces in SQL*Plus.

SET AUTOTRACE TRACEONLY; SELECT coa.coa#, coa.text, SUM(gl.dr + gl.cr) FROM coa JOIN generalledger gl ON(coa.coa# = gl.coa#) WHERE coa.type = 'A' GROUP BY coa.coa#, coa.text;     Execution   Plan ------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=420              Card=139 Bytes=7    1    0   SORT (GROUP BY) (Cost=420 Card=139 Bytes=7784)    2    1     TABLE ACCESS (BY INDEX ROWID) OF                  'GENERALLEDGER'(Cost=2    3    2       NESTED LOOPS (Cost=30 Card=66243                   Bytes=3709608)    4    3         TABLE ACCESS (FULL) OF 'COA' (Cost=2                      Card=14 Bytes=3    5    3         INDEX (RANGE SCAN) OF 'XFK_COA#'                      (NON-UNIQUE)(Cost=     Statistics --------------------------------------------------------       0   recursive calls       0   db block gets    4876   consistent gets       0   physical reads       0   redo size     565   bytes sent via SQL*Net to client     499   bytes received via SQL*Net from client       2   SQL*Net roundtrips to/from client       1   sorts (memory)       0   sorts (disk)       2   rows processed

Autotrace can be useful.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net