Tuning Oracle SQL

Oracle Trace
- Can trace a query , a batch process or entire system
- used to measure timing statistics

Init.Ora
- TIMED_STATISTICS = TRUE
- MAX_DUMP_FILE_SIZE =
USER_DUMP_DEST

>alter session set SQL_TRACE=true
>run the query
>alter session set SQL_TRACE=face

Run tkprof to make the trace file readable
tkprof *.trc *.out explain=user/pass

AUTOTRACE ON

V$SQLAREA
disk reads and memory reads