Diagnostic Destination in 11g

Introduced in 11g, DIAGNOSTIC_DEST initialization parameter can be used to set user, core, background dump destination. These had to be individually set or would default to $ORACLE_HOME/dbs.

DIAGNOSTIC_DEST now contains all the ADR (Automatic Diagnostic Repository) files which includes alerts, trace, core dumps etc.

It follows the following structure.

{diagnostic_dest}/diag/rdbms/{dbname}/{instname}

and breaks down further into

{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/alert
{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/incident
{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/trace
and so on...


Alert : Contains XML LOG (alert)
Incident : Core dumps and other incident dumps
Trace : Background (bdump) and alert_$ORACLE_SID.log

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;