| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: db_block_buffers - can you have too many ? Thomas Kyte, please help!
A copy of this was sent to satar_at_my-dejanews.com
(if that email address didn't require changing)
On Thu, 12 Nov 1998 00:48:35 GMT, you 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.
>
Given the amount of information below, its impossible to say definitely why decreasing the db_block_buffers helped in this particular case, however... It is easy to speculate.
I could speculate that the machine with 256meg of ram (small for a server these days) might have had some other application(s) running on it. The 60meg of ram sounded small, but given other things on the machine was quite large. Reducing the amount of ram dedicated to the SGA gave the oracle dedicated servers and other processes running on the machine more space...
I could speculate that there were lots of people logged into the machine and given only 256meg of ram, reducing the amount needed by the SGA sped them up.
I could speculate that the queries didn't ever re-use the data (the data was never read twice, lots of reading going on but not lots of re-reading of data). In this case, having lots of data in the cache and managing that would slow things down (lots to manage and more to look through to discover that it isn't already cached and you have to go to disk anyway).
I could speculate that the machine was swapping like crazy and need the ram back.
Its not necessarily a good thing to cache entire databases. We are expecting, and are optimized for, to have to go to disk sometimes. We have a feature called the VLM (very large memory) for caching more (showing the the bigger the cache, you need different algorithms to manage).
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 12 1998 - 12:59:25 CST
 |
 |