Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle tkprof Command Line Utility
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Andrew Kerber, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

tkprof

 

The tkprof command-line utility translates the plethora of performance raw data contained in trace files into a more human readable format. The trace file is a raw collection of Oracle process instrumentation data, with references and pointers that must be traversed and aggregated. While it is human readable, it’s it is not human comprehensible  – for it requires reformatting into a more meaningful and readable format. That’s That is exactly what tkprof does. Its command syntax is a follows:

 

$ tkprof file_name[,file_name…] [options]

 

Where the options are:

 

<                  waits=yes|no

<                  Record wait event summary

<                  print=N

<                  Lists only the first sorted SQL statement

<                  aggregate=Y|N

<                  Aggregate multiple users of same statement

<                  insert=filename

<                  Creates a file with inserts for the statistics

<                  sys=Y|N

<                  Include SYS and recursive SQL statements

<                  table=schema.table

<                  The explain table used during execution

<                 explain=user/password

<                  The user id and password for the explain user

<                  record=filename

<                  Creates a file all the non-recursive SQL statements

<                  width=N

<                  The output file width

<                  sort=sort_options[,…]

<                  Descending sort criteria for the output file

 

Where the sort options are:

 

<                  PRSCNT

<                  # parses

<                  PRSCPU

<                  Parse CPU time

<                  PRSELA

<                  Parse elapsed time

<                  PRSDSK

<                  Parse physical reads

<                  PRSQRY

<                  Parse consistent mode block reads

<                  PRSCU

<                  Parse current mode block reads

<                  PRSMIS

<                  Parse library cache misses

<                  EXECNT

<                  # executes

<                  EXECPU

<                  Execute CPU time

<                  EXEELA

<                  Execute elapsed time

<                  EXEDSK

<                  Execute physical reads

<                  EXEQRY

<                  Execute consistent mode block reads

<                  EXECU

<                  Execute current mode block reads

<                  EXEROW

<                  Execute # rows processed

<                  EXEMIS

<                  Execute library cache misses

<                  FCHCNT

<                  # fetches

<                  FCHCPU

<                  Fetch CPU time

<                  FCHELA

<                  Fetch elapsed time

<                  FCHDSK

<                  Fetch physical reads

<                  FCHQRY

<                  Fetch consistent mode block reads

<                  FCHCU

<                  Fetch current mode block reads

<                  FCHROW

<                  Fetch # rows processed

<                  USERID

<                  User ID that parsed the cursor

 

Thus, in this example tkprof is being asked to report on the contents of the one_big.trc file from the prior section to produce the one_big.lis report file.

 

C:\Temp>tkprof one_big.trc one_big.lis waits=yes explain=BERT/BERT sort=FCHCPU

 

Examine a portion of each of those files to see the before and after, going from unreadable to more easily readable. Note how the one_big.trc file seems like nothing more than a collection of print statements at various stages of the execution with some associated counters and numerical data. This data is much easier to read in the one_big.lis report file.

 

   one_big.trc consolidated trace file

 

PARSING IN CURSOR #3 len=543 dep=0 uid=42 oct=3 lid=42 tim=7241343753 hv=1890009883 ad='9ccd07d0' sqlid='fknz7njsafhsv'

select cu.firstname, cu.lastname, mr.rentaldate,

       mr.totalcharge, mt. title, mx.categoryname

from   customer cu, movierental mr, rentalitem ri,

       moviecopy mc, movietitle mt, moviecategory mx,

       (select avg(totalcharge) total from movierental) ar

where  cu.customerid = mr.customerid and

       mr.rentalid = ri.rentalid and

       ri.moviecopyid = mc.moviecopyid and

       mc.movieid = mt.movieid and

       mt.categoryid = mx.categoryid and

       mr.totalcharge <= ar.total

order by cu.lastname, cu.firstname, mr.rentaldate

END OF STMT

PARSE #3:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=7241343744

EXEC #3:c=0,e=161,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=7241344167

FETCH #3:c=109375,e=110611,p=0,cr=223,cu=0,mis=0,r=1,dep=0,og=1,tim=7241455269

FETCH #3:c=0,e=174,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241457210

FETCH #3:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241459544

FETCH #3:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241461165

FETCH #3:c=0,e=214,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241463715

FETCH #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241465140

FETCH #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241466770

FETCH #3:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241468074

FETCH #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241469784

FETCH #3:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241471425

FETCH #3:c=0,e=622,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241474291

FETCH #3:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241475635

FETCH #3:c=0,e=679,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=7241478094

 

      one_big.lis consolidated trace file report

 

select cu.firstname, cu.lastname, mr.rentaldate,

       mr.totalcharge, mt. title, mx.categoryname

from   customer cu, movierental mr, rentalitem ri,

       moviecopy mc, movietitle mt, moviecategory mx,

       (select avg(totalcharge) total from movierental) ar

where  cu.customerid = mr.customerid and

       mr.rentalid = ri.rentalid and

       ri.moviecopyid = mc.moviecopyid and

       mc.movieid = mt.movieid and

       mt.categoryid = mx.categoryid and

       mr.totalcharge <= ar.total

order by cu.lastname, cu.firstname, mr.rentaldate

 

call     count      cpu   elapsed       disk      query    current        rows

------- ------  ------- --------- ---------- ---------- ----------  ----------

Parse        4     0.00      0.00          0          0          0           0

Execute      8     0.00      0.00          0          0          0           0

Fetch     1824     0.60      0.96          0        892          0       27272

------- ------  ------- --------- ---------- ---------- ----------  ----------

total     1836     0.60      0.96          0        892          0       27272

 

Misses in library cache during parse: 0

Parsing user id: 42  (MOVIES)

 

Conclusion

 

In this chapter, a wide range of server-side utilities was examined that operate ostensibly at a level one step closer to the operating system than the normal, more SQL-based administrative commands. Often these commands are once removed like this as they operate entirely outside the database itself. The most useful of these commands were covered here and are the ones that will most likely be used with any great frequency.

 

Next to be detailed is SQL Management.


 


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.