Hello,
we migrated from a 12.1.0.1 standard edition without asm and sles 11 to an 12.2.0.1 enterprise edition with asm, sles 12 and SSD for Data and Redolog.
After that we have on high workload times a log file sync avg wait about 50ms.
From the AWR:
| Begin Snap: | 4257 | 20-Dez-17 09:00:12 | 317 | 54.7 |
| End Snap: | 4262 | 20-Dez-17 14:00:25 | 261 | 58.9 |
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Avg Wait | % DB time | Wait Class |
|---|
| DB CPU | | 38.1K | | 52.1 | |
| log file sync | 548,571 | 30.8K | 56.15ms | 42.1 | Commit |
| direct path read | 6,117,691 | 1978 | 323.33us | 2.7 | User I/O |
| db file sequential read | 4,253,008 | 1731.8 | 407.20us | 2.4 | User I/O |
Most of the time the wait time is ok but there a about 1% with more than 32ms
| Event | Waits 1ms to 32ms | <512us | <1ms | <2ms | <4ms | <8ms | <16ms | <32ms | >=32ms |
|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| log file sync | 496K | 8.7 | 59.4 | 23.0 | 6.2 | 1.8 | .1 | .0 | .9 |
| | | % of Total Waits |
|---|
| Event | Waits 4s to 2m | <2s | <4s | <8s | <16s | <32s | < 1m | < 2m | >=2m |
|---|
|
|
|
|
|
|
|
|
|
|
|
|
| log file sync | 4388 | 99.2 | .1 | .4 | .3 | | | | |
I think the log file parallel write is ok, so it is no I/O related issue:
| Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait | Waits /txn | % bg time |
|---|
| log file parallel write | 841,239 | 0 | 1,142 | 1.36ms | 1.56 | 30.30 |
We increased the cpu priority of the lgwr processes, but it does not help.
Do you have any suggestions?
Thanks