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';
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';
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, ' ');
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.
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>
/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>
No comments:
Post a Comment