Re: Database Buffer Cache Hit Ratio

From: <fitzjarrell_at_cox.net>
Date: 6 Oct 2005 13:16:42 -0700
Message-ID: <1128629802.767196.20310@g14g2000cwa.googlegroups.com>

pcambraf wrote:
> I didnt like your off topic answers... if you dont want to help me just
> dont say nothing, i wasnt answering for been replyed like that...
>
> Im a DBA jr, in the applications side, cause the real DBAS wont fix
> anything, i just dont have access to sys or system or whatever could be
> called superuser, so i begin with handicap.

Yet you've stated in your original post the following:

| 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?
|
| Thank you so much...
|
| Regards

Note this passage: "I've changed the db_block_buffers parameter ..."

How on earth did YOU perform such an act absent access to SYS? Yes, you can change the value in the init.ora file yet you'll need SYS as SYSDBA permissions (INTERNAL through SVRMGR) to stop and restart the database so the changes will take effect. Did you do this without express permission from the 'real DBAs'? Did you do this just prior to a scheduled shotdown so your changes would take effect even though you have no authority to make them? Unilateral decisions, made by someone who is, at best, a JUNIOR member of the team, is not acceptable. Yet you feel compelled to meddle in team affairs as a rogue member. As it's been stated before if you don't have access to SYS you're not a DBA, in any way, shape or form. Such actions may be why you're still an almost junior member, and have no access to accounts with which you could do real damage to the database. And, possibly, since the 'real' DBA's 'won't fix anything' there is nothing to 'fix'?

> Anyway, im on a OLTP enviroment, with dictionary managment tablespaces,
> its a small system, about 3 o 4 gigs on disk and there are user
> complains, so much. The application is not well written, but fix this
> is not possible, they dont want to (another handicap), ive seen this db
> cache hit ratio so low, and i thought its a problem, and my question
> was: Could a contention in DBWR process (theres only one as ive said
> above) produce a low hit ratio in the instance?

Such an occurrence is not likely, as your poor code is the most likely culprit in your 'low buffer cache hit ratio'. Of course it could also be fueled by numerous one-off queries against any number of tables. The BCHR numbers MAY be indicating a problem is existent, but they are not the problem unto themselves. Ed Stevens gave you excellent advice: generate a statspack report and upload it to oraperf.com. You'll probably be surprised by the findings. And, possibly, your 'real DBAs' have already done so, and discovered that in your situation there is nothing to fix. They are the 'real DBAs' for a reason; I'd stop trying to second-guess them at every turn.

David Fitzjarrell Received on Thu Oct 06 2005 - 15:16:42 CDT