TKPROF

TKPROF

File name of trace file

select rtrim(c.value,'/')|| '/'||d.instance_name|| '_ora_'|| ltrim(to_char(a.spid))||'.trc' from v$process a, v$session b, v$parameter c, v$instance d where a.addr=b.paddr and b.audsid=sys_context('userenv','sessionid') and   c.name='user_dump_dest';

To set tracing on before running session
 

SQL> SET TIMING ON

TIMED_STATISTICS is set to TRUE to get timing information in our trace files. 

SQL> alter session set timed_statistics=true;

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file.
SQL> alter session set max_dump_file_size = unlimited;

Enable tracing for specific user session
SQL> alter session set events '10046 trace name context forever, level 12';

Execute SQLs or PL/SQLs to trace the session.

Stop tracing :-
SQL> alter session set events '10046 trace name context OFF';
 

For extended trace for another session.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(&SID, &SERIAL_NUMBER, 10046, &TRACELEVEL, ' ');

To stop extended trace for another session.
 

SQL> EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID, SERIAL_NUMBER);
The SID and SERIAL# can be fetched from V$SESSION.
 

Default trace file location - udump directory.

To convert trace file (.trc) tkprof (.txt)

Go to the trace file location

e.g.
/Oracle/PROD/db/tech_st/11.1.0/admin/PROD_erpdbsvr/diag/rdbms/prod/PROD/trace

Connect SQLPLUS
SQL> alter session set timed_statistics=true;
SQL> alter session set sql_trace=true;

NOW OPEN DUPLICATE SESSION
Eg. $  tkprof PROD_dbrm_22096.trc
TKPROF <trace filename>

IT WILL ASK FOR OUTPUT FILE NAME
Eg. PROD_dbrm_22096.txt

tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
example: tkprof file1234.trc file1234.out explain=<apps/apps>