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
