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>/trace2. 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
5. The last updated ".trc" file of above output is the one related to our SQL query analysis.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
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.
- https://docs.oracle.com/cd/B10500_01/server.920/a96533/sqltrace.htm
- https://oracle-base.com/articles/8i/tkprof-and-oracle-trace
- http://www.orafaq.com/wiki/TKProf
- http://www.dbspecialists.com/files/presentations/use_explain.html
Example output of TkProf:
TKPROF: Release 11.2.0.3.0 - Development on Sun Oct 25 14:41:58 2015Copyright (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.
