Saturday, April 2, 2016

Using Tkprof tool for Analysing Oracle trace files

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.

 


~~~


No comments: