sql trace and tkprof

Sql trace and tkprof are useful tools for understanding what sqls are being generated by a poorly performing report or application.

To invoke sql trace you can either alter your session using sql command
ALTER SESSION SET sql_trace = true;

To invoke sql trace in other users/application sessions you can use plsql package
dbms_system.set_sql_trace_in_session ( sid, serial#, TRUE 0 );

To find out the users sid and serial we select from v$session
select sid, serial# , status from v$session where program = < ' The Relevant EXE ' >;

example:
select sid, serial# , status from v$session where program = 'BbTSMain.exe' ;

If there are multiple sessions as there are for BbTSMain.exe then I can dynamically build a sql trace for this

example
spool trace.sql
select 'EXECUTE dbms_system.set_sql_trace_in_session (' ||sid || ','|| serial# || ', TRUE);' from v$session where program='BbTSMain.exe'
/
spool off

This will generate a script like

EXECUTE dbms_system.set_sql_trace_in_session (396,55252, TRUE);

@trace.sql
Now that sql tracing is turned on, it is time to look for our trace directory and tracefile
show parameter user_dump_dest

sort the directory for the *.trc files being actively modified

Now lets add some value to the sql trace by running the tracefile through the tkprof utility

tkprof explain=envision/@envision

example: tkprofe bbts_ora_2160.trc k.out explain=envision/123@envision

edit the outfile to see the sqls and the execution plans

You have now successfully mined the underlying sqls for the application