Re: Oracle Myths

Niall Litchfield wrote:
>
> "Fraser McCallum" <fmcc_at_removetoemail_odbaguru.com> wrote in message
> news:n0tE8.10994$b5.37784_at_newsfeeds.bigpond.com...
> > Jeff,
> >
> > If you check your buffer cache hit ratio in the traditional way the cache
> > misses can include the direct reads which no amount of buffer cache tuning
> > can eliminate. Off the top of my head temporary tablespaces are an example
> > of something that does direct reads. I know that Steve Adams has scripts
> > that take all of this into account somewhere on his web site
> > www.ixora.com.au as well as probably some more in-depth details.
>
> In addition the hit ratio is just that a ratio it hides the values you
> should be measuring. If you have 2 statements one which does 10 io's to get
> the results 3 of which are physical and a second which does 10000 ios 1 of
> which is physical the first query will always perform better despite having
> a "terrible" hit ratio of 70%.
>
> In addition the last document I have seen from oracle suggests that 80% is a
> 'good' hit ratio this is lower than often quoted.
>
> >
> > Increasing the shared pool size in response to a bad library hit ratio
> will
> > create more memory in which the database will create longer free lists and
> > thus ultimately increasing the time taken to scan these lists and making
> > your hit ratio worse. However after you restart the database when their is
> > still lots of free chunks things will appear to have been made better,
> thus
> > many DBA's fall into a cycle of continuos enlargement.
>
> I actually had in mind the more subtle problem of web based apps which
> always seem to use literals. increasing the memory to store these statements
> is an utterly pointless exercise since they'll never get reused. what you
> should be dioing is eliminating the poor sql queries.
>
> NOTE I am also not saying that these measures are completely useless - just
> that one needs to understand what is happening in your system and that
> ratios (especially) don't tell you everything you need to know.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

A simple PL/SQL program to obtain any desired hit ration for a system can be downloaded from my site...

SQL> exec choose_hit_ratio(99.3)

and will be churn away happily until your hit ratio is 99.3

Magic!

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."