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
|
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: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 - 1000000th 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 10000
enqueue_resources 680000
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-YYYY
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 *
-------------------------------------------------------------
End of Report
