RE: log buffer space wait
Vivek - When you've got Cary Millsap quoting Steve Adams, well the advice just doesn't get any better than that. Go with their advice. As I pointed out, I replied because at that point nobody had posted yet. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:34 PM To: Multiple recipients of list ORACLE-L http://www.ixora.com.au/q+a/0008/24162020.htm Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, September 02, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Vivek Good question. At this point I haven't seen any replies to your request. The specific wait points to the log buffer itself as being too small, not particularly the redo logs themselves. Your init.ora parameters show this buffer as being only 10-meg. I would suggest you increase it. But your redo logs are only 10-meg themselves, so you should consider increasing the size of the logs while you are at it or you may hit that as the next limit. Since the redo logs are continuously written, it is usually recommended that they be on a device that is devoted to redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 8:39 AM To: Multiple recipients of list ORACLE-L At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing log buffer space wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB IdInstance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41 23 1.5 End Snap: 241 01-Sep-03 04:00:55 24 1.7 Elapsed: 51.23 (mins) Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time 22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn -- -- -- log buffer space1,817,258139566,516312 54.4 latch free 2,964,652892,594334,120113 88.8 log file sync 55,230 21,210 43,488787 1.7 buffer busy waits 62,839 10,250 26,129416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 PctAvg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss(s) Requests Miss -- -- -- -- -- post/wait queue 97,3574.70.4577 1,871,155 249.5 process allocation 7517.23.0 10 747 0.5 process group creation1,5020.0 0 0 redo allocation 24,482,283 13.50.4 ## 0 redo copy
RE: log buffer space wait
These are cumalitive times for a few hundred processes. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/03/2003 10:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: log buffer space wait I have one question regarding these wait times, may be dump. In the following wait events what is the unit of measurement for Time, I believe its in seconds as per the documentation. If yes then here stats are collected for only 51.23 Minutes (Appx 3073 seconds), how come Oracle is showing 566,516 seconds wait for log buffer space. I would appreciate if someone can explain. Thanks Top 5 Timed Events ~~ % Total EventWaits Time (s) Ela Time --- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:39 AM To: Multiple recipients of list ORACLE-L Subject: log buffer space wait At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing "log buffer space" wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41231.5 End Snap: 241 01-Sep-03 04:00:55241.7 Elapsed:51.23 (mins) Top 5 Timed Events ~~ % Total EventWaits Time (s) Ela Time --- ---- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits EventWaits Timeouts Time (s) (ms) /txn -- -- -- ---- log buffer space1,817,258139 566,516 312 54.4 latch free 2,964,652 892,594 334,120 113 88.8 log file sync 55,230 21,210 43,488 787 1.7 buffer busy waits 62,839 10,250 26,129 416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 Pct Avg Wait Pct Get Get Slps TimeNoWait NoWait LatchRequests Miss /Miss (s) Requests Miss -- -- -- -- -- post/wait queue 97,357 4.7 0.4 577 1,871,155 249.5 process allocation 751 7.2 3.0 10 747 0.5 process group creation 1,502 0.0 0 0 redo allocation 24,482,283 13.5 0.4 ## 0 redo copy675,177 99.6 1.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter NameBegin value(if different) - - -- _db_block_lru_latches 128 audit_trail FALSE background_dump_dest /oracle/ora92-64/rdbms/log/bdump compatible 9.2.0.3 control_files /lbmdb02/bm2/data02/control_01_bm core_dump_dest/oracle/ora92-64/rdbms/log/cdump cursor_sharingEXACT cursor_space_for_time TRUE db_block_size 8192 db_cache_size 1258291200 db_file_multiblock_read_count 32 db_files 1500 db_keep_
RE: log buffer space wait
I have
one question regarding these wait times, may be dump.In the following wait
events what is the unit of measurement for Time, I believe its in seconds as per
the documentation. If yes thenhere stats
are collectedfor only 51.23 Minutes (Appx 3073 seconds), how come
Oracle is showing 566,516 seconds wait for log buffer space. I would appreciate
if someone can explain.
Thanks
Top 5 Timed
Events
~~
% Total
Event
Waits Time (s) Ela Time
---
log buffer
space
1,817,258 566,516
56.24
latch
free
2,964,652 334,120
33.17
log file
sync
55,230 43,488
4.32
buffer busy waits
62,839
26,129 2.59
CPU
time
-Original Message-From: VIVEK_SHARMA
[mailto:[EMAIL PROTECTED]Sent: Tuesday, September 02, 2003
6:39 AMTo: Multiple recipients of list ORACLE-LSubject:
log buffer space wait
At a Benchmark a
particular Processing + DML intensive batch Job (Accounts Interest
Calculation) is experiencing
log buffer space wait
.
Config
:-
On Solaris 9
Cpu_count =
72
Oracle
9.2
Online Redo logfiles are
on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M
$ sar Command Outpuit %wio
only about 6 % for Volume containing the redo logfiles
Database in NOARCHIVELOG
mode
Qs. How can we reduce this
wait event ? Would RAW / RAID 1 for redo logfiles help ?
Qs. Could this be a Bug
?
*
STATSPACK report
for
DB
Name DB Id
Instance Inst Num Release
Cluster Host
---
--- ---
BM2
3727568246
bm2
1 9.2.0.3.0 NO
sleepy
Snap Id Snap Time
Sessions Curs/Sess Comment
--- -- -
---
Begin
Snap: 240 01-Sep-03 03:09:41
23 1.5
End
Snap: 241 01-Sep-03 04:00:55
24 1.7
Elapsed:
51.23 (mins)
Top 5 Timed
Events
~~
% Total
Event
Waits Time (s) Ela Time
---
log buffer
space
1,817,258 566,516
56.24
latch
free
2,964,652 334,120
33.17
log file
sync
55,230 43,488
4.32
buffer busy waits
62,839
26,129 2.59
CPU
time
22,018 2.19
-
Wait Events for DB:
BM2 Instance: bm2 Snaps: 240 -241
- s -
second
- cs - centisecond
- 100th of a second
- ms - millisecond
- 1000th of a second
- us - microsecond -
100th of a second
- ordered by wait time
desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event
Waits Timeouts Time (s)
(ms) /txn
-- -- --
log buffer
space
1,817,258 139
566,516 312 54.4
latch
free
2,964,652 892,594
334,120 113 88.8
log file
sync
55,230 21,210
43,488 787
1.7
buffer busy
waits
62,839 10,250
26,129 416
1.9
Latch Activity for DB:
BM2 Instance: bm2 Snaps: 240 -241
Pct Avg
Wait
Pct
Get Get
Slps Time NoWait
NoWait
Latch
Requests Miss /Miss
(s) Requests Miss
-- -- -- -- --
post/wait
queue
97,357 4.7 0.4
577 1,871,155 249.5
process
allocation
751 7.2 3.0
10 747
0.5
process group
creation
1,502
0.0
0
0
redo
allocation
24,482,283
13.5 0.4
##
0
redo
copy
675,177 99.6 1.1 ##
21,986,833 708.0
init.ora Parameters for
DB: BM2 Instance: bm2 Snaps: 240 -241
End value
Parameter
Name
Begin
value
(if different)
-
- --
_db_block_lru_latches
128
audit_trail
FALSE
background_dump_dest
/oracle/ora92-64/rdbms/log/bdump
compatible
9.2.0.3
control_files
/lbmdb02/bm2/data02/control_01_bm
core_dump_dest
/oracle/ora92-64/rdbms/log/cdump
cursor_sharing
EXACT
cursor_space_for_time
TRUE
db_block_size
8192
db_cache_size
1258291200
db_file_multiblock_read_count
32
db_files
1500
db_keep_cache_size
117440512
db_name
bm2
db_writer_processes
16
dml_locks
1
enqueue_resources
68
hash_join_enabled
FALSE
java_pool_size
16777216
large_pool_size
16777216
log_buffer
10485760
log_checkpoint_interval
0
log_checkpoint_timeout
0
log_checkpoints_to_alert
TRUE
max_dump_file_size
10240
max_rollback_segments
4000
nls_date_format
DD-MM-
open_cursors
1000
open_links
4
optimizer_index_cost_adj
15
optimizer_mode
CHOOSE
os_authent_prefix
log buffer space wait
At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing log buffer space wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41 23 1.5 End Snap: 241 01-Sep-03 04:00:55 24 1.7 Elapsed: 51.23 (mins) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log buffer space 1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time 22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn -- -- -- log buffer space 1,817,258 139 566,516 312 54.4 latch free 2,964,652 892,594 334,120 113 88.8 log file sync 55,230 21,210 43,488 787 1.7 buffer busy waits 62,839 10,250 26,129 416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss -- -- -- -- -- post/wait queue 97,357 4.7 0.4 577 1,871,155 249.5 process allocation 751 7.2 3.0 10 747 0.5 process group creation 1,502 0.0 0 0 redo allocation 24,482,283 13.5 0.4 ## 0 redo copy 675,177 99.6 1.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter Name Begin value (if different) - - -- _db_block_lru_latches 128 audit_trail FALSE background_dump_dest /oracle/ora92-64/rdbms/log/bdump compatible 9.2.0.3 control_files /lbmdb02/bm2/data02/control_01_bm core_dump_dest /oracle/ora92-64/rdbms/log/cdump cursor_sharing EXACT cursor_space_for_time TRUE db_block_size 8192 db_cache_size 1258291200 db_file_multiblock_read_count 32 db_files 1500 db_keep_cache_size 117440512 db_name bm2 db_writer_processes 16 dml_locks 1 enqueue_resources 68 hash_join_enabled FALSE java_pool_size 16777216 large_pool_size 16777216 log_buffer 10485760 log_checkpoint_interval 0 log_checkpoint_timeout 0 log_checkpoints_to_alert TRUE max_dump_file_size 10240 max_rollback_segments 4000 nls_date_format DD-MM
Re: log buffer space wait
RE: log buffer space wait
Title: Message They say that the silence is golden, but I don't think that the rule applies to the mailing lists. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of zhu chaoSent: Tuesday, September 02, 2003 11:29 AMTo: Multiple recipients of list ORACLE-LSubject: Re: log buffer space wait Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
RE: log buffer space wait
Vivek Good question. At this point I haven't seen any replies to your request. The specific wait points to the log buffer itself as being too small, not particularly the redo logs themselves. Your init.ora parameters show this buffer as being only 10-meg. I would suggest you increase it. But your redo logs are only 10-meg themselves, so you should consider increasing the size of the logs while you are at it or you may hit that as the next limit. Since the redo logs are continuously written, it is usually recommended that they be on a device that is devoted to redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 8:39 AM To: Multiple recipients of list ORACLE-L At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing log buffer space wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB IdInstance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41 23 1.5 End Snap: 241 01-Sep-03 04:00:55 24 1.7 Elapsed: 51.23 (mins) Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time 22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn -- -- -- log buffer space1,817,258139566,516312 54.4 latch free 2,964,652892,594334,120113 88.8 log file sync 55,230 21,210 43,488787 1.7 buffer busy waits 62,839 10,250 26,129416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 PctAvg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss(s) Requests Miss -- -- -- -- -- post/wait queue 97,3574.70.45771,871,155 249.5 process allocation 7517.23.0 10 747 0.5 process group creation1,5020.0 00 redo allocation 24,482,283 13.50.4 ##0 redo copy 675,177 99.61.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter NameBegin value (if different) - - -- _db_block_lru_latches 128 audit_trail FALSE background_dump_dest /oracle/ora92-64/rdbms/log/bdump compatible9.2.0.3 control_files /lbmdb02/bm2/data02/control_01_bm core_dump_dest/oracle/ora92-64/rdbms/log/cdump cursor_sharingEXACT cursor_space_for_time
RE: log buffer space wait
http://www.ixora.com.au/q+a/0008/24162020.htm Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, September 02, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Vivek Good question. At this point I haven't seen any replies to your request. The specific wait points to the log buffer itself as being too small, not particularly the redo logs themselves. Your init.ora parameters show this buffer as being only 10-meg. I would suggest you increase it. But your redo logs are only 10-meg themselves, so you should consider increasing the size of the logs while you are at it or you may hit that as the next limit. Since the redo logs are continuously written, it is usually recommended that they be on a device that is devoted to redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 8:39 AM To: Multiple recipients of list ORACLE-L At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing log buffer space wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB IdInstance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41 23 1.5 End Snap: 241 01-Sep-03 04:00:55 24 1.7 Elapsed: 51.23 (mins) Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time 22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn -- -- -- log buffer space1,817,258139566,516312 54.4 latch free 2,964,652892,594334,120113 88.8 log file sync 55,230 21,210 43,488787 1.7 buffer busy waits 62,839 10,250 26,129416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 PctAvg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss(s) Requests Miss -- -- -- -- -- post/wait queue 97,3574.70.4577 1,871,155 249.5 process allocation 7517.23.0 10 747 0.5 process group creation1,5020.0 0 0 redo allocation 24,482,283 13.50.4 ## 0 redo copy 675,177 99.61.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter NameBegin value (if different) - - -- _db_block_lru_latches 128
Re: log buffer space wait
Hi!
What's the value for _log_simultaneous_copies
parameter? You might want to manually increase it, even though it depends on
CPU_COUNT when left default.
I personally would increase log_buffer size as
well, if you got that many CPUs and concurrent sessions.
Tanel.
- Original Message -
From:
VIVEK_SHARMA
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, September 02, 2003 4:39
PM
Subject: log buffer space wait
At a Benchmark a
particular Processing + DML intensive batch Job (Accounts Interest
Calculation) is experiencing
log buffer space wait
.
Config
:-
On Solaris 9
Cpu_count =
72
Oracle
9.2
Online Redo logfiles are
on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M
$ sar Command Outpuit %wio
only about 6 % for Volume containing the redo logfiles
Database in NOARCHIVELOG
mode
Qs. How can we reduce this
wait event ? Would RAW / RAID 1 for redo logfiles help ?
Qs. Could this be a Bug
?
*
STATSPACK report
for
DB
Name DB Id
Instance Inst Num Release
Cluster Host
---
--- ---
BM2
3727568246
bm2
1 9.2.0.3.0 NO
sleepy
Snap Id Snap Time
Sessions Curs/Sess Comment
--- -- -
---
Begin
Snap: 240 01-Sep-03 03:09:41
23 1.5
End
Snap: 241 01-Sep-03 04:00:55
24 1.7
Elapsed:
51.23 (mins)
Top 5 Timed
Events
~~
% Total
Event
Waits Time (s) Ela Time
---
log buffer
space
1,817,258 566,516
56.24
latch
free
2,964,652 334,120
33.17
log file
sync
55,230 43,488
4.32
buffer busy waits
62,839
26,129 2.59
CPU
time
22,018 2.19
-
Wait Events for DB:
BM2 Instance: bm2 Snaps: 240 -241
- s -
second
- cs - centisecond
- 100th of a second
- ms - millisecond
- 1000th of a second
- us - microsecond -
100th of a second
- ordered by wait time
desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event
Waits Timeouts Time (s)
(ms) /txn
-- -- --
log buffer
space
1,817,258 139
566,516 312 54.4
latch
free
2,964,652 892,594
334,120 113 88.8
log file
sync
55,230 21,210
43,488 787
1.7
buffer busy
waits
62,839 10,250
26,129 416
1.9
Latch Activity for DB:
BM2 Instance: bm2 Snaps: 240 -241
Pct Avg
Wait
Pct
Get Get
Slps Time NoWait
NoWait
Latch
Requests Miss /Miss
(s) Requests Miss
-- -- -- -- --
post/wait
queue
97,357 4.7 0.4
577 1,871,155 249.5
process
allocation
751 7.2 3.0
10 747
0.5
process group
creation
1,502
0.0
0
0
redo
allocation
24,482,283
13.5 0.4
##
0
redo
copy
675,177 99.6 1.1 ##
21,986,833 708.0
init.ora Parameters for
DB: BM2 Instance: bm2 Snaps: 240 -241
End value
Parameter
Name
Begin
value
(if different)
-
- --
_db_block_lru_latches
128
audit_trail
FALSE
background_dump_dest
/oracle/ora92-64/rdbms/log/bdump
compatible
9.2.0.3
control_files
/lbmdb02/bm2/data02/control_01_bm
core_dump_dest
/oracle/ora92-64/rdbms/log/cdump
cursor_sharing
EXACT
cursor_space_for_time
TRUE
db_block_size
8192
db_cache_size
1258291200
db_file_multiblock_read_count
32
db_files
1500
db_keep_cache_size
117440512
db_name
bm2
db_writer_processes
16
dml_locks
1
enqueue_resources
68
hash_join_enabled
FALSE
java_pool_size
16777216
large_pool_size
16777216
log_buffer
10485760
log_checkpoint_interval
0
log_checkpoint_timeout
0
log_checkpoints_to_alert
TRUE
max_dump_file_size
10240
max_rollback_segments
4000
nls_date_format
DD-MM-
open_cursors
1000
open_links
4
optimizer_index_cost_adj
15
optimizer_mode
CHOOSE
os_authent_prefix
parallel_max_servers
0
parallel_min_servers
0
processes
3500
session_cached_cursors
50
sessions
3855
shared_pool_reserved_size
52428800
shared_pool_size
536870912
sort_area_retained_size
2048576
sort_area_size
12582912
timed_statistics
TRUE
transaction_auditing
FALSE
undo_management
AUTO
undo_tablespace
TBA_UNDO
user_dump_dest
/oracle/ora92-64/rdbms/log/udump
utl_file_dir
Re: log buffer space wait
After doing some reading on this, I suspect that your log files are too small. I don't see any info on log file size in this post, or at least, I can't find it. A large percentage of log buffer space waits occurr immediately after a log switch, so if you're switching logs very frequently, you will get a lot of these waits. A 10 meg buffer is actually pretty large. Since IIRC, you can only fill the log to 1/3 or 1 meg, whichever comes first, there's not a lot of benefit to making the log buffer larger. You will find quite a bit of info on this at Steve Adams site, www.ixora.com.au HTH Jared Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/02/2003 08:24 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: log buffer space wait Hi! What's the value for _log_simultaneous_copies parameter? You might want to manually increase it, even though it depends on CPU_COUNT when left default. I personally would increase log_buffer size as well, if you got that many CPUs and concurrent sessions. Tanel. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 02, 2003 4:39 PM Subject: log buffer space wait At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing "log buffer space" wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41231.5 End Snap: 241 01-Sep-03 04:00:55241.7 Elapsed:51.23 (mins) Top 5 Timed Events ~~ % Total EventWaits Time (s) Ela Time --- ---- log buffer space1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits EventWaits Timeouts Time (s) (ms) /txn -- -- -- ---- log buffer space1,817,258139 566,516 312 54.4 latch free 2,964,652 892,594 334,120 113 88.8 log file sync 55,230 21,210 43,488 787 1.7 buffer busy waits 62,839 10,250 26,129 416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 Pct Avg Wait Pct Get Get Slps TimeNoWait NoWait LatchRequests Miss /Miss (s) Requests Miss -- -- -- -- -- post/wait queue 97,357 4.7 0.4 577 1,871,155 249.5 process allocation 751 7.2 3.0 10 747 0.5 process group creation 1,502 0.0 0 0 redo allocation 24,482,283 13.5 0.4 ## 0 redo copy675,177 99.6 1.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter NameBegin value(if different) - - -- _db_block_lru_latches 128 audit_trail FALSE background_dump_dest /oracle/ora92-64/rdbms/log/bdump compatible 9.2.0.3 control_files /lbmdb02/bm2/data02/control_01_bm core_dump_dest/oracle/ora92-64/rdbms/log/cdump cursor_sharingEXACT cursor_space_for_time TRUE db_block_size 8192 db_cache_size 1258291200 db_file_multiblock_read_count 32 db_files 1500 db_keep_cache_size 117440512 db_namebm2 db_writer_process
Re: log buffer space
I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full. Set log_buffer to an easy number such as 600K. Create table with one column of a nice large size, e.g. varchar2(1000); Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat. Insert N rows into table. Taks snapshot and find changes. Vary the number of rows inserted until M rows does not result in a redo write M+1 rows results in a redo write. Check the redo size for M and M+1 rows. Under both 8.1.7.4 and 9.2.0.2, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer. (One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 16 March 2003 13:28 Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Jonathan: I have just sent a mail which has the test statistics. I would appreciate your comments on that.. Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8. KG Best Regards, K Gopalakrishnan -Original Message- Lewis Sent: Monday, March 17, 2003 7:14 AM To: Multiple recipients of list ORACLE-L I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full. Set log_buffer to an easy number such as 600K. Create table with one column of a nice large size, e.g. varchar2(1000); Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat. Insert N rows into table. Taks snapshot and find changes. Vary the number of rows inserted until M rows does not result in a redo write M+1 rows results in a redo write. Check the redo size for M and M+1 rows. Under both 8.1.7.4 and 9.2.0.2, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer. (One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 16 March 2003 13:28 Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
KG, I got the direct mail much quicker than the list mail - but I'll just echo the doubt I raised in the original. If you use 10046 level 8 to watch for log writer writes, I would expect you to see writes that could be of an almost arbitrary size. Assume a 3MB log buffer - we 'know' that Oracle triggers on 1MB: but what if the system is busy when a write is triggered and the users are generating lots of work ? LGWR writes 1MB - and in that time interval the users fill the other 2MB of the log buffer. The next write that LGWR does is 2MB. You could even argue that if the largest write you regularly see is 2/3 of the log buffer size, then the trigger is probably 1/3 of the log buffer size. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 17:42 Jonathan: I have just sent a mail which has the test statistics. I would appreciate your comments on that.. Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8. KG Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -Original Message- Sent: Friday, March 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Dennis, The 1MB condition was in 8i as well, at least in 8.1.7, as I mentioned in my original post. I was always under impresssion that the flush is triggered by the buffer being 1/3rd full; but KG mentioned it was 2/3rd, not 1/3rd and I was wondering where he got that information from and if it's documented. It true, that will certainly invalidate most of the what the fine manuals and Oracle Support analysts have said. Any ideas, anybody? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:18 AM Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: log buffer space
It's a fantastic ratio :-). Anjo Kolk wrote: 1 log switch per minute ;-) AK wrote: log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: log buffer space
Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
What's the size of your log buffer, and the redo log file? Do you see any errors/messages in alert.log file that are related to 'checkpoints' or 'log switching'? - Kirti -Original Message- Sent: Friday, March 14, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
Are there any equations which can say log buffer is small or large ? I mean how to find the proper size of log buffer . -ak - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 5:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data fileson the same disk) to othernot-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
RE: log buffer space
I think the only ways adding more log files might help is if your app is waiting on log file switch (%) events. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Friday, March 14, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Really? I thought that condition was there pre-9i as well. Maybe I'm mistaken. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- WILLIAMS Sent: Friday, March 14, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network
RE: log buffer space
Title: RE: log buffer space I think this might help... http://www.ixora.com.au/tips/tuning/log_buffer_size.htm -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] Sent: Friday, March 14, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: log buffer space What's the size of your log buffer, and the redo log file? Do you see any errors/messages in alert.log file that are related to 'checkpoints' or 'log switching'? - Kirti -Original Message- Sent: Friday, March 14, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
Dennis, The 1MB condition was in 8i as well, at least in 8.1.7, as I mentioned in my original post. I was always under impresssion that the flush is triggered by the buffer being 1/3rd full; but KG mentioned it was 2/3rd, not 1/3rd and I was wondering where he got that information from and if it's documented. It true, that will certainly invalidate most of the what the fine manuals and Oracle Support analysts have said. Any ideas, anybody? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:18 AM Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
RE: log buffer space
Maybe finger stutter. I've never seen it mentioned anywhere except as 1/3. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Friday, March 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Dennis, The 1MB condition was in 8i as well, at least in 8.1.7, as I mentioned in my original post. I was always under impresssion that the flush is triggered by the buffer being 1/3rd full; but KG mentioned it was 2/3rd, not 1/3rd and I was wondering where he got that information from and if it's documented. It true, that will certainly invalidate most of the what the fine manuals and Oracle Support analysts have said. Any ideas, anybody? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:18 AM Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET
RE: log buffer space
Arup I have a busy day, so the only resource I can quote without research is the Oracle Education Oracle9i Database Performance Tuning Student Guide. Lesson 5-3 reads: When the redo log buffer is one-third full. Hope that answers your question. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 1:04 PM To: Multiple recipients of list ORACLE-L Dennis, The 1MB condition was in 8i as well, at least in 8.1.7, as I mentioned in my original post. I was always under impresssion that the flush is triggered by the buffer being 1/3rd full; but KG mentioned it was 2/3rd, not 1/3rd and I was wondering where he got that information from and if it's documented. It true, that will certainly invalidate most of the what the fine manuals and Oracle Support analysts have said. Any ideas, anybody? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:18 AM Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http
RE: log buffer space
I remember seeing documentation on it for 8i as well. --- Pete Sharman [EMAIL PROTECTED] wrote: Really? I thought that condition was there pre-9i as well. Maybe I'm mistaken. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- WILLIAMS Sent: Friday, March 14, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
Re: log buffer space
log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
Well, there lies your problem. I suggest you do the following in this order of priority and see if they resolved the problem. (1) Log buffer of 320K is definitely small. Make it 4 MB (or at least 3MB) if you have enough memory. (2) Your log switching is 50 per hour?!! That's a little too much. Are you sure it's that high? If so, you need to bring it down to about 2 per hour. If 50/hr is your average rate of switch, then you need to size the logfiles at least 25 times current size to achieve the 2 per hour rate. Make your logs 500M each, again if you have enough disk space. (3) If possible, try to move the redo logs to non-RAID disks, preferably local (direct attached). HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:34 PM log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: log buffer space
50 log switches per hour is a LOT. Get it down to a handful or so by increasing the size of the redo logs with a factor 10 or so. Redo buffer sounds a bit small, and going to 1M in size might help a bit, although there's no guarantee for it. Mogens AK wrote: log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log buffer space
I'm sorry, I've missed whether this is a production database in archive log mode or not. If it is, log switches twice per hour may be too few. You should find out what your data loss tolerance is for this instance and adjust your redo log setup to accomodate that. I agree, 50 per hour is a bit much, but there are databases where 30 minutes of data lost could be quite hurtful. [EMAIL PROTECTED] 03/14/03 05:29PM Well, there lies your problem. I suggest you do the following in this order of priority and see if they resolved the problem. (1) Log buffer of 320K is definitely small. Make it 4 MB (or at least 3MB) if you have enough memory. (2) Your log switching is 50 per hour?!! That's a little too much. Are you sure it's that high? If so, you need to bring it down to about 2 per hour. If 50/hr is your average rate of switch, then you need to size the logfiles at least 25 times current size to achieve the 2 per hour rate. Make your logs 500M each, again if you have enough disk space. (3) If possible, try to move the redo logs to non-RAID disks, preferably local (direct attached). HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:34 PM log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official
Re: log buffer space
1 log switch per minute ;-) AK wrote: log buffer is 320 K and log files are 30M approx. there are about 50 log switch /hr . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:29 AM AK, Could you provide the redo log sizes and the log_buffers parameters, please. Also let us know the log switch frequency, too. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:14 AM Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
RE: log buffer space
Dear AK, So far we don't know your log buffer size. Can we have a glimpse of this parameter, log_buffer ? Rajesh -Original Message- Sent: Friday, March 14, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Do you guys think , adding more log file can help ? I think it should not , cuz any way logwriter is going to write in one datafile at a time , correct ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rajesh Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
log buffer space
I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
RE: log buffer space
Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data fileson the same disk) to othernot-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
Re: log buffer space
AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data fileson the same disk) to othernot-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
