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 Tips by Burleson

Breakdown of Third Commute

45 minutes to get to work =

40 minutes driving +

5 minutes waiting for traffic lights 

This time, about 89% of the time is spent driving and 11% waiting for traffic lights.  The traffic light miss ratio has now dropped to 11%, which is much better than the original 33%.  Unfortunately, the commute now takes 50% longer and is a lot longer in distance.  The SLMR metric is improving, but not quite hitting the mark of getting home or to work sooner.   

This illustration helps to show one of the flaws in tuning by ratios. Improving these metrics may not always resolve the poor performance the user sees.  Unfortunately, for quite some time, that was the common knowledge and what any Remote DBA that went to training, seminars or sat at the feet of the guru’s learned.  The hit-or-miss results obtained by following the ratio philosophy have muddied the waters.  Some people claimed success by doing this hit-and-miss ratio philosophy, and then wrote books and gave seminars on the practice, when others followed the same steps and saw no positive change.   

Sometimes the problem was a buffer cache that was undersized, and the low Buffer Cache Hit Ratio (BCHR) really was accurately measuring the problem. By increasing the buffer cache, there would be less data blocks that needed to be read from disk, which would result in better performance.  Other times, the problem was that the network was too slow and the delays the users were complaining about were not a database problem.  Moreover, maybe a user had started running a query that was performing multiple full table-scans on the largest table.  The possible causes of a slow database are many and unfortunately, the ratio methodology cannot identify a lot of them. 

Another problem with the ratio method is mathematical.  The following three ratios are all the same, but they have different values for the components of the formula. 

10/100=0.1

100/1000=0.1

1000/10000=0.1 

All three of these ratios are 10%.  If a job offer came with a tax-to-salary rate of 10%, which monthly paycheck would be preferred?  $10,000 per month sounds much better than $1,000 per month. In database terms, in the case of the BCHR, a high value ratio could mean that the database is very efficient and is almost never reading from disk. Or it could mean that the database is reading some things from the buffer cache far more often than it needs to.  A small code adjustment could bring the hit ratio down but increase the performance due to the reduction in data that needs to be read.   

Examining trends can be insightful in database administration.  For example, observing that database space usage is growing at a rate of 4 GB each month, to say that 20 GB of free space will only last 5 months is a simple exercise.  In tuning, trend analysis can help identify the existence of a problem.  For example, a BCHR that suddenly drops from an average of 90% to 60% indicates there could potentially be a problem.  While there is some discussion about the validity of certain ratios for trending database performance and identifying deviations from normal to help identify problems, this book will not be discussing ratios in any great detail.

Another issue with ratios is that these values take a while after startup to be reliable.  This is due to the fact that when the database is first started, none of the data blocks are in the buffer cache until they are first accessed.  This means that until a normal load has been placed on the database for a period of time, these ratios are not reliable.  

The problem with ratios is that bad ratios are not the problem.  Bad ratios are an incomplete and sometimes misleading measure of an underlying issue.  This is the basic flaw of the ratio tuning methodology.


The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Interface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert 

http://www.rampant-books.com/book_2004_2_wait_tuning.htm

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.