Re: Is there any harm in setting very large db_cache_size even when buffer hit ration does not increase?

yes. The buffer cache is simply a group of linked lists. Each list has a latch to protect. Every time a list is read a latch is taken out. If you set autotrace on, and look at 'consistent gets', each one of those is a latch get.

So the larger your buffer cache the longer your linked lists. The longer your linked list, the more time it takes to retrieve data. The more time it takes to retrieve data, the longer each latch is held. Thereby blocking others.

Make the buffer cache as large as necessary. If you have extra RAM see how much sort space you are using and consider increasing the size of your PGA.

btw, hit ratio isn't all that relavent of a stat.

"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0405201739.7d707593_at_posting.google.com...
> I have sevel GB's of memory on my HP UNIX server. My buffer hit ration is
> low (50%). I have increased db_cache_size (Oracle9i) gradually. I have not
> seen increase in hit ration all that much. My question, is there any harm
> in increasing db_block_size so it uses 1Gb or more of memory (which is
avaiable
> to Oracle database instance) without causing any impact on other
applications
> on that sever. Actaully, my server is mainly a database oracle server.
There
> are a few application scripts which run in the night for a few minutes.
>
>
> I would think more memory I use by increasing db_cache_size will help the
> performance even if hit ratio is not increasing all that much (i.e.
> it is 55% instead of 95%.
>
> Prem
Received on Thu May 20 2004 - 21:56:12 CDT