Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

Linda Webb

Got breaking Oracle news?    Burleson Consulting Oracle News

Click here for more Oracle News Headlines

 


Great Oracle CBO & SQL tuning paper published

Glenn Goodrum has published an excellent overview of tuning by adjusting the statistics for the Oracle cost-based optimizer.  This is one of the best I’ve seen, and it has a very clear step-by-step approach:

 

http://www.dbazine.com/goodrum2.shtml

 

Goodrum makes some insightful conclusions, especially noting a potential flaw in the Oracle optimizer for costing subqueries:

 

Conclusion

 

Because the query optimizer bases its choices on estimates of cardinality, the resulting plans can be inefficient when those estimates are inaccurate. In this article, we saw an example where a predicate’s selectivity was far less than what the optimizer assumed. Even accurate column statistics and histogram do not counter the effect of the optimizer’s assumptions about predicates of the form COLUMN=(subquery). We worked around that incorrect assumption by changing the column statistics such that the predicate’s selectivity was accurate, and the optimizer responded by giving us a better query plan.

 

Keep in mind that the technique described here is not a silver bullet. The only way to know for sure whether a given change will help or not is to try it in a test database containing production-sized tables.

 

Some Top Oracle experts weigh-in on Goodrum’s unique approach to SQL tuning:

 

**********************************************************

There is never an “optimal” solution when the optimizer makes a bad call, but whatever the solution, it’s important to ensure that the repair is:

 

1 – Permanent

2 – Addresses the same issue with other SQL statements

 

Fixing the CBO statistics is a great solution, but we still have the “persistence” issue, where we must force the dbms_stats package to collect the right stats on the next analyze.

 

Donald K. Burleson

 

**********************************************************

Thinking about this...

 

But... is this technique really any better than using hints? I suggest that
it's worse than a hint, for these reasons:

 

  1. It's prone to be over written next time we generate statistics.
    2. It effects *every* query in the database using that table.
    3. Related to #1, it disallows you from re-generating statistics, or you have
     to manually calculate and set this value every time you do.

 

Bottom line is that the optimizer is WRONG and that should be corrected by Oracle.

 

BUT.... it's a very insightful article on how the Optimizer works.

 

Robert Freeman

 

**********************************************************

 

Very interesting article. 

 

I find it odd that the experts say that using
hints is bad because the data may change and the hint will force a bad
execution plan.  Yet this article changes the stats on a single column,
and the change will not survive the next stats collection, nor will it
change when the data changes.  To me it is a catch either way.

 

Col. John Garmany

 

***********************************************************************

 

Glen was kind enough to respond to this feedback:

 

As Mr. Freeman points out, manual changes to the statistics would indeed be overwritten by the next global recomputation job.  In those cases where a manual override is actually beneficial, the administrator in charge would need to implement an additional step to the usual recomputation job, to re-establish the manual overrides.  Most shops I've worked in have some kind of periodic script (cron, DBMS_JOB, etc.), so the overrides could be added to the end of that script.  I see now that I did not make that clear in the article.

 

It is also true that the effect of the overrides is not limited to the problematic query, a fact I referred to near the end of the article, right before the conclusion.  The example in the article, using EFFDT=(SELECT MAX(EFFDT)...) came from Peoplesoft HR.  The EFFDT column is actually a good candidate because it is *only* used in the subquery predicate, never EFFDT=constant, so the optimizer's selectivity for that predicate is always wrong.  I almost succeeded in convincing the admin of a Peoplesoft HR system to use this technique, because it actually helped large number of hard-to-tune queries, but they ultimately opted for case-by-case hints.  But I agree that it is not a cure-all.
 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational