Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Tunning

Re: Oracle Tunning

From: Mogens Nørgaard <mln_at_miracleas.dk>
Date: Fri, 01 Feb 2002 09:30:35 -0800
Message-ID: <F001.00403702.20020201091033@fatcity.com>

It's very true that we want improved response times, better performance, more colorful sunsets and a few other things I shall not mention here :-).

However, the problem with Niemich' book is that it contains incorrect information which will lead you to wrong conclusions and down paths you shouldn't tread. For instance, on page 7 at the bottom, it's stated that if you raise the buffer cache hit ratio from 94 or 97 (or whatever) then you can triple your throughput. That's of course absolutely wrong and misleading at the same time. A high bchr is a rather sure sign that you have grossly inefficient SQL running - and there's no direct link between bchr and performance, by the way. Now, that kind of advise would send a lot of people out hunting for high hit ratios when in fact their problems lie somewhere else. And so on and so forth. The list is very long of grave errors or misleading statements in that book. Sorry about that. I haven't met Niemich himself so far, but I'm sure he's a very nice guy like everybody else out there in Oracle land. His "others will need oxygen" attitude, however, might mean something else to the rest of us than intended...

The technique for estimating db_block_buffers is just as flawed. We see way too high db_block_buffers practically everywhere we go.

Interpreting the bstat/estat stuff is probably correct as such, but if you don't use time-based measurement methods there is NO way to find the correct bottlenecks, estimate their impact, prioritize, etc. IBM found that out in the mid-80's and instrumented their mainframe environments. Oracle instrumented the kernel in V7.0. It's about time people stop using this checklist tuning thing, which is such a waste of time and energy - and most often will end up with the wrong conclusions for you.

Cary Millsap has published a very nice paper called "Why a 99% buffer cache hit ratio is NOT ok". Check it out. Cool stuff. Most SQL statements that perform badly have an extremly high cache hit ratio, by the way... The very same myth about the need for a high bchr is, interestingly enough, alive and well among SQL Server instructors, DBA's and writers. Perhaps that fact alone can get people to stop focusing on this nonsense in the Oracle world :-))).

Bjorn Engsig's paper on interpreting StatsPack can be downloaded from, among other places, Anjo's OraPerf.com site - and that paper effectively explains how little you need to know in order to interpret the bstat/estat or StatsPack output. You'll be surprised.

And then there's this YAPP paper from Anjo. It just might be available on OraPerf - you never know :-))).

The best thing to do with regard to using the wait interface (the only way to go) is to find one of the many scripts that collect the correct information (or use Bjorn Engsigs paper about StatsPack), then ask questions about the output on this list, for instance. You'll also find, that if you type the full wait event name in between double quotes on Google.com you'll usually find good information about it.

Best regards,

Mogens

K Gopalakrishnan wrote:                     

Samir,   

 
  

I cannot resist anymore. I was the one who raised this issue in this list and few other discussion groups&#8230;in which few of Rich&#8217;s colleagues also participate. It is one of the incorrect irrelevant books in the Oracle Performance Tuning.

I can find ludicrous errors in every chapter (well.. some times in every
page) .. Not typo errors but also conceptual errors. Even the one you are
mentioning (using X$BH table to monitor buffer use). It is another immature
recommendation (in case if you are mentioning the one explained as &#8220;TIP&#8221; in page 607.  It is a bunch of printed
papers nicely bound as a book sometime I use it to keep my laptop to adjust the height while working with the docking station.   

 
  

I had written to Rich long back (when he published the book) pointing all these errors and got a reply from Rich saying &#8220;It is my first book. It will be corrected in my next edition&#8221;. We have discussed this in a big way in this list (Me, Yong Huang, and few others). If you have time you can search those mails in the archives of the lists&#8230;   

 
  

Let me see whether those mails are still with me. If I find them I will forward that to you.   

 

  <!---size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">&nbsp;AUTOTEXTLIST \s &quot;E-mail Signature&quot;
Best Regards,   

K Gopalakrishnan   

 
  

 

  <!---size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'>
 
  

-----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of SARKAR, Samir
  Sent: Friday, February
01, 2002 2:25 AM
  To: Multiple recipients
of list ORACLE-L
  Subject: RE: Oracle Tunning   

 
  

Well Mogens, Niemiec's book does have its plus points as well. What we basically want from Oracle   

is improved response times and faster performance........which is all about how many hits we r getting   

and improving the hit ratio is something any DBA will try to attain.   

There is a wonderful technique in his book about how to estimate the size of the db_block_buffers   

by mythically raising it by a certain amount and checking the hit ratio or decreasing it and checking   

the impact. This way, we can arrive at an optimum value for the db_block_buffers.   

How to interpret the UTLESTAT/UTLBSTAT statistics r very clearly and lucidly explained as r the various   

join methods, improper use of indexes including index suppression, using the x$bh table to monitor buffer use,   

a very clear explanation of the Explain Plan, new tips for Oracle 8/8i, how to use PL/SQL for better performance etc.   

The best part is the book has lots of very useful queries and screen reprints to help us understand the   

scenario better.   

 
  

Am not saying that it is the best tuning book in the market but the techniques of tuning r very clearly explained   

in it.   

Tuning by wait events is still very arcane and not explained clearly exactly how to do it in many books though   

I haven't read the 101 book. I still find it difficult to properly interpret all the various wait events and latch contention   

and how to go about tuning them And until Steve Adams comes out with his advanced performance tuning book,   

we will all have to wait.   

 
  

Samir   

Samir Sarkar   

Oracle DBA - Lennon Team   

Schlumberger
S
ema   

Email :  samir.sarkar_at_nottingham.sema.slb.com   

            samir.sarkar_at_sema.co.uk
  

Phone : +44 (0) 115 - 957 6217   

EPABX : +44 (0) 115 - 957 6418 Ext. 76217   

Fax : +44 (0) 115 - 957 6018              

-----Original Message-----
  From: Mogens Nørgaard [mailto:mln_at_miracleas.dk]   Sent: 31 January 2002 18:39
  To: Multiple recipients
of list ORACLE-L
  Subject: Re: Oracle Tunning   

Commit; :-)   

 In my opinion, you shouldn't spend your money on buying the Niemich book. It's full of errors (increase the buffer cache hit ratio, for instance) and the wrong approach (no time-based measurement method, just checklist after checklist).   

 Buy 101 by Gaja. Then buy Tom Kyte's One-On-One book for general fantastic advise on anything. Then go to oraperf.com (Anjo), hotsos.com (Millsap), ixora.com.au (Steve Adams) and Jonathan Lewis' website (can never remember the adresse). Or go to MiracleAS.dk and find all these links, including the book links.   

 Mogens
 Miracle A/S
 Denmark   

 Farnsworth, Dave wrote:

  Binay,I totally agree with this recommendation from Jared for a tuning book.Read the first three chapters, stop and re-read them.  And if you playyour cards right you can even get a question answered by an author onthis list.  Cool, eh.Dave-----Original Message-----Sent: Tuesday, January 29, 2002 3:05 PMTo: Multiple recipients of list ORACLE-LStart with 'Oracle Performance Tuning 101',  available at anamazon.com near you.JaredOn Tuesday 29 January 2002 09:10, BINAY.KUMAR_at_p onl.com wrote:
  Hi Everyone       Can anyone suggest me some very good book on Oracle Tunning.   Please only

  mention those books  which you think is really worth purchasingBinay KumarOracle Cerified DBALondon-------------------------------------------------------------------The contents of this e-mail are confidential to the ordinary userof the e-mail address to which it was addressed and may also beprivileged. If you are not the addressee of this e-mail you shouldnot copy, forward, disclose or otherwise use it or any part of itin any form whatsoever. If you have received this e-mail in errorplease notify us by telephone or e-mail the sender by replying tothis message, and then delete this e-mail and other copies of it
from your computer system. Thank you.We reserve the right to monitor all e-mail communications throughour network.   

 
        


  

This email is confidential and intended solely for the use of the   

individual to whom it is addressed. Any views or opinions presented are   

solely those of the author and do not necessarily represent those of   

SchlumbergerSema.   

If you are not the intended recipient, be advised that you have received this   

email in error and that any use, dissemination, forwarding, printing, or      

copying of this email is strictly prohibited.      

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.   


               Received on Fri Feb 01 2002 - 11:30:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US