-- #############################################################################################
--
-- %Purpose: Calculate 'Average Length of the Dirty Buffer Write Queue' for Performance Tuning
--
-- Use:      Oracle DBA
--
-- #############################################################################################
--
PROMPT ========================================================================
PROMPT This script lists the Dirty Queue Length. The longer the queue length,
PROMPT the more trouble the dbwr is having keeping up.
PROMPT
PROMPT Average length of the dirty buffer write queue:
PROMPT
PROMPT If this is larger than the value of:
PROMPT
PROMPT 1. (db_files * db_file_simultaneous_writes)/2 [Calculation-1]
PROMPT
PROMPT or
PROMPT
PROMPT 2. 1/4 of db_block_buffers [Calculation-1]
PROMPT
PROMPT which ever is smaller and also there is a platform specific limit
PROMPT on the write batch size (normally 1024 or 2048 buffers). If the average
PROMPT length of the dirty buffer write queue is larger than the value
PROMPT calculated before, increase db_file_simultaneous_writes or db_files.
PROMPT Also check for disks that are doing many more IOs than other disks.
PROMPT ========================================================================
--
column  "Write Request Length" format 999,999.99
--
SELECT  (SUM(DECODE(name,'db_files',value)) *
         SUM(DECODE(name,'db_file_simultaneous_writes',value)))/2
         "Calculation-1"
FROM v$system_parameter
WHERE name IN ('db_files','db_file_simultaneous_writes');
--
SELECT  (SUM(DECODE(name,'db_block_buffers',value)) / 4)
         "Calculation-2"
FROM v$system_parameter
WHERE name IN ('db_block_buffers');
--
SELECT SUM(DECODE(name,'summed dirty queue length',value)) /
       SUM(DECODE(name,'write requests',value)) "Write Request Length"
  FROM v$sysstat
 WHERE name IN ( 'summed dirty queue length','write requests')
   AND value > 0;