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

 

 

   
 

The TKPROF Report
Oracle Tips by Burleson
 

The output file is a report laid out with the SQL statement given first, then the timed statistics for PARSE, EXECUTE, and FETCH, followed by the execution plan if the EXPLAIN option was used.

UPDATE mrp_relief_interface
    SET request_id = :sql_req_id,
    process_status = 3
    WHERE   inventory_item_id  IN
        (SELECT inventory_item_id
         FROM   mrp_relief_interface rel2
         WHERE  rel2.request_id IS NULL
         AND    rel2.error_message IS NULL
         AND    rel2.relief_type = 1
         AND    rel2.process_status = 2
         AND    rownum <= :batch_size
         AND    NOT EXISTS
           (SELECT 'x'
            FROM   mrp_form_query
            WHERE  query_id = :in_process_items
                 AND    number1  = rel2.inventory_item_id))
    AND request_id IS NULL
    AND error_message IS NULL
    AND relief_type = 1
    AND process_status = 2

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        2      0.02       0.02          0          0          0          0
Execute      2    239.39    1003.16     274981    3792129        534        242
Fetch        0      0.00       0.00          0          0          0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total        4    239.41    1003.18     274981    3792129        534        242

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 41  (APPS)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  UPDATE STATEMENT   GOAL: RULE
      0   UPDATE OF 'MRP_RELIEF_INTERFACE'
    242    NESTED LOOPS
    234     VIEW
    242      SORT (UNIQUE)
    242       COUNT (STOPKEY)
    242        FILTER
1886651         TABLE ACCESS (BY INDEX ROWID) OF
                    'MRP_RELIEF_INTERFACE'
1886652          INDEX (RANGE SCAN) OF 'MRP_RELIEF_INTERFACE_N2'
                     (NON-UNIQUE)
    234         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'MRP_FORM_QUERY_N89' (NON-UNIQUE)
   3597     TABLE ACCESS (BY INDEX ROWID) OF 'MRP_RELIEF_INTERFACE'
   3831      INDEX (RANGE SCAN) OF 'MRP_RELIEF_INTERFACE_N2'
                 (NON-UNIQUE)

The best part of this report is the details showing all activity within the parse, execute, and fetch phases of SQL execution. For each of these phases, TKPROF reports timing and other statistical information as follows (bolded names that follow do not match the column headings in the listing—count, cpu, elapsed, disk, query, current, rows):

  • COUNT  This is the number of times that the SQL statement was parsed, executed, or fetched.

  • CPU  This is the total number of CPU seconds (in hundredths of a second) taken to perform each phase of the given SQL statement.

  • ELAPSED  This is the total amount of time (in hundredths of a second) from start to finish for each phase to be performed. This statistic can be viewed as "wall clock" time, but it does not include Net8 transmission time to a remote client.

  • DISK  This is the number of Oracle blocks read from disk for each phase. If fetch has high values, you should check for possible full-table scans in the execution plan.

  • QUERY  This is the total number of blocks fetched for consistent reads. If there are lots of insert, update, and delete transactions occurring when this trace file was generated, then this value can be high.

  • CURRENT  This is the number of blocks acquired for modifying transactions such as inserts, updates, and deletes.

  • ROWS  This is the number of rows operated on by either the execute or fetch phases.

Next, let’s look at another great script provided by Oracle’s Center of Expertise (COE). This script expands on the SQL Trace facility and also includes detailed information about the table and indexes that participate in the query.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

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