Log Buffer Size

[KenEwald]

I'd leave your log file alone at 100mb... If the db is generating more than 1 archive file per say 10 - 20 minutes.. then its time to increase the size.

Not picking on you, but I'm genuinely curious, is the 10-20 minute window for reasonable recovery .. Ex. "can't afford to loose up to 20 minutes of transactions"?

What are the ramifications of time based vs. size based logging?

I've heard that the redo should be larger for RAC. Is that true?

Ken

[ixion]

Ken,

Absolutely not for recovery purposes... The size/time is for performance reasons.. If you cut too many logs to quickyl ie.. lets say your cutting a redo log once every 30 seconds then Increase the size. This obviously begs the question of what is the right time... There's no one perfect answer.... Each cutting of a log does take some time and requires a checkpoint so the idea is to reduce the number of checkpoints by sizing the log files accordingly for each systems load.

Time based does have some impact especially for products like DataGuard which require the log be cut and transmitted.

Redo is redo, RAC should have very little to no impact. Except for potentially a bit more logging due to inter instance SCN synchronization. I've not taken much of a look at RAC from that aspect so YMMV.

[KenEwald]

I'm not sure I explained my question properly, let me try it this way.

Let's compare two systems that generate 3gb/hr.

One has 3 - 100mb redo logs and the other has 3 - 1gb redo logs.

If the transactions were evenly distriubted over time, the first system would generate a log file every 2 minutes. The other every 20 minutes.

What's the rationale for sand-bagging I/O? Would it be better to more evenly distribute it over time?

Just curious,

Ken

[pareshjavkar]

I assume you have 15 file and each file is 100MB generating 1500 MB redo per day. I will go for 300MB redo log files.

Dear Thomas,

I have 5 groups, 2 members in each group, each of size 100MB.

And my Redo generation for the Day is around 15 Log switches in 24 hours time Frame.

Log_Buffer=512K

Shall, i increase my log_buffer=1MB and try and reduce size of Redo Logs ?

[yxez]

Hi friend paresh....i really appreciate your dedication to learn I wish i could be like you too. Let me just share my thoughts
We have developed a new apps system....after which I set-up the
database for it to run on. I just remember I installed it with defaults
init parameters and it is running fine. I check the logs that you
are concern with and I saw the following:

log_group=3 the default ( with 1 member)
logsize =2M
log_buffer=163840
logs generated every 4 mins interval

My point is...as long as my OLTP meets the 3 seconds response time,
I dont need to change any parameters even if I used default values
during install.

Its really the application programs and design that has the greatest
affect of all to the performance.

[pareshjavkar]

Hi friend paresh....i really appreciate your dedication to learn I wish i could be like you too. Let me just share my thoughts
We have developed a new apps system....after which I set-up the
database for it to run on. I just remember I installed it with defaults
init parameters and it is running fine. I check the logs that you
are concern with and I saw the following:

log_group=3 the default ( with 1 member)
logsize =2M
log_buffer=163840
logs generated every 4 mins interval

My point is...as long as my OLTP meets the 3 seconds response time,
I dont need to change any parameters even if I used default values
during install.

Its really the application programs and design that has the greatest
affect of all to the performance.

Does your Response mean, i don't have to change any of the paramaters (log_buffer and redo log size). Correct me if i am wrong.

[yxez]

if u dont have problem with the users and response time then
leave it alone...its the users that rates ur performance in the company ....i just wait daveys comment

[pareshjavkar]

if u dont have problem with the users and response time then
leave it alone...its the users that rates ur performance in the company ....i just wait daveys comment

Any more inputs, Daveys and Thomas ?

FYI:

redo entries 12788742
redo buffer allocation retries 2362
redo log space requests 1061

[Thomasps]

Log buffer is still too big even at 3 MB...

Try starting out at 512K for you log buffer....

Yes, you can set to 512K.. But I felt, you are inviting "redo buffer allocation retries" as the redo size is on the lower side.

[Thomasps]

Any more inputs, Daveys and Thomas ?

FYI:

redo entries 12788742
redo buffer allocation retries 2362
redo log space requests 1061

Change the LOG_BUFFER to 1MB and see values for "redo buffer allocation retries".