Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter