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

Hi,

is there a "group by" or "order by" contained in your query? If so, the buffer cache may be filled with sort temp blocks, which are all modified and need to be flushed by dbwr to the temp tbs.

If this is the case, make sure your sort_area_size is at least 1 MB in order to make oracle use the sort direct buffers.

Regards
Stephan

"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> 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.
Received on Fri Jan 10 2003 - 03:38:44 CST