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