Re: Oracle Myths

"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

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