Page 760
In this example, tkprof reformats the trace file (ora_1776.trc) into a readable, user-defined output file (ora_1776.out). An example of what the tkprof output might look like for the following SQL statement is shown in Listing 30.6:
SELECT * FROM EMPLOYEES WHERE DUTY >= 10;
Listing 30.6An Example of Formatted tkprof Output
Call count cpu elapsed disk query current rows Parse 1 0.17 0.35 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 1 0.06 0.10 13 304 0 3679 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: 8
So what does all this information mean and how can it help me tune my application? Well, as with most diagnostic tools, information is supplied, but interpretation, at least automatic interpretation, is not. First, examine each of the columnar statistics in our output file and what they mean, as discussed in Table 30.4.
Table 30.4Description of tkprof Statistics
Statistic | Description |
count | The number of parse, execute, or fetch calls. |
cpu | The actual cpu time in seconds (if parse = 0.00, then the statement was in the library cache). |
elapsed | The actual clock (or wall) time in seconds (should always be greater than or equal to cpu time). |
disk | Oracle blocks read from the datafiles; also known as physical reads. |
query | Oracle buffers read for consistent read from the rollback buffer cache, stored in the database buffer cache; also known as consistent gets. |
current | Oracle buffers read in current mode from the database buffer cache, also known as db block gets. |
rows | The number of rows accessed by the main driving (or outer) statement; shown for Fetch for SELECTs, otherwise shown for Execute (DML). |
Note the number of logical reads equals query+current. How should you interpret this? Your goals, as an application developer, programmer, or DBA should be to:
Page 761
SQL> SELECT E.LAST_NAME, E.FIRST_NAME,N.DEPT_NAME 2> FROM EMPLOYEES E, DEPARTMENTS D, DEPT_NAMES N 3> WHERE E.DEPT_ID = D.DEPT_ID 4> AND D.DEPT_ID = N.DEPT_ID;
SQL> SELECT E.LAST_NAME, E.FIRST_NAME,N.DEPT_NAME 2> FROM EMPLOYEES E, DEPT_NAMES N 3> WHERE E.DEPT_ID = N.DEPT_ID;
tkprof offers several other command line options in addition to the abbreviated example just presented; the most useful are discussed in Table 30.5. Interpreting execution plans is covered in more detail in the following section, "Understanding EXPLAIN PLAN."
Page 762
Table 30.5 tkprof Command Line Options
Option | Description |
explain=<user/password> | Enables you to run an EXPLAIN PLAN as a user; extremely useful because it provides the execution plans for each of the statements in the trace file. |
record=<file> | Enables you to specify a file to save the statements in the trace file; useful to save scripts with for individual statement testing. |
sys=NO | Suppresses recursive calls traced back through the sys user; this is useful because even though you can view these calls by default, they can't really be modified to help performance in any wayleaving them out simplifies the amount of output you have to filter through. |
To display these options on the screen, type TKPROFS HELP=YES at the command line.
CAUTION |
The explain tkprof command line option provides plans at tkprof runtime and is not based on the SQL trace run, so these two sets of outputs may not agree. |
To summarize using SQL trace and tkprof:
Whereas the SQL trace facility and tkprof program can provide useful statistics and execution plan information given many statements run in a single application sequence, EXPLAIN PLAN can give execution plan information for one statement at a timewithout having to run the statement. This is extremely useful in analyzing individual problem queries, especially if the queries are long-running queries. Why run a query and wait for the execution plan output via