This could be a result of slow IO subsystem or DBWR is not performing well. Sessions waits on this wait event when some process want to modify the data blocks which are already marked by DBWR as "being written" and can not be modified untill they are written to disk.
It would be useful to check frequency of DBWR writing blocks to disk.
Cheers, -Harish Kalra
I've got a dbms_job that hangs. Tracing the job shows massive amounts of write complete waits:
*** 2006-09-26 13:08:01.500
WAIT #5: nam='write complete waits' ela= 976709 p1=202 p2=55183 p3=0
WAIT #5: nam='write complete waits' ela= 986042 p1=202 p2=55183 p3=0
WAIT #5: nam='write complete waits' ela= 986163 p1=202 p2=55185 p3=0
WAIT #5: nam='write complete waits' ela= 986249 p1=202 p2=55185 p3=0
Now I thought that the p1 and p2 values pointed to the file# and block# values. However, there is no file with an id of 202 in this database:
SQL> select file# from file$;
20 rows selected.
What am I missing? And what could cause a process to wait for hours for write complete waits