I am troubleshooting the performance issue in one of my warehouse database. There are good number of sessions running, some of them are doing FTS and some of them doing sequential reads.

I have a 15 minutes interval stats pack setup and please find the top 5 timed wait events.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
free buffer waits 375,211 11,867 32 63.2
db file sequential read 267,049 3,562 13 19.0
db file scattered read 241,747 2,495 10 13.3
CPU time 490 2.6
enq: FB - contention 49 113 2303 .6
I understood that 'free buffer waits' is an indication that DBWR process is not clearing the buffer catch fast enough.

There are huge inserts and updates are happening in the DB. What ever it is, DBWR has to clean the dirty buffers which is not happening.

I am just confused by reading multiple documents posted by different people with different reasons. I want to clear some of my confusions.

1. Is only DBWR process responsible for this wait event?
2. while inserting and updating why too many blocks are reading into buffer? is this because of bad Indexes?
3. Is lack of stats on Indexes cause this?

A simple query from v$buffer_pool_statistics gives me the below information.

FBW - FREE_BUFFER_WAIT
WCW - WRITE_COMPLETE_WAIT
BBW - BUFFER_BUSY_WAIT

ID NAME FBW WCW BBW
---------- ------------ ------------ ------------------- ----------------
3 DEFAULT 118914584 630902 348394799
6 DEFAULT 0 0 0
Oracle - 10.2.0.4
SGA Target - 24GB
IO - Hitachi FC SAN

And, finally when I query v$sga_resize_ops I see the below output (last few lines)

db_cache_size 31-JAN-11 22078816256 22062039040 COMPLETE
shared_pool_size 31-JAN-11 771751936 788529152 COMPLETE
shared_pool_size 31-JAN-11 788529152 754974720 ERROR
db_cache_size 31-JAN-11 22062039040 22095593472 ERROR
no resize operations after 31st Jan and no rows in v$sga_current_resize_ops.

That means, Oracle is not doing SGA resize operations?

I am happy to provide if you need any more Information to guide me.

Thanks,