| |
 |
|
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.
|
