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 


 

 

 


 

 

   

Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines

 


Don Burleson: "Busting the Oracle Myth Busters"


September 15, 2004
Mark Rittman

If you followed the recent colourful debates on tuning using ratios vs. tuning using extended trace, sizing the buffer cache, calibrating the cost-based optimizer, and rebuilding indexes, read Cary Millsap's book on Optimizing Oracle Performance, Niall Litchfield's Five Great Oracle Myths presentation, you'll be interested to know that Don Burleson has just published an interesting article for DBAZine entitled "Busting the Oracle Myth Busters", in which he looks at what he considers to be the new myths of Oracle performance tuning:

The first one addresses tuning using the data buffer hit ratio:

"New myth - Ratio-based Oracle tuning is meaningless

Some Oracle professionals point out that the DBHR can be manipulated at will (by running queries with high consistent gets), and falsely conclude that because the DBHR can be changed at will, the data buffer hit ratio is a meaningless metric!

Detractors of ratio-based tuning correctly point out that databases that seldom reread data blocks (like a large data warehouse) and database systems with 30 gig data buffers that approach full-block caching will get little benefit from DBHR monitoring and tuning.

However, most OLTP systems and systems in which the working set of frequently-referenced data are not cached will greatly benefit from ratio-based tuning. Oracle Corporation recognizes that monitoring SGA usage ratios and adjusting the size of the SGA regions can have a dramatic impact on system performance, and this is the foundation of Oracle10g Automatic Memory Management (AMM) in which Oracle calculates the point of diminishing marginal return from adding data buffers"

The next looks at whether performance benefits from regular index rebuilds:

"New Oracle Myth: Index rebuilding requires careful analysis

Many Oracle professionals have attacked the practice of rebuilding Oracle indexes on a regularly-scheduled basis, correctly noting that the space reclamation and index performance does not always significantly change when an index is rebuilt. This has lead to the incorrect assertion that Oracle indexes are always optimized and never required rebuilding. Others attempt to create a set of index characteristics that accurately determine when an index will benefit from rebuilding.

In reality, many Oracle databases experience a huge benefit from periodic index rebuilding. Oracle recognized this benefit of index rebuilding when the Oracle9i online index rebuild feature made it possible to rebuild an Oracle index while the index is being updated (using the alter index xxx move online tablespace yyy; syntax).."

and the last looks at application tuning using extended SQL trace.

"New Oracle Myth: Wait Event Analysis is the best way to tune Oracle

During the late 1990s, a small movement arose among Oracle professionals who advocated the analysis of 10046 trace dumps to locate Oracle performance problems. The Oracle details dumps are created with the ďalter session set events '10046 trace name context forever, level 8'; command ad generate a huge dump which reveals all Oracle wait events for a session.

Now, donít get me wrong; I have been reading dumps since the 1970s, and I still remember how to use my slide rule and my old-fashioned E6B flight computer from my pilot flight school days. To this day, I try to get Oracle to publish their DSECTs, and I love to roll-around a three-foot thick, 80 lb. core dump on a dolly to impress visitors.

However, some of the wait-event tuning zealots wrongly forsake other Oracle performance methods. I once witnessed a wait event tuning expert spend hours analyzing a dump, only to discover high physical write latency due to a RAID5 disk subsystem. A quick look at a STATSPACK report would have revealed this issue in just a few minutes..."

All interesting stuff, and the full article can be read here.


 

   
��  
 
 
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