Buffer Busy Waits in a Read-Mostly Database?
11gR2 Standard Edition on Linux x86_64.
The database consists of two large tables (12GB+), one column of each of which has an Oracle Text index on it. Once a month, the two tables are refreshed from elsewhere, the Text indexes are updated, and then they sit there for the rest of the month, effectively read-only as users perform full text searches. The instance runs in 20GB of RAM, of which 16GB is given over to the (8K, default) buffer cache, 1GB SGA, 2GB PGA.
The principle recurring wait event on this database is buffer busy waits, for data blocks (i.e., not undo segment headers) -and the data blocks are those of the two tables (which have default freelists, freelist groups and initrans and maxtrans).
I get that during the monthly refresh, when there's loads of inserts happening, there could be lots of buffer busy waits. Since that refresh happens at weekends out-of-hours, waits during that time are not of any great concern.
My question is why there would be any such waits during the database's 'read-only' period, in between refreshes. I can positively guarantee that no DML is taking place then, yet the buffer busy waits still occur, from time to time.
On a possibly related note, why would I see lots of "consistent reads" during the 'read-only' period? The data isn't changing at all, so why would the database be busy doing consistent reads when current reads (I would have thought) would be good enough to get the data in the state it's actually at?