Better Autotrace setup.

This example shows how to setup the autotrace,possible options and understanding out of the output.

Time Needed : 20min

How to setup autotrace.

1. cd to $ORACLE_HOME/rdbms/admin
2. Create a user with create table and create public synonym priv.
3. make plan_table under this user(for details see the note in the performance tab. – better way to create plan_table)
4. login as sysdba and run @plustrace.sql
5. grant plustrace to public.

Autotrace options

1. set autotrace off – set the tracing off
2. set autotrace on explain : Runs as normal and shows the execution plan.
3. set autotrace on statistics – Runs as normal and shows the statistics.
4. set autotrace on – same as 3 and 4 together.
5. set autotrace traceonly – same as 4 but does not print the output. GOOD ONE if you don’t interested in o/p.
6. set autotrace traceonly statistics – shows only execution statistics does not print output.
7. set autotrace traceonly explain – show only query plan. for select does not run the sql. for insert/update/delete it still runs.

Good formatting option(better to copy in login.sql)
column id_plus_exp format 990 heading I
column parent_id_plus_exp format 990 heading P
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44

if you done need then can use
column object_node_plus_exp NOPRINT
column other_tag_plus_exp NOPRINT
column other_plus_exp NOPRINT

Analysis of Autotrace

Recursive calls : Number of SQL statement execute in order to execute your sql. If you rerun this should go to . Else it might have trigger or something that fires more sql.(required for parsing,requesting additional space,temp space etc.) in locally managed tablespace recursive sql gets less for space mgmt.

DB Block Gets : Total Number of blocks read from buffer cache in current mode.
1. Normally its 0 for select. but may be 4+ for reading segment header.
2. Non zero for DMLS.

Consistent Gets : Number of times the block requested in buffer cache.May require read aside to rollback.Normally its a/r+N(what is a,r,N ?) = Total records/Record to read+blocks. Last two are most important. With various different plan try to reduce this. set arraysize 10,100,1000,5000 and see the effect.

Physical Reads : This is the disk read. Should get zero in second iteration.This may be done for sorting as well if it goes to memory because of not enough sort_area_size. See the sorts section also to confirm this. INSERT /*+ append */(beneficial in noarchivelo,or nologging on table but nologging needs db backup next time).even the indexes can generate redo. so mark index unusuable and rebuild nologging after load(parallel too).

Redo size : Total Amount of Redo generated in terms of bytes.

Bytes Sent via SQL*Net to client : Total Number of bytes sent to client from server.

Bytes Received via SQL*Net from client : Total Number of Bytes Received from client.

SQL*Net roundtrips to/from client. : This includes round trip featches for mulitple-row set. look at arraysize to reduce this.

Sorts(Memory) : Sorts done in user’s session memory.controlled via sort_area_size.(auto via pga_aggregate_target)

Sorts(disk) : Sort that used the disk because the sort exceeded the memory.

Rows Processed : Rows processed by modification/select statement.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: