Chapter 25
Oracle8 Application Tuning

In This Chapter

•  Factors Designers Can Control

•  The Rule-Based Optimizer

•  The Cost-Based Optimizer

•  Execution Plans

•  Hints

•  Index Design

•  General Guidelines

•  The Value of Experimentation

“It’s not my job.” Perhaps tuning applications is not your problem. Perhaps you already have more than enough problems to deal with and do not want to get into any more. However, you still should have a basic understanding of application and query tuning. Why? Because odds are, if you have a performance problem with the system as a whole, the first words out of people’s mouths are going to be about the need for database tuning—they will tell you about how messed up your system is. You should, of course, perform the database tuning steps listed in the previous chapters to ensure that you know what you are talking about before you open your mouth. However, once you prove that the database is performing within normal limits, you may want to be able to track down the real cause to make your case more convincing. That is where a little knowledge about application tuning can make you dangerous.

The majority of application tuning work I have run across involves optimization of queries against the database. Although you may think up situations where you might want to tune transactions, most of these situations lead to splitting input/output loads between disks and other such things that are grouped within the realm of database tuning. However, generally speaking, the process of updating rows, deleting rows, and inserting new rows is relatively well-defined and follows a single course. The most you can do is make sure you have indexes that match the typical update/deletion criteria used and let Oracle do its work.

Queries, however, are another matter. The real value of database management systems to most organizations is not the capability of storing data (flat files did that successfully). The real value of a DBMS is the capability of rapidly and accurately recalling data from the database. This is where man-years of labor were spent in older systems as each developer had to build smarter query algorithms to find data in a reasonable time frame. Not every query is the same, though. Some may require you to scan the entire table (that is, you use almost every column in the table as part of your where clause). Others may look only at a single, indexed column in a single table. Still others may link a half-dozen tables using complex join criteria.

It seems pretty obvious that a search method that works well for one of these types of queries is probably not the best method for the others. In fact, Oracle and most other databases have a number of search algorithms in their systems that are designed to handle different needs. This is what enables them to work in such a large number of environments and applications. When an application is performing slowly on a well-tuned database, the first thing you should check is how the query is being processed to ensure that it is as efficient as it can be.

This chapter explains what you can do to make a query run more efficiently in an Oracle system, starting with a discussion of the factors that you can control when issuing a query. Then you learn about the two optimizers that Oracle provides. These are the components within the system that determine the search algorithm, or execution plan, that will be used to process the query. Then you explore steps you can take to optimize the query. Finally, being trained as a scientist, I go over a brief discussion on the value of experimenting with different factors to optimize the query. Unless you devote a percentage of your time to this discipline, you will probably not be able to look at a query, make two changes, and then watch as it flies.

Factors Designers Can Control

The process begins with a request for information. In an Oracle database system, this usually starts with a SQL query. Some development tools give you a graphical interface to enable you to tell it what you want, but these are merely convenience tools that eventually generate the SQL query and pass it to Oracle. Once the query is formed, Oracle goes through a number of steps to get the answer to the user. The steps related to the choice of an execution plan and execution of the query are shown in Figure 25.1.

Figure 25.1.  Query execution steps.

The first step in figuring out ways to make queries perform better is to understand the factors that you can control. Perhaps one of the most frustrating things about query optimization is that the basics of SQL do not provide you with much in the way of direct controls over the query execution process. Of course, this lack of control is one of the things people like most about a database management system—it handles all the details. In most cases, the DBMS will choose a plan that is far better than most programmers can implement. However, especially in large databases with large tables, you may need a little bit better control in those cases where Oracle chooses a plan that does not work out well.

The first item you need to control in Oracle version 7.1 and later is which type of query optimization Oracle performs. There are two optimizers available. The first is the latest version of the optimizer that Oracle has used for some time—the rule-based optimizer. It gets its name from the fact that Oracle has programmed in a series of rules about how to read the SQL statement in order to determine the execution plan. You can write some really poor queries if you are not careful with this optimizer. The other optimizer is referred to as the cost-based optimizer. It looks beyond the pure format of the SQL you write to determine the sizes of the tables and indexes involved with the query to determine the best execution plan.

Once you choose your optimization method, you have to work with your queries to help ensure that the optimizer chooses the best method for your needs. If you are using the cost-based optimizer, you need to ensure that you routinely take statistics on your database objects. This ensures that Oracle has the information it needs to make wise decisions. This optimizer ignores the order in which you list items in your SQL statements; therefore, your only choice is to override the optimizer (as described later in this chapter).