| Previous | Table of Contents | Next |
Oracle8 supports two optimizers, the rule-based optimizer and the cost-based optimizer. The optimizers tell Oracle the best execution path for a particular SQL statement. The rule-based optimizer is the original Oracle optimizer. The cost-based optimizer was introduced with Oracle6.
The rule-based optimizer uses a series of 19 rules to decide this path (see Table 24.1). The lower the rank, the more performance benefit from the rule. Tuning is accomplished by making the rule-based optimizer make different selections. For example, adding an index would alter the rule selection, as would adding a function to an indexed field in a WHERE clause of a SQL statement (used to disable the use of an index), and most important, the way the SQL statement is coded has a great effect on which rules are selected. There is the concept of a driving table, or the table that will be compared to the others in a join SQL statement (multiple tables listed in the FROM clause). Since Oracle parses SQL statements from end to front, the driving table in a rule-based optimized SQL statement will be the last one listed in the SQL statement. There are a few other dependencies that can affect this decision, but for the most part, sometimes performance gains can be substantial just by changing the order of the tables in the FROM clause.
Note:
There is no guarantee between releases of Oracle that the rank and order processing of these rules remains constant. It is important to test applications when moving to newer versions of Oracle software.
| Rank | Where clause predicates |
|---|---|
| 1 | ROWID = constant |
| 2 | unique indexed column = constant |
| 3 | entire unique concatenated index = constant |
| 4 | entire cluster key = cluster key of object in same cluster |
| 5 | entire cluster key = constant |
| 6 | entire nonunique concatenated index = constant |
| 7 | nonunique index = constant |
| 8 | entire noncompressed concatenated index >= constant |
| 9 | entire compressed concatenated index >= constant |
| 10 | partial but leading columns of noncompressed concatenated index |
| 11 | partial but leading columns of compressed concatenated index |
| 12 | unique indexed column using the SQL statement BETWEEN or LIKE options |
| 13 | nonunique indexed column using the SQL statement BETWEEN or LIKE options |
| 14 | unique indexed column < or > constant |
| 15 | nonunique indexed column < or > constant |
| 16 | sort/merge |
| 17 | MAX or MIN SQL statement functions on indexed column |
| 18 | ORDER BY entire index |
| 19 | full table scans |
The cost-based optimizer makes its decisions based on a cost factor, derived from precollected statistics for the objects in the SQL statement. The ANALYZE SQL statement is used to collect these statistics. Since larger objects take longer to collect the statistics, Oracle offers an ESTIMATE option on the ANALYZE command so that only a percentage of rows are used to gather the statistics. Since the cost-based optimizer bases its execution-path decisions strictly on these collected statistics, it is important to keep the statistics current with dynamically changing objects.
Note:
The rule-based optimizer does not support partitioned tables, index-only tables, reverse indexes, histograms, hash joins, bitmap indexes, parallel query, star joins, or basically any new indexing/performance feature since Oracle7.3.
Enabling the cost-based optimizer is accomplished by running the ANALYZE command to collect statistics. The init.ora parameter, OPTIMIZER_MODE, must be set to CHOOSE or COST, or can be set at the user session level with the ALTER SESSION command. Disabling the cost-based optimizer is easily done by either resetting the init.ora parameter OPTIMIZER_MODE or simply deleting the statistics. If there are current SQL statements in the shared pool that have execution plans based on newer statistics, Oracle will invalidate the SQL in the shared pool, forcing it to be reparsed, using the new statistics the next time the SQL statement is accessed.
The ANALYZE command does use sorts and can be a time-consuming, resource-intensive process for larger objects. The ESTIMATE option of the ANALYZE command gathers a sampling of information from the object to base its statistics on. The following is a sample ANALYZE command:
analyze table <TABLE NAME> estimate
Tip:
If data is skewed or is not evenly balanced throughout an object, histogram statistics should be created. The more statistics gathered, the better decisions the cost-based optimizer will make. The following is an example of creating a histogram with 10 buckets. Additional information on histograms can be referenced in the Oracle 8.0 Tuning Guide, Chapter 8.analyze table <TABLE NAME> compute statistics for columns <COLUMN NAME> size 10
Hints are used to influence the cost-based optimizer. Hints can be used to control the optimizer’s goal, access methods, join conditions, parallel, and partitioning options. Hints are specified in the SQL statement syntax. The following shows an ALL_ROWS hint in the EMP table.
select /*+ ALL_ROWS */ ename, sal from EMP where SAL > 1000
| Previous | Table of Contents | Next |