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

Thanks to Jonathan, Stephan, and Yong:

1. Yes, the data was inserted via conventional insert, but this was long time ago. Since then the database has been shut down with "shutdown immediate" several times.
2. This is the simplest possible query, something like

select /*+ full(d) */

   count(*)
from

   table_name partition (partition_name) d;

The hint is to prevent it from using index.

3. The same query runs fine if a smaller partition is chosen (with less than 500,000 blocks) or if PARALLEL hint (/*+ fuis used. As far as I can see from V$SESSION_LONGOPS, parallel query splits the 1,000,000 blocks partition into chunks of approx. 125,000 blocks. I thought that if "delayed block cleanout" is a problem, then Oracle will need to do it during parallel scan. However, a scan without parallel hint experienced "free buffer waits" problem when run AFTER parallel scan (on the same partition).

4. No, the query does not have any "group by" or "order by", and it is not using db link.

5. sort_area_size = 150 MB
Other relevant parameters with non-default values are

db_block_size = 8K
db_block_buffers = 50000
db_block_max_dirty_target = 15000
db_block_lru_latches = 70
db_file_multiblock_read_count = 16

The box has 8 CPUs

Thanks Received on Mon Jan 13 2003 - 01:54:47 CST