Tuesday, April 5, 2011

Setting Up AUTOTRACE in SQL*Plus

AUTOTRACE is a facility  within  SQL*Plus that  shows us the explain  plan of  the  queries that we  have executed  and the resources they used. This topic makes extensive  use  of  the AUTOTRACE facility.There is more than  one way to  get AUTOTRACE configured. This is what I like to do to get AUTOTRACE working :
1.) cd  $ORACLE_HOME\rdbms\admin
2.) log into SQL*Plus as SYSTEM
3.) Run @utlxplan
4.) run the below command 


SQL> create public synonym plan_table for plan_table ; 
SQL> grant all on plan_table to public ;
We can  automatically get  a report  on the  execution path  used by the  SQL optimizer  and  the statement execution statistics. The report is  generated  after  successful  SQL DML ( i.e., select,delete,update,merge and insert ) statements. It  is  useful  for monitoring and  tuning the  performance of these  statements. We can control the  report by  setting  AUTOTRACE  system  variable.

1.) SET AUTOTRACE OFF : No AUTOTRACE report is generated. This is the default.
2.) SET AUTOTRACE ON : The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics. Here is an demo 
SQL> set autotrace on 
SQL>select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;
LAST_NAME              SALARY               JOB_TITLE
----------------             ----------          -----------------------
King                           24000              President
Kochhar                     17000              Administration Vice President
De Haan                    17000              Administration Vice President
Russell                      14000               Sales Manager
Partners                    13500               Sales Manager
Hartstein                   13000               Marketing Manager
6 rows selected.


The statement can be automatically traced when it is run :
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)

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


3.) SET AUTOTRACE ON STATISTICS :  The AUTOTRACE report shows only the SQL statement execution statistics.
SQL>set autotrace on statistics
SQL>select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;

LAST_NAME              SALARY              JOB_TITLE
----------------            ----------            -----------------------
King                           24000              President
Kochhar                     17000              Administration Vice President
De Haan                    17000               Administration Vice President
Russell                      14000               Sales Manager
Partners                    13500               Sales Manager
Hartstein                   13000               Marketing Manager
6 rows selected.

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

4.) SET AUTOTRACE ON EXPLAIN : The AUTOTRACE report shows only the optimizer execution Path.
SQL> set autotrace on explain 
SQL> select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;
LAST_NAME              SALARY             JOB_TITLE
----------------          ----------           -----------------------
King                           24000              President
Kochhar                     17000              Administration Vice President
De Haan                    17000               Administration Vice President
Russell                      14000               Sales Manager
Partners                    13500               Sales Manager
Hartstein                   13000               Marketing Manager
6 rows selected.

Execution Plan :
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)
5.) SET AUTOTRACE TRACEONLY : This is like SET AUTOTRACE ON, but it suppresses the printing of the user’s query output, if any.
SQL >  set autotrace traceonly 
Execution Plan 
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS' 
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)
Statistics : 
---------------------------------------------------------
          0  recursive calls
          2  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        848  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
6  rows processed

Enjoy       :)

No comments: