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;
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:
Post a Comment