Re: Oracle slowed down

"ianal Vista" <ianal_vista_at_hotmail.com> wrote in message news:Xns97AD44453D2E7ianalvistahotmailcom_at_70.169.32.36...
> "Bob Jones" <email_at_me.not> wrote in
> news:Fng2g.4679$Lm5.3587_at_newssvr12.news.prodigy.com:
>
>>
>> "ianal Vista" <ianal_vista_at_hotmail.com> wrote in message
>> news:Xns97ACC06ABD3DDianalvistahotmailcom_at_70.169.32.36...
>>> "Bob Jones" <email_at_me.not> wrote in news:tff2g.50869$_S7.36405
>>> @newssvr14.news.prodigy.com:
>>>
>>>>
>>>> "hpuxrac" <johnbhurley_at_sbcglobal.net> wrote in message
>>>> news:1145630093.179258.20210_at_g10g2000cwb.googlegroups.com...
>>>>> Bob Jones wrote:
>>>>>
>>>>>> > Ratios are mathematical normalizations that may obsure
>>> information.
>>>>>> >
>>>>>>
>>>>>> That is a big "may". In most real world cases, it does not.
>>>>>
>>>>> The real world is much too large and complicated to say "In most".
>>>>>
>>>>> Unless you have accurate statistics on everything in the real
>>>>> world?
>>>>>
>>>>
>>>> Does one need to stand at every spot on earth to know gravity exist
>>>> everywhere?
>>>>
>>>>> Ratio's have been deprecated is the new viewpoint.
>>>>>
>>>>
>>>> Says the Pope?
>>>>
>>>>
>>>>
>>>
>>> So say most Oracle "experts" for the last 3 - 5 years.
>>>
>>> BCHR is a meaningless indicator of database performance.
>>> It appears that you suffer from Compulsive Tuning Disorder (CTD).
>>
>> Because the "experts" say so, that's why I have to think so. It
>> appears you are suffering from Absence of Judgments Syndrome.
>>
>>
>
> At what point does BCHR go from "good" to "bad" or vice versa?
>
> For some case a high BCHR is good & for other cases it is not good.
> For some cases a low BCHR is good & for other cases it is not good.
>
> So just by observing the value of BCHR can you know what to do next?
>
>

As I have said, BCHR is just one of the indicators, not the only one.

>
> 7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
>
> There are many factors to examine before considering whether to increase
> or decrease the buffer cache size. For example, you should examine V
> $DB_CACHE_ADVICE data and the buffer cache hit ratio.
>

Hmmm, I thought BCHR is deprecated.

> A low cache hit ratio does not imply that increasing the size of the
> cache would be beneficial for performance. A good cache hit ratio could
> wrongly indicate that the cache is adequately sized for the workload.
>

The first statement is a little vague. Low means how low? I would change "does not" to "sometimes does not".
Again, in the second statement, good means how good? Close to 100%? Do you still consider cache size being inadequate then?

> To interpret the buffer cache hit ratio, you should consider the
> following:
>
> *
>
> Repeated scanning of the same large table or index can
> artificially inflate a poor cache hit ratio. Examine frequently executed
> SQL statements with a large number of buffer gets, to ensure that the
> execution plan for such SQL statements is optimal. If possible, avoid
> repeated scanning of frequently accessed data by performing all of the
> processing in a single pass or by optimizing the SQL statement.
> *
>
> If possible, avoid requerying the same data, by caching frequently
> accessed data in the client program or middle tier.
> *
>
> Oracle blocks accessed during a long full table scan are put on
> the tail end of the least recently used (LRU) list and not on the head
> of the list. Therefore, the blocks are aged out faster than blocks read
> when performing indexed lookups or small table scans. When interpreting
> the buffer cache data, poor hit ratios when valid large full table scans
> are occurring should also be considered.
>
> Note:
> Short table scans are scans performed on tables under a certain
> size threshold. The definition of a small table is the maximum of 2% of
> the buffer cache and 20, whichever is bigger.
>
> *
>
> In any large database running OLTP applications in any given unit
> of time, most rows are accessed either one or zero times. On this basis,
> there might be little purpose in keeping the block in memory for very
> long following its use.
> *
>
> A common mistake is to continue increasing the buffer cache size.
> Such increases have no effect if you are doing full table scans or
> operations that do not use the buffer cache.

Do all these mean BHCR is deprecated? Received on Sat Apr 22 2006 - 10:23:54 CDT