What is TkProf?
TkProf is an Oracle tool which helps to convert Oracle trace files to human readable format.
Where to find the tool?
TkProf tool is located in
%ORACLE_HOME%/bin folder.
How to use it?
0. login to SQLPlus.
1. First find the trace file dump folder using following sql command.
select value from v$parameter where name = 'user_dump_dest';
The output:
/<PATH TO YOUR DB>/<YOUR_SID>/trace
2. Then enable the sqltrace (This can affect to performance).
alter session set sql_trace=true;
3. Run your sql.
4. Check the trace file by doing a "ls -ltr
| grep _ora_". You can run a shell command within SQLPlus by prepending "!" to the command.
SQL> !ls -ltr /<PATH TO YOUR DB>/trace | grep _ora_
-rw-rw---- 1 oracleuser dbagroup 1386 Sep 28 14:30 YOUR_SID_ora_27758.trm
-rw-rw---- 1 oracleuser dbagroup 3628 Sep 28 14:30 YOUR_SID_ora_27758.trc
-rw-rw---- 1 oracleuser dbagroup 1359 Sep 28 14:55 YOUR_SID_ora_29733.trm
-rw-rw---- 1 oracleuser dbagroup 3779 Sep 28 14:55 YOUR_SID_ora_29733.trc
-rw-rw---- 1 oracleuser dbagroup 1355 Oct 21 16:07 YOUR_SID_ora_32487.trm
-rw-rw---- 1 oracleuser dbagroup 3613 Oct 21 16:07 YOUR_SID_ora_32487.trc
-rw-rw---- 1 oracleuser dbagroup 122 Oct 25 14:38 YOUR_SID_ora_6253.trm
-rw-rw---- 1 oracleuser dbagroup 3506 Oct 25 14:38 YOUR_SID_ora_6253.trc
5. The last updated ".trc" file of above output is the one related to our SQL query analysis.
6. Run the tkprof command to get the formatted result. The tkprof tool is located at
bin folder of %
ORACLE_HOME% folder.
/home/oracleuser[YOUR_SID]:tkprof /<PATH TO YOUR DB>/YOUR_SID/trace/YOUR_SID_ora_6253.trc tkprof_out.txt
TKPROF: Release 11.2.0.3.0 - Development on Sun Oct 25 14:41:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
/home/oracleuser[YOUR_SID]:less tkprof_out_3.txt
More Details:
Please see following websites for more detailed information about how to use TkProf tool and analysing the output.
Example output of TkProf:
TKPROF: Release 11.2.0.3.0 - Development on Sun Oct 25 14:41:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /<PATH TO YOUR DB>/YOUR_SID/trace/<YOUR_SID>_ora_6253.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 4tk6t8tfsfqbf Plan Hash: 0
alter session set sql_trace=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.03 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
********************************************************************************
<YOUR_SQL_QUERY>
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 53 1 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 53 1 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 NESTED LOOPS (cr=53 pr=0 pw=0 time=14025 us)
0 0 0 NESTED LOOPS (cr=53 pr=0 pw=0 time=14018 us cost=12 size=162 card=1)
2502 2502 2502 PARTITION HASH SINGLE PARTITION: 1 1 (cr=53 pr=0 pw=0 time=10393 us cost=12 size=170136 card=2502)
2502 2502 2502 TABLE ACCESS FULL <YOUR_TABLE_1> PARTITION: 1 1 (cr=53 pr=0 pw=0 time=5007 us cost=12 size=170136 card=2502)
0 0 0 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=6275 us cost=0 size=0 card=1)
0 0 0 INDEX UNIQUE SCAN <A_PK_INDEX> PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 15488)
0 0 0 TABLE ACCESS BY LOCAL INDEX ROWID <ANOTHER_TABLE_2> PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=94 card=1)
********************************************************************************
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.03 0 0 0 0
Fetch 1 0.01 0.01 0 53 1 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.05 0 53 1 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /<PATH TO YOUR DB>/YOUR_SID/trace/<YOUR_SID>_ora_6253.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
59 lines in trace file.
78 elapsed seconds in trace file.
