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

 

 

   
   
 

An Example of Using TKPROF
Oracle Tips by Burleson
 

Let’s assume that we have generated a trace file, ora_90213.trc, containing the following DML statement:

SELECT last_name, first_name, middle_name
FROM   STUDENTS
WHERE  ssn = '999999999';

We’ll run TKPROF against the trace file using this command:

TKPROF ora_90213.trc ora_90213.out EXPLAIN=jschmoe/boogieman;

TKPROF generates an output file, ora_90213.out, containing this text:

SELECT last_name, first_name, middle_name
FROM   STUDENTS
WHERE  ssn = '999999999';
 
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.00    0.00      0       1        1       1
--------   -----   -----  -------   ----   -----   -------   ----
total        3     0.00    0.00      0       1        1       1
 
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 3
 
Rows     Explain Plan
----     -------------------------------------------------------
  0      SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
  1      TABLE ACCESS (BY ROWID) OF 'STUDENTS'
  1      INDEX (UNIQUE SCAN) OF 'STUDENTS_SSN' (UNIQUE)
****************************************************************

So, how do we read this data?

First, let’s look at the explain plan for the SELECT statement. By looking at the rightmost operation in the Explain Plan column and reading to the left, we can retrace the steps that Oracle took to execute the statement, as follows:

1.  First, a scan of the STUDENTS_SSN index was performed, which returned one row matching social security number 999-99-9999.

2.  The ROWID for this row was then used to fetch the values of the first_name, last_name, and middle_name columns from the STUDENTS table.

Next, let’s look at the statistics that TKPROF calculated.

  • Zero values for cpu and elapsed for the statement indicate that it was already parsed in the SGA when the statement was issued.

  • The query value of 1 indicates that one consistent block read was performed to return the result set for the statement.

  • The current value of 1 indicates that one current block read was performed to return the result set for the statement.

  • The rows value of 1 indicates that one row was returned from the statement.

That was pretty simple. A lot of the time, you won’t need this level of detail about the performance of your statements, but there will be times when you need this information to determine the source of a problem. For instance, high disk values would indicate that performance problems might be stemming from a slow or overworked hard disk drive.

SQL*Plus statements generally perform very well. However, there are some tuning tips with which you should be familiar in the event that one of your statements doesn’t meet your performance expectations.

     
               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

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. 

Hit Counter