Re: db_block_buffers - can you have too many ? Thomas Kyte, please help!

It really depends on what you do. A large Buffer Cache may move your problem to a later stage (when the buffer cache is flled up e.g.) and writing the dirty buffers may be come the bottleneck. Having a smaller buffer cache could make the DBWR more active and write dirty buffers earlier.

The other problem is that when extents get allocated the DBWR has to make sure that the extent range is not in the buffer cache and scan the whole buffer cache and write dirty blocks in that range (for reuse).

The reasons can always be found in v$system_event.

Anjo Kolk.

satar_at_my-dejanews.com wrote:

> I had a similiar experience, another DBA was evaluating my Database and told
> me that I would have better performance if I lowered my db_block_buffers. I
> couldn't understand the logic behind it, so I didn't do it. My cache hit
> ratio is 99.97 percent, and I saw no tuning needs. I also wasn't paging or
> swaping and everything is fine and dandy. My question is, why would anyone
> suggest lowering the db_block_buffer parameter? My understanding was to shove
> the whole database into the SGA whenever possible.
>
> Satar
>
> > > I had sudden performance problems - typical query
> > > response times up by a factor of 10. No change had been made to the box and
> > > it's dedicated to Oracle.
> > >
> > > The database is quite large - 300 plus tables, and is heavily laden with
> > > PLSQL code.
> > >
> > > With 256MB RAM on the box, I had increased db_block_buffers well above the
> > > sample values shown in init.ora. (but still keeping the SGA size well below
> > > total RAM - total SGA size was approx 60MB)
> > > Oracle support told me to reduce the db_block_buffers and this did actually
> > > solve the problem !?
> > >
> > > Can anyone help me to explain this ? Surely the more database blocks you
> > > can cached, the better so long as your OS isn't swapping the SGA to disk ?
> > > Or am I missing something.
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Nov 12 1998 - 14:08:06 CST