Search This Blog

Wednesday, May 28, 2008

Enable SQL Trace

What is involved in enabling SQL Trace and what INIT parameters should be set ? Here are my thoughts...

STATISTIC_LEVEL – Default Typical
(can be changed to ALL)
TIMED_STATISTICS
If STATISTICS_LEVEL set to basic then false
If STATISTICS_LEVEL set to typical or all then TRUE.

In order to enable SQL Trace
TIMED_STATISTICS should be TRUE.
MAX_DUMP_FILE_SIZE
– Default Unlimited in 11g (ranges from 0 to unlimited)
USER_DUMP_DEST (In 11g DIAGNOSTIC_DEST can be set)
– Set in init, can be changed.

Trace files are generated in USER_DUMP_DEST.
OR
$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/

To enable sql trace within a session
Alter session set SQL_TRACE = TRUE;
To disable sql trace from that session
Alter session set SQL_TRACE = FALSE;

No comments: