Re: db_buffer performance

> > I have an Oracle 9.2.0.4 database. I changed a few parameters and am
> > not getting HORRIBLE db_buffer hit ratios.
> >
> > Was:
> > db_block_buffers = 250000
> > db_block_size = 8192
> > db_cache_size = 70000000
> >
> > is:
> > ##db_block_buffers = 250000
> > db_block_size = 8192
> > db_cache_size = 80000000
> >
> > I thought since going to 9i I didn't need db_block_buffers.
> > Ideas?

> I suspect that you didn't have both the db_block_buffers and
> the db_cache_size set at the same time, as you would have
> got an error ORA-00381: cannot use new and old parameters

They were orginally with no error upon boot or in the alert log.

>But your buffer size with the old settings would be ca. 2GB
>(250,000 x 8K).

>You buffer size with the new setting would be ca. 80M,
>or about 10,000 blocks - a reduction by a factor of 25.
>With a change in buffer space that extreme, it should not
>surprise even the most 'BCHR is bad' fanatic that your
>overall requirement for physical I/O requests should go
>up, and your hit ratio drop.

That makes a ton of sense! Thanks! In fact, when I checked glance, teh HPUX utility, I now see that there is more free memory.

>Note that the poster is not complaining specifically about
>a particular value for the BCHR - he is complaining that
>he sees a significant change in the BCHR. Although most
>people now realise that any 'specific' value is of itself of
>little value, it is important to remember that a dramatic
>change in the ratio is often indicative of a developing
>(or developed) problem.

Could not have explained it better. Thanks again.

"I am mortified to be told that, in the United States of America, the sale of a book can become a subject of inquiry, and of criminal inquiry too." - Thomas Jefferson Received on Wed Sep 01 2004 - 10:10:25 CDT