Re: free buffer waits in a database with no update activity

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0301091508.4862ce36_at_posting.google.com>...
> 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.

What if you alter system checkpoint before you run your query? If most of your free buffer waits time out, do you see any problem in alert.log? The query does not go across a dblink, does it?

Yong Huang Received on Fri Jan 10 2003 - 03:50:53 CST