TKPROF

The TKPROF program converts Oracle trace files into a more readable form.

TKPROF also determine the execution plans of SQL statement and creates a SQL script that stores the statistics in the database.

Steps and Commands to trace a session and run TKPROF :-

1.       Login to putty and connect to Oracle .

2.       To get the name of the trace file that will be generated run the following sql in the same session.

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';

The result of the above query gives the path and name of the trace file that will be generated for the session.

3.      Following parameter have to be set before tracing a session.

SET TIMING ON

4.      Following queries have to be fired to trace the session

alter session set timed_statistics=true;

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

alter session set max_dump_file_size = unlimited;

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file.

alter session set events '10046 trace name context forever, level 12';

Above command enables tracing for our specific user session.

5.      Execute all the SQLs or PL/SQLs for which we want to trace the session.

6.      Once we are done with tracing, we either exit our session to stop tracing or we can use the alter session command to stop it

ALTER SESSION SET EVENTS '10046 trace name context OFF';

7.      We can also set trace outside an applications session, i.e. if we don’t have access to the source code, we can remotely enable an extended trace for another session.

The command for the same is

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

The SID and SERIAL# can be fetched from V$SESSION.

Oracle will create the trace file in the server’s udump directory.

To stop the trace in the other session we can use the command as explained in step 6 or we use the following command: -

EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID, SERIAL_NUMBER);

8.      Connect to a duplicate session in Putty and go to the path where the trace file is generated (we get the path from the step 2). We can open the .trc file and check the content of it but the content is not in a readable format. Thus, we go for TKPROF.

9.      Go to the tkprof directory in the BIN directory of the Oracle and run the following command:-

TKPROF /u01/app/ORACLE/PRODUCT/12.2.0/ADMIN/TEST\UDUMP\TEST_ORA_4936.TRC  /u01/temp/OUTPUT.TXT

EXPLAIN=I102/ORACLE66@TEST TABLE=SYS.PLAN_TABLE SYS=NO WAITS=YES SORT=FCHELA,EXEELA,PRSELA

The output file name should be given with proper path where we want to create the .prf file.

NOTE: - We should have the 777 permission(permission to read, write, and execute) on all directories to run the TKPROF.