SEO

#1 (**permalink**) 02-23-2008, 09:47 AM

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

#2 (**permalink**) 02-23-2008, 09:47 AM

Prem K Mehrotra wrote:
> 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

Ready, Fire, Aim!

Exactly why do you think that increasing BCHR is a "Good Thing" (TM)?
You've already demonstrated that increasing the db_cache_size has
little to no impact on BCHR. What make you conclude that increasing
it more will do more good than harm?

Do you realize that there are scripts which will provide any BCHR you
select? Since this is a proven reality, please aceept the reality that
the BCHR is a mythical indicator of performance.

Let's speculate...
Assume you can increase BCHR from 50% to 90%,
exactly what does that mean WRT response time for your application?

You are fixated on a meaningless number WRT Oracle performance.

Your time would be better spent find slow SQL statements & making them
faster.

#3 (**permalink**) 02-23-2008, 09:47 AM

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@hotmail.com> wrote in message
news:43441e77.0405201739.7d707593@posting.google.c om...
> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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