Re: log_buffer settings

A copy of this was sent to "Steven Cardinal" <scardinal_at_yahoo.com> (if that email address didn't require changing) On 27 Sep 1999 07:48:47 PDT, you wrote:

>Can you further explain the issue of a large log_buffer setting? I have
>mine sized quite large and, if I understand you correctly, I am probably
>hurting my performance. I have 8.0.4 on NT 4.0 with 1.25 Gb. We maintain a
>warehouse of data. We load large quantities at a time via sqlldr (direct
>load option is used) - between 2 and 8 million records at a shot. We then
>have to scrub this data with various procedures looking for bogus
>characters, swear words, etc. The we de-dup it. By watching (and probably
>mis-interpreting) stats and knowing that all of our important processing is
>with large quantities of data, I have sized the system as follows:
>
>Database Buffers: 320,000 KBytes
>Fixed Size: 47 KBytes
>Redo Buffers: 100,008 KBytes
>Variable Size: 20,252 KBytes
>
>The sort_area_size is 25 Mb and my db_block_size is 8K
>

redo log buffers are flushed when:

• you fill them about 1/3 full (33meg in your case).
• you commit.
• 3 seconds pass by.

so, if you never generate 33meg of log in 3 seconds -- you've wasted RAM you could use elsewhere for example.

so, if you generate 33meg of log with 100meg of online redo log in 2 seconds -- we've never flushed it. Now you commit. Now we have 33meg to flush (and you to wait) before we can go on. I would rather be flushing as we are going.

there are 2 examples where it is bad.

>Further discussion is appreciated, as I am still new to Oracle and welcome
>any real world info that can be passed along
>
>Steve
>
>Thomas Kyte wrote<snip>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 27 1999 - 15:38:26 CDT