11G Oracle TKPROF & SQL_TRACE Performance investigation.

System Privilege To Enable and Disable Tracing

GRANT/REVOKE alter session TO uwclass;

Add time_statistics in trace file.

ALTER SYSTEM SET timed_statistics=TRUE;

SQL COMMANDS

ALTER SESSION SET sql_trace = TRUE;
ALTER SESSION SET sql_trace = FALSE;

Using PL/SQL CODE

trace enable

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);

trace disable

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);

Location of TKRPROF Utl.

$ORACLE_HOME/rdbms/admin/utltkprf.sql

Trace file location

$ORACLE_BASE/diag/orabase/orabase/trace

Sqlplus commandline

TKPROF c: emp\orabase_ora_1492.trc c: emp race_out.txt

To enable the SQL Trace Session from SQL prompt

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);

To disable tracing specified in the previous step:

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634); Taken from, for more details trace_tkprof