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

Hi,

could you try with the parameters db_block_max_dirty_target and db_block_lru_latches with default values.

Dias

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0301122354.5a7df7c1_at_posting.google.com>...
> 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 - 11:03:20 CST