How to generate trace file for a query in oracle database- tkprof creation for a query

 Most of the situation we may need to create a trace file for a query or a session which include PL/SQL block.

We can use below method to create trace file for the sql statements or PL/SQL blocks.

1. Enable tracing in a session where you are going to run a query with high level tracing 12.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

2. we will have number of tracefiles in database diag locaions, to identify the trace file generated by this session, you can set the identifier as below, so that file name includes identifier.

alter session set tracefile_identifier = 'mysessiontrace';

3.  run the query or PL/SQL block.

4. turn off the tracing.

alter session set events '10046 trace name context off';

you can find the trace file generated with name mysessiontrace, search as below.

ls -lrth *mysessiontrace*

If you want to generate tkprof of this trace file. use below 

tkprof orcl_mysessiontrace.90222_53078.trc orcl_mysessiontrace.90222_53078.txt SYS=NO