Handy tkprof sql creating script using bash shell unix, linux with oracle environment

Oracle gives you this program called TKProf that reads your trace file and spits out somewhat meaningful output. My favorite option to tkprof is sort=prsela,fchela,exeela. This sorts the statements from longest running to shortest running. I prefer this format because I can concentrate on the top two or three statements for the most impact.

By tracing, I mean capturing all the SQL in a user’s session, or a SQL trace. As you can tell from the Oracle Performance Tuning Guide and Reference, there are many ways to enable tracing. Below are two methods I use as a DBA in my day-to-day routine.

Here is the script I often used to find the sql script using oracle trace utility for oracle applications forms, reports. Change the user id, password and database name to suit your environment.

Download TKprof shell script

Sometimes, a session is already underway and you need to start a trace midway through it’s execution. Here, you need to be a DBA and enable the 10046 event in the user’s session. This is a little more tricky since you have to know the sid and serial# from v$session in order to enable the tracing. For example,

Statements get written to the trace file when they are first encountered after the trace is started. The statement that is executing may not be in the final trace file.

Trace with TKProf
PARAMETERS
You need 2 database parameters to trace sessions: TIMED_STATISTICS and USER_DUMP_DEST.

TIMED_STATISTICS should be TRUE to use statistics.
Also possible so set this in a session:
SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;

USER_DUMP_DEST points to the directory on the server where the tracefiles are being written.

Enable trace
You can enable tracing in the following ways:
SQL*Plus:
SQL> alter session set sql_trace true;

PL/SQL:
dbms_session.set_sql_trace(TRUE);

DBA
SQL> execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
with: sid en serial# from the query:
Select username, sid, serial#, machine from v$session;

Oracle forms:
start forms with f45run32.exe statistics=yes
or make a PRE-FORM trigger with the statement:
forms_ddl(’alter session set sql_trace true’);

Oracle reports:
BEFORE-REPORT trigger with statement:
srw.do_sql(’alter session set sql_trace true’);

PRO*C
EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;

Use TKPROF
To make a tracefile readable, you need TKProf. Use the following command on the server:
TKPROF tracefile exportfile [explain=username/password] [table= …] [print= ] [insert= ] [sys= ] [record=..] [sort= ]

The statements between brackets are optional. Their meaning is:
explain=username/password: show an executionplan.
table= schema.tabelnaam : use this table for explain plan
print=integer restrict the number of shown SQL-statements.
insert=bestandsnaam Show SQL-statements and data within SQL statements
sys = NO Don’t show statements that are executed under the SYS-schema. Most of the times these are recursive SQL-statements that are less interesting.
Aggregate=NO Don’t aggregate SQL-statments that are executed more than once.
sort= Sort the SQL-statements. The option is made up of 2 parts:
part1:
Prs Sorteer op parse-values
Exe Sorteer op executie-values
fch Sorteer op fetch-values

Part 2:
Cnt Sort on number of calls
Cpu Sort on CPU-usage
Ela Sort on elapsed time
Dsk Sort on disk-reads
Qry Sort on consistent reads
Cu Sort on current reads
Mis Sort on library cache misses
row Sort on number of processed rows

I think Oracle uses tkprof as an acronym for Tom Kyte, Professor!