TKPROF With SQL TRACE

TKPROF is a utility at operating system level that translates oracle trace file into more readable format. It is used to analyze trace file in order to tune the application.

SQL> Alter Session Set SQL_TRACE = True;
Execute some queries.
SQL> Alter Session Set SQL_TRACE = False;

C:\> TKPROF C:\Oracle\Diag\TESTDB\TESTDB\Trace\    C:\temp.txt
This command will write trace file into temp.txt in more readable format.

Now, we can see the file temp.txt in any editor.

N.B. 1. Privilege ‘Alter Session’ is required to enable or disable SQL_TRACE.
Connect as SYSDBA and grant privilege to specific user.
SQL> Grant Alter Session To ;

2. Trace file location can be identified by value of parameter USER_DUMP_DEST.
Connect as SYSDBA and grant privilege to specific user.
SQL> Show Parameter USER_DUMP_DEST;