322-325 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

An Example Of Using TKPROF

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.

Other Tuning Tips

This section discusses some situations that commonly require performance tuning, including several stumbling blocks that confuse new PL/SQL developers. One of the most common pitfalls by new developers is scanning too many records (often whole tables of records).

Full-Table Scans

Oracle uses a full-table scan of a table when it cannot use any of the indexes for the table. A full-table scan retrieves every row of data from a table. In large tables, this process can take a long time to execute and can considerably diminish performance of your applications.

If you haven t quite grasped the inefficiency of using a full-table scan, try imagining that you re running a video store and someone returns a movie. In order to make the returned movie accessible for other customers to rent, it has to be put back in the proper place.

If you have to walk around your store and check all the movies to find the proper place, you re performing the equivalent of a full-table scan. To avoid full-table scans in your DML commands, you should not:

Compare the values of two columns within the same table.

Pass any columns to a predefined or a stored function.

Use the IS NULL and IS NOT NULL comparisons against any column.

Use NOT IN comparisons against any column.

Use the LIKE operator against any column.

Use subqueries against nonindex columns.

Make comparisons against nonindexed columns.

There are several instances in which a full-table scan is as quick (or quicker) than the use of indexes. These occurrences include the following:

A DML statement must return more than 20 percent of the rows in a table.

The functionality of a statement requires that every row of a table will be processed .

The table is extremely small. It s difficult to put an exact size on a table, but if a table has more than 500-1,000 rows, a full-table scan will probably be less effective than an indexed reference to the table. You ll need to do some ad hoc testing to determine which approach is best.

A related performance problem occurs when the WHERE clause of a statement is incomplete, causing Oracle to scan too many rows of data. While this isn t as expensive as performing an unnecessary full-table scan, it still requires Oracle to waste resources.

Going back to the returned movied metaphor, knowing that the newly returned movie goes in the Adventure section is better than knowing only that it belongs somewhere in the store. However, you ll be able to replace the film much more quickly if you also happen to know the name of the movie.

In a SELECT statement, the category and name of the movie would be included in the WHERE clause.

Table of Contents