| |
 |
|
Tips for Tuning
Rule-Based Queries
Oracle Tips by
Burleson
|
Unlike cost-based tuning, where your job is to
add hints to change the execution plan, the job with the RBO is to
rewrite the query to change the execution plan. Here are some tips for
effective use of Oracle's rule-based optimizer:
-
Resequence table names Try changing the
order of the tables listed in the from clause to change the
driving table. Joins should be driven from tables returning fewer
rows rather than tables returning more rows. In other words, the
table that returns the fewest rows should be listed last.
This usually means that the table with the most rows is
listed first. If the tables in the statement have indexes,
the driving table is determined by the indexes. One Oracle developer
recently cut SQL processing time in half by changing the order of
the tables in the from clause! Another developer had a
process shift from running for 12 hours to running in 30 minutes by
changing the from clause.
-
Resequence Boolean predicates Try
changing the order of the statements in the where clause.
Oracle parses the SQL from the bottom of the SQL statement in the
reverse order with Boolean expressions separated by ANDs. Therefore,
the most restrictive Boolean expression should be on the bottom.
-
Add cost-based hints There are many
cases in Oracle8i where you will want to override the
rule-based default with the first_rows hint and analyze all
tables and indexes that participate in the query. Remember, you can
combine the first_rows hints with other cost hints to get
exactly the fastest execution plan.
-
Carefully evaluate join methods If you
are using the RBO, you must settle for a nested loop join. Even
though a sort merge join is available in the RBO, you should replace
all RBO queries that perform sort merge joins with the cost-based
equivalent that utilizes Oracle parallel query. In general, a merge
join is the most efficient join when the query returns a large
number of rows from both tables and you have multiple CPUs, because
the sort merge join performs full-table scans against both tables.
To use a sort merge join with the RBO, add a use_merge hint
combined with a parallel hint and be sure to analyze all
tables and indexes that participate in the query.
TIP: Remember that the hash join is not
available in the RBO. If you suspect your nested loop join will run
faster with a hash join, analyze the involved table and indexes and
add a use_hash hint to your query.
Next, let’s take a close look at how the order
of predicates in the where clause can affect the execution plan
for rule-based Oracle SQL.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
