Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Establishing Your Instance-Wide SQL Baseline Oracle Tips by Burleson

When undertaking to tune all of the SQL in a database, it is unwise to leap headfirst into the task and begin to tune individual SQL statements.

Rather, most SQL tuning professionals begin by changing instance-wide parameters to establish a baseline for the tuning of individual SQL statements. This instance-wide tuning involves resetting some important initialization parameters, most notably the optimizer_mode, and ensuring that the optimal settings are in place for Oracle parallel query and multiblock reads.

Setting the Default Optimizer_Mode

This optimizer_mode is the most important of the initialization parameters for SQL tuning. Essentially, the choice for optimizer_mode is the rule-based mode (rule), or the cost-based optimizer modes (first_rows or all_rows). The all_rows cost-based optimizer mode is generally reserved for batch-oriented systems where throughput is more important than transaction response time. There are some general guidelines for the best optimizer mode for a given version of Oracle (Figure 9-1). The rule-based optimizer has not been changed since Oracle7, while the cost-based optimizer has been constantly improved for better performance. If we take Oracle’s Applications software as a benchmark, we see that Oracle Applications used rule-based optimization until Oracle8i was introduced.

Figure 1: Optimizer mode and optimal execution plans

It is ironic that while Oracle’s official posture was to recommend cost-based SQL optimization in Oracle7 and Oracle8, Oracle Corporation continued to use rule-based optimization for their own Oracle Applications software. Starting with Oracle8i, the cost-based optimizer has improved to the point where it can be considered as a default optimizer_mode for an entire database. If your database is using advanced features such as bitmapped or function-based indexes, you can still use a rule-based default and add statistics and first_rows or all_rows hints to invoke the CBO for those queries that requires advanced indexes.

In addition to the version of the Oracle software, there are also some guidelines that can give you a hint about the best optimizer_mode for your system (Table 9-1).

Table 1: System Characteristics and Probable Default Optimizer Mode

The goal of establishing a baseline is to set the optimizer_mode such that the majority of SQL statements are optimized, thereby reducing the amount of individual SQL tuning. Let’s illustrate this concept with a simple example. Assume that we have an OLTP database running Oracle 8.0.5, and the optimizer_mode has been defaulted to choose, and all of the tables and indexes have been analyzed. Since the optimizer_mode=choose will always use the CBO when statistics are present, we must first see if another default will result in faster overall performance. In this type of scenario, the choices are between the rule and first_rows optimizer mode defaults.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.