Previous | Table of Contents | Next

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:

  1. Reduce the number of logical reads to as little as possible. Make sure you are accessing exactly what you need to access, and nothing more. In other words, don't access tables or rows within tables unless you absolutely must. Take shortcuts whenever

Page 761

    possible. For example, suppose an EMPLOYEES table contains a foreign key on DEPT_ID to a DEPARTMENTS table and to a lookup table known as DEPT_NAMES. Now suppose a common query is to retrieve the DEPT_NAME given the DEPT_ID when retrieving an EMPLOYEES row using:
    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;
    
  1. Reduce the physical reads/logical reads ratio to as close to 0 as possible. First, ensure that the first goal of reducing the total logical reads is addressed. Second, make sure you are accessing what you need to access in the most efficient manner possible. Use what you've learned so far in this chapter. Plus, the remaining sections will aid you by providing more examples and scenarios. Third, if you've absolutely covered the first two tasks, increase your database buffer case in your SGA and if necessary, purchase more core memory. This is discussed further in Chapter 31, "Tuning Memory." On average, your ratio should be less than .10 for most statements. In the tkprof output example for a SELECT statement, the ratio is 13/304, or about .04. This is a good ratio. In fact, it corresponds to a 96 percent (100_.04) database buffer cache hit ratio.
  2. Reduce the logical reads/rows to as close to 0 as possible. For every logical read, you want to read as many rows as possible. Hence, the smaller the ratio is, the more efficient your reads are. This is an aspect of block efficiency, which is discussed in Chapter 32, "Tuning I/O." Clearly, if the ratio seems too high, for example only 1:8 (0.125) or larger, the db_block_size should probably be increased, because it appears to be too low relative to the large row sizes. In the previous tkprof example, the ratio is good (13/879), about .015, or smaller than 1:67. This means about 1000 rows for every 15 blocks read is retrieved, or about 67 rows per block. (If you were using a 16K Oracle db_block_size, this is about 246 bytes per row.)

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 way—leaving 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:

  1. Use these facilities mostly when you have multiple concurrent applications and want to pinpoint your most resource-intensive applications. Target those that appear to be most inefficient for your priority tuning efforts.
  2. Turn on SQL trace in init.ora or with ALTER SESSION.
  3. Run your application(s).
  4. Turn off SQL trace (if you're not expecting to reuse it soon).
  5. Run tkprof to format your trace file(s) into readable output file(s).
  6. Interpret the results.
  7. Tune your application or database as indicated. This often means rewriting the queries.
  8. Go back to step 1 and repeat until satisfied with step 5.

Understanding EXPLAIN PLAN

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 time—without 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

Previous | Table of Contents | Next