Log Buffer Size

Printable View

10-12-2006, 03:50 AM
M.Shakeel Azeem

Log Buffer Size

i execute the following script
SQL> @c:\sga_statistics.sql;

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> /
> SGA CACHE STATISTICS
> ********************
> SQL Cache Hit rate = 99.24
> Dict Cache Hit rate = 99.67
> Buffer Cache Hit rate = 99.88
> Redo Log space requests = 1440
>
> INIT.ORA SETTING
> ****************
> Shared Pool Size = 52428800 Bytes
> DB Block Buffer = 32768 Blocks
> Log Buffer = 20971520 Bytes
>
*** HINT: Log Buffer value is rather low!

should i have to increase the size of log_buffer Because Redo Log space requests = 1440 is high?
What u'll suggest Sir?
thanx in advance
10-12-2006, 04:30 AM
Thomasps

Your log buffer is too big. What is your redo log file size? "Redo Log space requests" shows the number of times a user process wait for a redo log file not for buffer.
10-12-2006, 06:22 AM
M.Shakeel Azeem

Quote:

Originally Posted by Thomasps

Your log buffer is too big. What is your redo log file size? "Redo Log space requests" shows the number of times a user process wait for a redo log file not for buffer.

we are having 3 redo log file of 1M
should i increase size to 20m and add 1 more redo file?
10-12-2006, 06:30 AM
pareshjavkar

Hi,

I think increasing Redo Log size and Decreasing Log Buffer size might just help you.

Your Log_buffer is too big
10-12-2006, 06:31 AM
davey23uk

1M? are you serious??
10-12-2006, 06:50 AM
Thomasps

20MB Log buffer and 1MB Redo log file.. Funny..! And that is the origion of your problem.

Make 3 - 5 MB Log buffer and 25MB Log file atleast - Again depends on your Redo generation.
10-12-2006, 07:49 AM
pareshjavkar

Dear Thomas,

What is advisable size of REdo Log file, if Redo generation for the day is around 15 for 100MB size.
10-12-2006, 08:11 AM
Thomasps

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.
10-12-2006, 12:17 PM
ixion

Quote:

Originally Posted by Thomasps

20MB Log buffer and 1MB Redo log file.. Funny..! And that is the origion of your problem.

Make 3 - 5 MB Log buffer and 25MB Log file atleast - Again depends on your Redo generation.

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

Try starting out at 512K for you log buffer....
10-12-2006, 12:19 PM
ixion

Quote:

Originally Posted by pareshjavkar

Dear Thomas,

What is advisable size of REdo Log file, if Redo generation for the day is around 15 for 100MB size.

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.
10-12-2006, 01:59 PM
KenEwald

Quote:

Originally Posted by ixion

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
10-12-2006, 04:09 PM
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.
10-12-2006, 04:32 PM
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
10-13-2006, 02:31 AM
pareshjavkar

Quote:

Originally Posted by Thomasps

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 ?
10-13-2006, 03:14 AM
yxez

Hi friend paresh....i really appreciate your dedication to learn :cool: I wish i could be like you too. Let me just share my thoughts :D
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. :)
10-13-2006, 03:21 AM
pareshjavkar

Quote:

Originally Posted by yxez

Hi friend paresh....i really appreciate your dedication to learn :cool: I wish i could be like you too. Let me just share my thoughts :D
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.
10-13-2006, 03:25 AM
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 :D ....i just wait daveys comment
10-13-2006, 04:31 AM
pareshjavkar

Quote:

Originally Posted by 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 :D ....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
10-16-2006, 04:20 AM
Thomasps

Quote:

Originally Posted by ixion

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.
10-16-2006, 04:22 AM
Thomasps

Quote:

Originally Posted by pareshjavkar

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".
10-16-2006, 02:22 PM
ixion

Quote:

Originally Posted by 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

Huh? Define Sand-bagging i/o?
It all depends on how the system is layed out...
Sure I/O will be terrible if you share drives between the arch file systems and redo logs (raw, file system).... But If you don't and have dedicated devices for each, then what does it matter if occasionally I/O spikes for 10 seconds to generate an arch log?

It better to generate fewer archive logs to reduce checkpoints, but in the end this must be ballanced by many other factors(ie data guard, backups etc).
..

I guess all i'm saying is a 2M log file is generally a bad idea, no matter what the system. It might be ok for say a read only or a DW where all loads are direct path and very little redo is generated.

Hope this helps,
Ken
01-13-2013, 01:49 PM
THPX

Hi all,
good advices, I was wondering if there is a way to see how much memory the redo buffer is consuming..I know we have the size , but how much of that size is used in a specific moment ???

Thanks