Re: Oracle slowed down

From: ianal Vista <ianal_vista_at_hotmail.com>
Date: Sat, 22 Apr 2006 13:42:27 GMT
Message-ID: <Xns97AD44453D2E7ianalvistahotmailcom@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?

From http://download-east.oracle.com/docs/cd/B19306_ 01/server.102/b14211/memory.htm#i29118

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.

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.

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. Received on Sat Apr 22 2006 - 08:42:27 CDT