General Guidelines

So far, you have learned that there are various execution plans and that some are better than others. You also know that you may need to try several alternatives before you find the one that works best for you. However, you do not know yet how to figure out which execution plan Oracle is using. This was intentional, because the output has little meaning until you have the basic concepts of query execution under your belt. Now you’re ready to jump into the subject of analyzing how your queries are being processed.

The basic procedure is relatively simple, but there is no single good source that explains it from end-to-end. It begins when you run the query you’re curious about with the trace utility turned on. All this trace utility does is make an output file in the udump directory for the Oracle instance that contains data on how the query was attacked. (It usually is a sequentially numbered file with a .trc extension.) I like to extract the SQL in question and put it in a separate SQL file with the command to start the trace utility right before it. In that way, I can run the script from SQL*Plus. The command to turn on the trace utility is

alter session set sql_trace = true;

You also need to make sure that the TIMED_STATISTICS parameter in the initialization file is set to TRUE. Once this trace file is produced (that is, you run the query), you must find out where Oracle put the trace results. You have to look through the udump directory to find the file that was made at the time you created and ran the query (I use the ls -lt command in UNIX). It’s not convenient, but you have to find the filename before you can decode the output file to produce a report. This file is not very readable. You need to use the tkprof utility to turn this data into something designed for human beings. Here is the format of the command that will give you this report:

tkprof trace_dir/filename.trc outfile.prf explain=user sys=no

This takes the filename.trc file in the trace_dir directory and runs it through tkprof. It stores the output of this process in the outfile.prf directory. The user parameter is used to connect to Oracle as a user to get some additional information. This is not the most convenient utility, but it does provide some useful outputs. The report has some general information at the top about the number of fetches performed and the time to perform the operation. It then goes into a series of execution plan results from which you can test and plan your strategies, similar to the following:

SELECT STATEMENT

 MERGE JOIN

  SORT             JOIN

   TABLE ACCESS    FULL            GOLF_SCORES

  SORT             JOIN

   TABLE ACCESS    FULL            GOLFERS

In this example, I show the results of a join between two tables based on some common key (golfer ID). This query selects all applicable rows from the first table and then all applicable rows from the second table. It then joins them together in memory to form the resulting output. Depending on the number of rows to be retrieved and the overall size of the table, this type of query can be quite time consuming when using the merge-join execution plan.

There is a UNIX shell script called “bench” included on the disk that executes a SQL script. It has the command to set tracing on and to run it through all the steps in the process. Very few people actually type all these commands at the command line.

Now let’s look at some general guidelines on the optimization process. It is really tough to come up with general guidelines for the process of query execution. However, the following steps show how I normally approach it. This should work for most cases.

1.  First, make sure your instance is properly tuned.

2.  Run the query with trace mode on and run it through tkprof to determine how the query is being approached by Oracle. You can use the explain plan command in SQL*Plus, but the output is much more difficult to read than if you use tkprof (even though it contains the same basic information on the execution plan).

3.  If the query returns a few rows that are selected based on columns commonly used as criteria but are not in an existing index, consider building an index of these columns to solve this problem. If there is an index that it should be using, try giving it a hint to use that index.

4.  If tables are joined via merge-join, try to get Oracle to use nested loops to meld the data.

5.  For nested loops, try to ensure that it does the search against the smaller table first (it will be listed last in tkprof output).

6.  When all else fails, experiment with some of the alternatives to see whether you can improve performance.

The Value of Experimentation

By now you may be a little overwhelmed with all the options available to you in the query optimization world. How do you know which one will work best in your particular circumstance? There are a few people who have worked with this so much that they know right away which is the best method, and whether you need to use a hint to get Oracle to follow this execution plan. I am not one of these folks, and very few of the DBAs I have met can do this off the top of their heads.

How should you figure out which is the best solution? I am trained as a physicist. Some of my fellow students sat at their desks and tried to figure out the elegant theories that explained everything. I liked to go into the laboratory and see what the reality of the situation was. Most scientists believe that theories are only as good as they match up with reality (while philosophers sit around and debate what reality is). Therefore, my favorite solution to the problem of tuning a query is to experiment with it. I build my SQL script that turns tracing on and then run it though the process described previously to see what it’s doing and how long it takes to do it. I then vary the format of the SQL (add UNIONs and so forth) and give it a variety of hints. After it’s all over, I look at the tkprof outputs to see which of the various alternatives provides me with the response time I want.

Summary

This chapter has covered the basics of application tuning within an Oracle database. Obviously, there are entire books devoted to the subject of optimal database object design. The Oracle8 Server has improved optimization routines, especially for such common database constructs as the star schema. Because the database is getting better at choosing execution plans, it’s easier for developers to form queries that will be executed efficiently. However, you still need to make judicious use of indexes and configure your database correctly to get the best performance possible.