www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  How to use Trace/ TKProf

Performance

Info on performance
dbms_profiler explained
How to use hints
Undocumented hints
How to Trace sessions
Create statistics


  OraDev.com

Trace with TKProf

PARAMETERS

You need 2 databaseparameters to trace sessions: TIMED_STATISTICS and USER_DUMP_DEST.

TIMED_STATISTICS should be TRUE to use statistics.
Also possible so set this in a session:
SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;

USER_DUMP_DEST points to the directory on the server where the tracefiles are being written.

Enable trace

You can enable tracing in the following ways:
SQL*Plus:
SQL> alter session set sql_trace true;


PL/SQL:
dbms_session.set_sql_trace(TRUE);

DBA
SQL> execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
with: sid en serial# from the query:
Select username, sid, serial#, machine from v$session;

Oracle forms:
start forms with f45run32.exe statistics=yes
or make a PRE-FORM trigger with the statement:
forms_ddl('alter session set sql_trace true');

Oracle reports:
BEFORE-REPORT trigger with statement:
srw.do_sql('alter session set sql_trace true');

PRO*C
EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;

Use TKPROF

To make a tracefile readable, you need TKProf. Use the following command on the server:
TKPROF tracefile exportfile [explain=username/password] [table= ] [print= ] [insert= ] [sys= ] [record=..] [sort= ]

Example:
tkprof ora_12345.trc output.txt explain=scott/tiger

The statements between brackets are optional. Their meaning is:
explain=username/password: show an executionplan.
table= schema.tabelnaam : use this table for explain plan
print=integer restrict the number of shown SQL-statements.
insert=bestandsnaam Show SQL-statements and data within SQL statements
sys = NO Don't show statements that are executed under the SYS-schema. Most of the times these are recursive SQL-statements that are less interesting.
Aggregate=NO Don't aggregate SQL-statments that are executed more than once.
sort=