The Oracle optimizers are a nice idea overall. You get to access your data without concerning yourself with search algorithms or physical storage parameters. You don’t even have to decide when to use an index or which index to use. This all works well in the vast majority of queries I have run across. Every now and then, however, you will run across a query that joins a lot of big tables and takes forever to execute. This is the time for manual intervention into the execution plan process. Intervention is needed usually only in very large instances (that is, greater than 25GB in size).

The forms of intervention in the execution plan process can be separated into three general areas (see Figure 25.2). The first form of intervention is the hint. You can add a hint to any of your SQL query statements. You can literally tell it the execution plan it should be using. You can also tell it the order in which you want the tables to be processed. This can be pretty powerful, but you should avoid using it unless you have queries that have problems. Later versions of Oracle may have even better execution plans available to them. If you code in a lot of hints, you’ll miss out on the opportunity to use these new execution plans. (Most places do not have the kind of time to go through a large application and clean out all the hints and then retest it for speed.)

Figure 25.2.  Intervention with Oracle execution plans.

The second form of intervention is goals. This construct enables you to tell Oracle whether you want to choose an execution plan that gets the entire query completed quickly or returns the first few rows of data quickly. For large reports that are not printed until you have completed them, you are typically concerned about getting all rows returned as quickly as possible. However, if you are displaying a list of values on the screen for the users, you may want to get them a few rows to read while the rest of the query is being completed. This enables them to read and analyze data and avoid screen dead time that leads to user complaints. These goal options use cost-based optimization whether there are statistics present or not. (With no statistics, the chance of making poor decisions is higher.)

The final form of intervention is the application design itself. You can often improve performance by using SQL over PL/SQL constructs. You also can eliminate unnecessary information from the query that can cause additional work for Oracle. Finally, you can try various combinations of query constructs, such as UNION and UNION ALL to try to improve performance. There are certain constructs that force certain execution plans (such as the dreaded merge-join) which are less efficient. I usually wind up doing a little experimentation with these options to find the construct that works best for a given query.

The Rule-Based Optimizer

The concept behind the rule-based optimizer is simple. It looks at the SQL statement to determine all the possible ways it can execute the query. Then it selects the one on its list of queries it thinks will be most effective. The key here is that it has a simple list of possible ways to approach the problem that is fixed for all types and sizes of tables. The following is the basic order in which the execution plans are selected by the rule-based optimizer.

1.  Access a single row by using its internal row ID.

2.  Access a single row by a join formed out of a cluster of tables.

3.  Access a single row by combining a hash cluster key with a unique or primary key from a table not in the cluster.

4.  Access a single row by a unique or primary key.

5.  Access by joining rows in a cluster together.

6.  Access by hash cluster keys.

7.  Access by using a cluster index key.

8.  Access by using a composite index.

9.  Access by using a single-column index.

10.  Access by using a bounded range search on indexed columns (for example, >25 but <50).

11.  Access by using an unbounded range search on indexed columns (for example, >5).

12.  Access by joining tables using a sort-then-merge algorithm.

13.  Access by finding the maximum or minimum of an indexed column.

14.  Access by using the ORDER BY clause on indexed columns.

15.  Access by performing a full table scan (read every row in the table).

As you can see, unless you use clusters, you have a very simple list of ways to attack the problem. The rule-based optimizer also does not have a clue as to which table to access (that is, which will return the fewest number of rows) first, based on this data. Another point to note is that you wind up doing a full table scan if you do not have a where clause to specify rules for joining tables or selecting rows from the tables. The rule-based optimizer is relatively simple in the way it approaches things and can be easily fooled.

There is a little more to the evaluation process than has been described so far. The rule-based optimizer will catch a few other ways to make the statement more efficient than you coded it, but not many. Here is the list of steps that the optimizers go through.

1.  Evaluates your SQL to determine the expressions and conditions.

2.  Modifies the statements into an equivalent join statement that may be more efficient.

3.  Merges the queries used for any views that are accessed by the query into the query.

4.  Chooses whether to use the rule-based or cost-based approach. The overall database can be set to use either cost-based or rule-based optimizers based on the OPTIMIZER_MODE parameter which defaults to cost but can be set to rule. You can also use the alter session command to change the optimizer mode for a particular job you are running.

5.  Chooses access paths for each of the tables accessed in the query.

6.  If two or more tables are joined, chooses the order in which the tables are joined.

7.  If tables are joined, chooses the method of joining.