Re: db_block_buffers - can you have too many ?

> 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.

I think Sybrand's answer is good but I'd like to add my thought that you do not have nearly enough RAM in that machine. I would cede 100MB to NT and with 300 tables (how many rows?), and lots of PL/SQL you may be running very tight depending on the number of users, cached tables, triggers, etc.

Daniel A. Morgan Received on Wed Nov 11 1998 - 12:44:11 CST