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= ]
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=
