BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle Optimization of Data Warehouse Queries

Oracle Tips by Burleson Consulting

Oracle provides initialization parameters to control the amount of work performed by the cost-based optimizer when evaluating a query. While parsing is quite fast for simple queries, complex queries with more than six tables can parse for many minutes while Oracle evaluates every possible table join combination.

Sometimes, data warehouse Remote DBAs are perplexed when they find that a 15-way table join takes 30 minutes to parse! This is because there are 15 factorial possible permutations of the query and over one trillion (1,307,674,368,000) query permutations.

While the ultimate solution is to employ stored outlines to remove the parsing phase, Oracle has two important initialization parameters that work together to control the number of possible execution plans generated by the Oracle optimizer.

TIP: If you do not have complex SQL queries that join five or more tables together, you need not be concerned with the optimizer_search_limit or optimizer_max_permutations parameters. These only apply when Oracle is computing possible table join combinations for queries with large numbers of tables.

The optimizer_search_limit Parameter

The optimizer_search_limit parameter specifies the maximum number of table join combinations that will be evaluated by the CBO when deciding the best way to join multiple tables. The reason is to prevent the optimizer from spending an inordinate amount of time on every possible join ordering. The optimizer_search_limit parameter also controls the threshold for invoking a star join hint, and a star hint will be honored when the number of tables in the query is less than the optimizer_search_limit. The default value is 5.

If the number of tables in the query is less than optimizer_search_limit, the optimizer examines all possible table join combinations. The number of join orders is the factorial value of the number of tables in the query. For example, a query joining five tables would have 5! = 5*4*3*2*1 = 120 possible combinations of table join orders. The number of possible evaluations is the factorial of the optimizer_search_limit, so with the default value for optimizer_search_limit of 5, the cost-based optimizer will evaluate up to 120 table join orders.

The optimizer_search_limit and optimizer_max_permutations parameters work together, and the optimizer will generate possible table join permutations until the higher of these two values is met. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining six tables will exceed the optimizer_search_limit but still may spend expensive time evaluating all 6! (720) possible table join orders because the optimizer_max_permutations parameter has not been exceeded with its default value of 80,000.

However, when tuning a SQL statement when you plan to use optimizer plan stability to make the execution plan permanent, it is acceptable to temporarily set the optimizer_search_limit up to the number of tables in your query, tune the query by reordering the table names in the where clause, and then using the ordered hint with stored outlines to make the change permanent.

Note: The use of the ordered hint overrides the optimizer_search_limit and optimizer_max_permutations parameters. This is because the ordered hint requests that the tables be joined in their specified order in the from clause of the query. The ordered hint is the way most SQL tuning professionals disable table join evaluation, once the optimal join order has been determined.


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


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter