Re: TOAD Statistics Analysis - DBWR avg scan depth - meaning please ??

dbwr summed scan depth is the total number of buffers DBWR has been told to scan by being posted by foreground processes looking for free buffers (each one passes in a 'scan depth' as it posts dbwr).

db_files is part of the equation telling Oracle how to calculate the dbwr block write batch, and one of the reasons for dbwr to write is that the number of blocks on the dirty leasts exceeds a certain fraction of the dbwr block write batch.

SO --
If db_files is large, then the dbwr block write batch is large, and the number of blocks allowed on the dirty list before a write takes place is large(r), so DBWR writes take place less frequently, so foreground processes are less likely to find that a buffer has been written out and thus become free so easily.

Actually --
There seems to be a buggy bit in 8.0.4/5 where the dbwr write batch seems to come out at 4096 irrespective of the setting for db_files, and I tend to find that this means more collisions waiting for write complete waits, and excessive redundant I/O. So whether or not the argument above (which I have to admit I just invented to explain the TOAD claim) is correct, it is sometimes worth keeping db_files down to reduce redundant I/O and even fixing up (down) the hidden parameter _db_block_write_batch (the actual value in use has to be found in x$ksvii).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Kevin A Lewis wrote in message ...
>In the T.O.A.D tool there is an Statistics Analysis page which reports a
>'DBWR avg scan depth' which when it is 'too high' produces a red light
alert
>and recommend that the
>
>#DB_FILES too high?
>
>What I would like to know is what the connection between the DB_FILES
>parameter is and this analysis value. The value appears to be based on the
>following query
>
>SELECT DECODE(DLU.VALUE, NULL ,0,0,0,DBS.VALUE / DLU.VALUE )
>FROM V$SYSSTAT DBS,V$SYSSTAT DLU
>WHERE DBS.NAME = 'DBWR summed scan depth'
>AND DLU.NAME = 'DBWR lru scans'
Received on Thu Jul 08 1999 - 11:32:18 CDT