Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Buffer Cache Hit Ratio

Re: Database Buffer Cache Hit Ratio

From: Joel Garry <joel-garry_at_home.com>
Date: 5 Oct 2005 16:07:54 -0700
Message-ID: <1128553674.441298.105730@g47g2000cwa.googlegroups.com>

pcambraf wrote:
> Hi people.
>
> I had an 8.1.7.4 database with a 68% of buffer hit ratio and 180 Mb of
> cache, I've changed the db_block_buffers parameter so we have 234 Mb of
> db buffer cache, and the hit ratio is now 57%, almost a 10% less!!.
> I'm mad about this, i've been looking around and i think it could be a
> I/O retention in the only one DBWR process, żis this possible?
>
> Are there any other factors to consider?

What is the purpose of your database? OLTP? DSS? DW? All of the above?

As others have pointed out, trying to tune to a particular hit ratio is not particularly productive. A DSS might well be stuck at a lower value.

Think about what it means - the chance that a block will have been read into the SGA. If you are constantly doing things that read through tables much larger than the SGA, and several people are doing this, what are the chances that they will both be asking for a block that happens to be there? So the answer is not to make the SGA a little bigger, but rather reduce the full table scans. If you are an OLTP type situation, there might be some obvious code that is wrong, or perhaps a missing index - that would likely show up as high waits for scattered reads. If DSS - well, it depends.

Now, 200M is pretty small these days - how much physical memory do you have? Why are you on an obsolete version? What hardware, OS? Which optimizer? Are you using dictionary managed tables (if so, it is possible that the internal tables used to track segments have grown unmanageable)? What else have you changed?

I do disagree with one thing said, viz., not to do anything if no one is complaining. That presupposes everything is reasonable to begin with. To check that, you might download the 9.2 OEM. Run that statspack thingee, too. If you are looking at a system that has been setup a while ago, things might be surprisingly far off.

jg

-- 
@home.com is bogus.
And relax, it could only be worse in Milwaukee.
Received on Wed Oct 05 2005 - 18:07:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US