Log file sync caused by more commit(五)
'sync scn'=0x6f01b3c4
time in wait: 2.779358 sec
timeout after: never
wait id: 32770
blocking: 0 sessions
current sql:
short stack: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwai
tctx()+163<-kcrf_commit_force()+972<-kcbdsy()+44<-ksupop()+1090<-opiodr()+1008<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o(
)+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244<-_start()+36
wait history:
* time between current wait and wait #1: 0.000208 sec
1. event: 'SQL*Net message from client'
time waited: 0.001506 sec
wait id: 32769 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000011 sec
2. event: 'SQL*Net message to client'
time waited: 0.000002 sec
wait id: 32768 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000845 sec
3. event: 'SQL*Net message from client'
time waited: 0.001030 sec
wait id: 32767 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
}
and is blocked by 'instance: 2, os id: 11611, session id: 1653',
which is a member of 'Chain 1'.
Chain 6 Signature: 'log file parallel write'<='log file sync'
Chain 6 Signature Hash: 0x9a625f43
Combine the ash report that there are more tan 20000 commit operations.
[sql]
Call Type Count % Activity Avg Active
---------------------------------------- ---------- ---------- ----------
COMMIT 22,124 55.99 12.29
V8 Bundled Exec 10,652 26.96 5.92
OAUTH 2,249 5.69 1.25
LOGOFF 584 1.48 0.32
-------------------------------------------------------------
With the awr report top 5 event
[sql]
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 269,309 33,931 126 75.7 Commit
log buffer space 104 5,424 52157 12.1 Configurat
buffer busy waits 2,593 3,580 1381 8.0 Concurrenc
log file switch completion 24 892 37155 2.0 Configurat
DB CPU 888 2.0
^LHost CPU (CPUs: 64 Cores: 32 Sockets: 4)
The log file sync wait event which average wait time is 126ms is percent 75.7 of DB time. In order to reduce this wait event, it should modify the application software to reduce commit operation.