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 5: Analyze tkprof Output

This is the most difficult step in the process. Each tkprof output file contains a header, body, and summary section. The header simply displays the trace file name, definitions, and sort options selected. The body contains the performance metrics for SQL statements. The summary section contains an aggregate of performance statistics for all SQL statements in the file.

tkprof Output


tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002

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

Trace file: ORCL92_ora_3064.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
********************************************************************************


select *
from
employee where emp_id = 87933


call count cpu elapsed disk query current rows

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

Parse 10 0.00 0.03 0 0 0 0

Execute 10 0.00 0.00 0 0 0 0

Fetch 20 0.34 0.35 72 4730 0 10

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

total 40 0.34 0.39 72 4730 0 10


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL EMPLOYEE

********************************************************************************



The output displays a table of performance metrics after each unique SQL statement. Each row in the table corresponds to each of the three steps required in SQL processing.

1. Parse – The translation of the SQL into an execution plan. This step includes syntax checks, permissions, and all object dependencies.

2. Execute – The actual execution of the statement.

3. Fetch – The number of rows returned for a SELECT statement.
The table columns include the following:

• Count – The number of times a statement was parsed, executed, or fetched.

• CPU – The total CPU time in seconds for all parse, execute, or fetch calls.

• Elapsed – Total elapsed time in seconds for all parse, execute, or fetch calls.

• Disk – The number of physical disk reads from the datafiles for all parse, execute, or fetch calls.

• Query – The number of buffers retrieved for all parse, execute, or fetch calls.

• Current – The number of buffers retrieved in current mode (INSERT, UPDATE, or DELETE statements).
 
Observe from the tkprof output above that the SQL statement performed a TABLE ACCESS FULL, meaning a full-table scan. Full-table scans can degrade performance, especially when accessing a small subset of the data in a table. In this case, the query is selecting one row, yet all 100,000 rows in the table are scanned. This is a perfect situation to add an index on the EMP_ID column of the EMPLOYEE table:

SQL> CREATE INDEX emp_idx1 ON employee (emp_id);

Index created.

Let’s examine the performance of this query again, this time with the index enabled.

select *
from
employee where emp_id = 87933


call count cpu elapsed disk query current rows

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

Parse 1 0.03 0.05 1 1 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.03 3 4 0 1

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

total 4 0.03 0.09 4 5 0 1


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID EMPLOYEE
1 INDEX RANGE SCAN EMP_IDX1 (object id 30498)

********************************************************************************


The CPU speed improved by a multiple of 11 (.03 vs. .34) compared to the benchmark before the index was added.
 

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.