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