SQL tracing and Tkprof

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';

VALUE

--------------------------------------------------------------------------------

e:\app\sumit.tyagi\diag\rdbms\sumit\sumit\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

NAME                  VALUE

------------------------------------ ----------- ----------

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';

OR

If you have the sid and Serial# then use the below:

execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,

waits=>true,binds=>false);

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,

waits=>true,binds=>false);

PL/SQL procedure successfully completed.