Increase in 'log file sync' waits while 'log file parallel write' remains unchanged
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Mon, 21 Sep 2009 15:52:59 -0700 (PDT)
Message-ID: <de608949-1d63-4f6e-a4f3-a222e31e72f5_at_u16g2000pru.googlegroups.com>
We have small 9.2.0.8 database on AIX, from time to time it experiences waits
on 'log file sync'. I am unable to explain these waits: - There is no increase in the number or size of transaction - While average wait on 'log file sync' increases from 10ms to 124ms average wait on 'log file parallel write' does not change much - approx 0.05 ms.
Date: Mon, 21 Sep 2009 15:52:59 -0700 (PDT)
Message-ID: <de608949-1d63-4f6e-a4f3-a222e31e72f5_at_u16g2000pru.googlegroups.com>
We have small 9.2.0.8 database on AIX, from time to time it experiences waits
on 'log file sync'. I am unable to explain these waits: - There is no increase in the number or size of transaction - While average wait on 'log file sync' increases from 10ms to 124ms average wait on 'log file parallel write' does not change much - approx 0.05 ms.
NORMAL PERIOD: Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 30,016.82
1,384.01
Logical reads: 10,273.75
473.70
Block changes: 179.17
8.26
Physical reads: 5.36
0.25
Physical writes: 6.98
0.32
User calls: 139.45
6.43
Parses: 152.69
7.04
Hard parses: 0.11
0.01
Sorts: 81.40
3.75
Logons: 0.01
0.00
Executes: 587.69
27.10
Transactions: 21.69
PERIOD WITH WAITS ON LOG FILE SYNC Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 29,366.27
1,385.22
Logical reads: 10,382.59
489.75
Block changes: 173.57
8.19
Physical reads: 4.87
0.23
Physical writes: 6.98
0.33
User calls: 137.17
6.47
Parses: 154.54
7.29
Hard parses: 0.08
0.00
Sorts: 82.76
3.90
Logons: 0.01
0.00
Executes: 591.21
27.89
Transactions: 21.20
So we have approx 20 transactions per second with transaction size 1.3K.
Wait event section
NORMAL PERIOD:
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
log file sync 20,386 0 201
10 1.1
db file sequential read 4,784 0 49
10 0.2
db file parallel write 1,186 0 27
23 0.1
control file parallel write 301 0 3
9 0.0
SQL*Net more data to client 67,998 0 2
0 3.5
log file parallel write 19,753 0 1
0 1.0
control file sequential read 368 0 0
1 0.0
latch free 19 15 0
8 0.0
direct path read 11 0 0
4 0.0
direct path write 11 0 0
2 0.0
buffer busy waits 26 0 0
1 0.0
process startup 1 0 0
21 0.0
enqueue 16 0 0
0 0.0
PERIOD WITH WAITS ON LOG FILE SYNC
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
log file sync 22,546 2,478 2,803
124 1.2
db file sequential read 3,605 0 46
13 0.2
db file parallel write 1,161 0 26
22 0.1
control file parallel write 304 0 25
82 0.0
control file sequential read 336 0 14
40 0.0
enqueue 26 0 4
172 0.0
log file sequential read 106 0 4
35 0.0
SQL*Net more data to client 69,471 0 2
0 3.6
log file parallel write 18,958 0 1
0 1.0
db file scattered read 102 0 1
9 0.0
log file switch completion 2 0 0
70 0.0
latch free 19 13 0
7 0.0
async disk IO 193 0 0
1 0.0
buffer busy waits 28 0 0
2 0.0
Received on Mon Sep 21 2009 - 17:52:59 CDT