Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips

Analyzing tkprof Results

 

Step 4: Locate Trace File and Execute tkprof

Locating the file is easy because the script above gives us the file name.  tkprof will format the raw trace file, although the file is somewhat readable without tkprof

 

Raw Trace File

 

PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535446373886 hv=159129
656 ad='12cbbe70'
 select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=37469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=535446373874
EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535446375834
FETCH #1:c=31250,e=42564,p=10,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535446418910
FETCH #1:c=0,e=3852,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535446424026
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535448474894 hv=159129
656 ad='12cbbe70'
 select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448474882
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448476767
FETCH #1:c=31250,e=30553,p=12,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535448507870
FETCH #1:c=15625,e=3832,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535448512927
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449209407 hv=159129
656 ad='12cbbe70'
 select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449209395
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449211302
FETCH #1:c=31250,e=32623,p=8,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449244513
FETCH #1:c=15625,e=3918,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449249648
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449801444 hv=159129
656 ad='12cbbe70'
 select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449801433
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449803310
FETCH #1:c=31250,e=31503,p=7,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449835358
FETCH #1:c=15625,e=4039,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449840721
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535450369301 hv=159129
656 ad='12cbbe70'
 select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450369290
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450371203
FETCH #1:c=15625,e=28362,p=5,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535450400245
FETCH #1:c=15625,e=4333,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535450405578
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================

 

With minimal effort, a programmer could create a trace file parser and formatter similar to tkprof that provides the trace data in a format even more suitable for analysis.

 

The tkprof command can now be executed from the operating system prompt.

 

C:\Oracle\admin\ORCL92\udump>tkprof ORCL92_ora_3064.trc output.txt insert=tkprof.sql record=Allsql.sql

 

tkprof: Release 9.2.0.1.0 - Production on Thu Dec 26 13:22:29 2002

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Based on the command above, tkprof will process the file ORCL92_ora_3064.trc and format the results in the file output.txt.  Two other files were also created (tkprof.sql, allsql.sql) that will be discussed later.


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.