SQL tracing and Tkprof

Performance Tuning

If the value of the timed_statistics parameter is false, you set it to true with the following statement.
SQL> alter system set timed_statistics=true scope=both;

You can also set this parameter at the session level with the following statement:
SQL> alter session set timed_statistics=true
To find all trace files for the current instance, issue the following query:

SQL> select value from v$diag_info where name = ‘Diag Trace’;


In Oracle Database 11g, the default value of the max_dump_file_size parameter is unlimited, as you
can verify by issuing the following command:

SQL> show parameter max_dump_file_size

max_dump_file_size unlimited

An unlimited dump file size means that the file can grow as large as the operating system permits
Tracing a Specific SQL Statement:
Switch on the SQl_tracing:
SQL> alter session set events ‘sql_trace level 12’;
Execute your query:
SQL> select count(*) from sales;
Switch of the tracing:
SQL> alter session set events ‘sql_trace off’;
You can trace a specific SQL statement running in a different session by issuing an alter system set events statement:

If you have the SQL_ID of the session to trace:

SQL> alter system set events ‘sql_trace[sql:fb2yu0p1kgvhr] level 12’;
SQL> alter system set events ‘sql_trace[sql:fb2yu0p1kgvhr] off’;
If you have the sid and Serial# then use the below:
execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,

Enabling Tracing in Your Own Session
SQL>execute dbms_session.session_trace_enable(waits=>true, binds=> false);

To disable tracing, the user must execute the session_trace_disable procedure, as shown here:

SQL> execute dbms_session.session_trace_disable();
Issue the following statement to set an identifier for your trace files, before you start generating the trace:
SQL> Alter session set tracefile_identifier=’MyTraceFile’;

Queries which can help to find the correct session for tracing:

select a.sid, a.serial#, b.spid, b.pid, a.username, a.osuser, a.machine
from v$session a, v$process b where a.username IS NOT NULL
and a.paddr=b.addr;
select sid, serial#, username from v$session where status=íACTIVEí;

Once you get the SID and SERIAL# from the previous query, invoke the session_trace_enable
procedure of the DBMS_MONITOR package, as shown here:

SQL> execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,
PL/SQL procedure successfully completed.