We are seeing some free buffer waits contention and I wanted to get some input from the forum participants if you have time to address it.
It is on an HP-UX 11.31 Itanium system running 11.2.0.3 of Oracle. This is a datawarehouse staging database and there are about 60 merge statements running in parallel (themselves also doing parallel dml) doing several million updates against 60 different tables with a total rowcount around 2 billion.
The dev team is putting in a more efficient update statement so that may in itself resolve these issues but still I'd like to see if the number of db writer processes we have makes sense. As far as I know we do not have asych io configured on our OS due to some bugs we have seen in the past. The server has 14 cpus and 95 gig of memory. Here are the top 5 events from our AWR report:
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
free buffer waits 319,324 261,188 818 46.08 Configuration
db file parallel read 134,710 62,404 463 11.01 User I/O
DB CPU 60,818 10.73
db file sequential read 11,783,603 26,032 2 4.59 User I/O
write complete waits 4,015 13,828 3444 2.44 Configuration
Does it make sense that I should increase the number of db writers?
db_writer_processes=4 in our system. With 14 cpus this is supposed to be enough as I understand it. But we have 60 dedicated server processes doing updates and only four db writer processes doing writes so it kind of makes sense to increase the number of writers.
I'm researching this on my own but I would appreciate any input you have on this issue.
Thanks,
Bobby