We have a database that experiences quite significant
"free buffer waits" - with no update activity in the database!
It is 8.1.6.3.0 (32bit) on Sun Solaris 8, with datafiles
created on HDS/Veritas VM/Veritas FS.
This is how it happens: I restarted the database and attempted to run
a query (full table scan) on a large partition
(1,000,000 8K blocks) of a partitioned table. It runs OK
up to 600,000 blocks (300 seconds), with "db file scattered read"
the only significant event in V$SESSION_EVENT. Then it "hits a wall":
number of "db file scattered read" waits stops growing,
while almost all time is spent in "free buffer waits" state.
Almost all "free buffer waits" result in timeout.
All this time I was the only user connected to the database.
I found Steve Adams's paper www.ixora.com.au/tips/mystery.doc
on DBWR tuning - it is a very good paper, but it presumes that
there is some update activity in the database.
Received on Thu Jan 09 2003 - 17:08:44 CST
