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

The give-away may be the 'restarted the database'.

How, and when, did the data get into the partitioned table ?

Your tablescan may be subject to delayed block cleanout if you did a normal insert for a huge amount of data and then shut the database down. In this case, as you run the scan, Oracle finds numerous blocks which are marked as 'in transaction', and checks the relevant undo transaction table slot to discover that the transaction has actually committed, and therefore cleans out the ITL and lock bytes on the block. Voila ! The block is dirty and has to be written, even though you are doing a query only activity and no-one else is connected to the database.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Vsevolod Afanassiev wrote in message
<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.