Hello.
We have OLTP system which uses ORACLE 10.2.0.5.
And some time we face with abnormal behavior of system, DB hangs from some seconds till some minutes.
ASH reports shows top events:
Event % Event P1 Value,P2 Value,P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
buffer busy waits 39.11 "574","1209954","1" 13.97 file# block# class#
"566","3822536","1" 7.16
"576","1108085","1" 4.37
Top blocking sessions:
Blocking Sid % Activity Event Caused % Event User Program # Samples Active XIDs
1108, 1 41.38 log buffer space 31.07 SYS oracle@dbfs (LGWR) 11/11 [102%] 0
log file sync 10.31
939, 3 13.92 buffer busy waits 13.92 PCP JDBC Thin Client 11/11 [102%] 0
940, 3 7.16 buffer busy waits 7.16 PCP JDBC Thin Client 11/11 [102%] 0
If I look in v$active_session_history, I can see, that all users sessions are blocked by LGWR (1108,1)
And session LGWR (1108,1) is blocked by CKPT (1107,1), which hangs with event “direct path write” on some DB file.
So looks like ORACLE waits CKPT process, CKPT renew headers of online db-files.
But some times it takes seconds, but some times it lasts minutes.
also we tried resolve issue with "buffer busy waits", set FREELIST on table with hot blocks, but don't see any changes in DB.
Can someone give some advice what we can improve on our instance?
Edited by: kpetrov54 on 30.05.2012 19:32