Average length of the dirty buffer write queue

Summary
This script lists the Dirty Queue Length. The longer the queue length, the more trouble the dbwr is having keeping up. If the average length of the dirty buffer write queue is larger than the value calculated before, increase db_file_simultaneous_writes or db_files. Also check for disks that are doing many more IOs than other disks.
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;
Reviews
Categories
Filters
Search