Re: db_block_buffers

R. Nettleton <oradba_n_az_at_excite.com> wrote in message news:7uqt49$74d$1_at_nnrp03.primenet.com...
> Hello,
>
> I have a database that has a DB block hit ratio of only 15%. My
> db_block_buffers is set to 10,000 and my db_block_size is 4096 which gives
> me a 40M DB buffer cache (quick math skills, huh?) At any given time we
> have about 80 -100 users on this database.
>
> For a test, I increased my db_block_buffers from 5,000 to 10,000 last
night
> and checked the result today (hence, this message). When the
> db_block_buffers was at 5,000, my DB block hit ratio was at 10% and, as
was
> said above, its now only at 15% after doubling my db_block_buffers.
>
> After running BSTAT and ESTAT for one hour during peak time, the report
> shows no latch contention, 98% of the sorts are done in memory, the
library
> cache and dictionary cache are both 99%. I'm guessing that it is the
> application that is causing this.
>
> It is a "canned" application so we don't have access to the code. It is a
> document management package called PC DOCS (if anyone is familiar with
it.)
> It seems that, of the queries run by the users, there are a few tables
they
> constantly query against. I'm wondering if I should pin these tables into
> memory.
>
> Fortunately the users don't complain about the database being slow, but
I'm
> at a loss as to why my ratio is so bad. Any thoughts?

Like the other guy said, it's possible that you're doing lots of full table scans. Sorting is not a problem, obviously. You could run a trace to find out where the physical reads are occuring.

Were the tables created using the nocache clause?

Are all the users accessing different tables to each other? If so then nothing would stay in the buffer for very long. So yeah, maybe you should specify the most used tables as cached, and the least used tables as nocached.

But 15%... crikey!

HTH Received on Sat Oct 23 1999 - 19:17:15 CDT