Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Tuning the Oracle 8.0 Database Writer Processes


Oracle Tips by Burleson
 

We may remember that earlier in this chapter we stated that the database writer (DBWR) background processes are responsible for writing dirty data blocks into disk.

For highly active databases, the database writer is a very important Oracle function since the DBWR processes govern the rate at which changed blocks are written to disk. Let's begin with a brief overview of the functions of the DBWR and see how it writes data blocks to disk.

When Oracle detects that a data block in the buffer cache has been changed, the data block is marked as “dirty.” Once marked as dirty, the block is queued for a database writer process, which writes the block back to the disk. The DBWR background processes have two responsibilities:

  • Scanning the buffer cache, looking for dirty buffers to write

  • Writing the dirty buffers to the disk

It is very important to note that every operating system has implemented disk I/O very differently. Hence, the internal process of writing data blocks is specific to the operating system.

Tuning the database writer processes is very important. Within the Oracle data buffer, read-only data blocks can age-out of the buffer, but dirty blocks must be retained in the data buffer until the database writer has copied the block to disk.

Note: The internal statistics for the database writer changed dramatically starting with Oracle8i.  Hence some of the following material will not apply to STATSPACK after release 8.0.5.  For example, Oracle8i no longer includes the “write request” statistic in Oracle8i. Also, the average write queue statistic has been removed from the utlestat report. It has been removed because the use of multiple buffer pools and asynchronous DBWR processes now mean that the size of the write queue is not a useful metric."

Oracle 8.0 parameters

Oracle 8.0 offers two Oracle parameters for implementing multiple database writers:

  •  dbwr_io_slavesThis is a method whereby a master database writer process spawns additional slave processes to handle the database writes. This option is also used on database servers where asynchronous I/O is not supported. Some UNIX server systems (such as Solaris and AIX) support asynchronous I/O. If your platform does not support the asynchronous I/O, you can simulate the asynchronous I/O by defining I/O slave processes.

  • db_writer_processesStarting with Oracle 8.0.5, Oracle8 supports true multiple DBWR processes, with no master/slave relationships. This parameter requires that the database server support asynchronous I/O.

Remember, you should only implement multiple database writers when you have a clear indication of writing backlogs. Implementing db_io_slaves or db_writer_processes comes at a cost in server resources. The multiple writer processes and I/O slaves are intended for large databases with high I/O throughput, and you should only implement multiple database writers if your system requires the additional I/O throughput.

In addition, there are several other Oracle parameters that affect the behavior of the DBWR processes:

  •  db_block_lru_latchesThis is the number of LRU latches for database blocks. You cannot set db_writer_process to a value that is greater than db_block_lru_latches. Note that db_block_lru_latches goes to an undocumented parameter in Oracle and is automatically set by Oracle.

  • log_checkpoint_intervalThis controls the number of checkpoints issued by the DBWR process. Frequent checkpoints make recovery time faster, but it may also cause excessive DBWR activity during high-volume update tasks. The minimum value for log_checkpoint_interval should be set to a value larger than the largest redo log file.

  • log_checkpoint_timeoutThis should be set to zero.

NOTE: (Oracle 8.0 only) Multiple db_writer_process and multiple dbwr_io_ slaves are mutually exclusive. If both are set, the dbwr_io_slaves parameter will take precedence.

Now that we understand how the DBWR processes work, let's see where we can go to find information about their performance.

Monitoring the Database Writers with STATSPACK (Pre 8.1.5)

We can begin our journey by looking at the stats$sysstat table. There are numerous statistics that STATSPACK keeps in this table that provide information about the DBWR behavior.  Here is the listing from Oracle8i.

sql> select distinct name from stats$sysstat where name like 'DBWR%'
NAME
----------------------------------------------------------------
DBWR Flush object call found no dirty buffers
DBWR Flush object cross instance calls
DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoint write requests
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR incr. ckpt. write requests
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buffer
DBWR skip hot writes
DBWR summed scan depth
DBWR timeouts
DBWR transaction table writes
DBWR undo block writes

Here is the listing from Oracle.  Note the changed statistics.

SQL> select distinct name from stats$sysstat where name like 'DBWR%';

NAME
----------------------------------------------------------------
DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR fusion writes
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buffer
DBWR summed scan depth
DBWR transaction table writes
DBWR undo block writes

Most of these values are of no interest, but a few of them are quite useful. Let's look at the functions of some of the useful values:

  • DBWR checkpoints This is the number of checkpoint messages that were sent to the DBWR from Oracle. During checkpoint processing, the log writer hands over to the DBWR a list of modified blocks that are to be written
    to disk.

  • DBWR buffers scanned This is the number of buffers looked at when scanning for dirty buffers to write to the database. This count includes all inspected buffers, including both dirty and clean buffers.

  • Summed dirty queue length This is the sum of the queue length after every write request has completed.

  • Write requests This is the total number of write requests that were made by Oracle to the database writers.

The main task is determining if the default configuration for the database writers is sufficient for your database. The summed dirty queue length and write requests are the two metrics in STATSPACK that are useful for measuring the efficiency of the DBWR background processes.

By dividing the summed dirty queue length by the number of write requests, you can get the average length of the queue following the completion of the write.

The following STATSPACK query will measure the dirty queue length for the time period between each snapshot. Any value above 100 indicates a shortage of DBWR processes.

rpt_dbwr_alert.sql (pre 8.1.5 only)

-- Written by Donald K. Burleson   1/25/01

set pages 999;
 
column c1 heading "Write request length" format 9,999.99
column c2 heading "Write Requests"       format 999,999
column c3 heading "DBWR checkpoints"     format 999,999
column mydate heading 'Yr.  Mo Dy  Hr.'  format a16

select distinct
   to_char(snap_time,'yyyy-mm-dd HH24') mydate,
   a.value/b.value                      c1,
   b.value                              c2,
   c.value                              c3
from
   stats$sysstat  a,
   stats$sysstat  b,
   stats$sysstat  c,
   stats$snapshot sn
where
   sn.snap_id = a.snap_id
and
   sn.snap_id = b.snap_id
and
   sn.snap_id = c.snap_id
and
   a.name = 'summed dirty queue length'
and
   b.name = 'write requests'
and
   c.name = 'DBWR checkpoints'
and
   a.value > 0
and
   b.value > 0
and
   a.value/b.value > 3
;

In the output from this report, we see that the average queue length is quite small, ranging from 2 to 5. According to Oracle, you should only become concerned if the average queue length after writes is more than 50 blocks.

Yr.  Mo Dy  Hr.  Write request length Write Requests DBWR checkpoints
---------------- -------------------- -------------- ----------------
2001-12-25 01                    4.71         20,103           44,016
2001-12-25 02                    4.62         20,520           44,260
2001-12-25 03                    4.51         21,023           45,235
2001-12-25 04                    4.31         22,002           47,198
2001-12-25 05                    4.13         22,948           49,134
2001-12-25 06                    3.96         23,902           51,055
2001-12-25 07                    3.81         24,867           52,991
2001-12-25 08                    3.67         25,808           54,913
2001-12-25 09                    3.54         26,731           56,797
2001-12-25 10                    3.42         27,667           58,673
2001-12-25 11                    3.31         28,618           60,622
2001-12-25 12                    3.20         29,580           62,544
2001-12-25 13                    3.10         30,524           64,489
2001-12-25 14                    3.01         31,492           66,418
2001-01-01 01                    4.70         13,492           31,992
2001-01-01 02                    4.37         14,481           34,007
2001-01-01 03                    4.09         15,486           36,032

We can easily extend the STATSPACK report to report on the average values, aggregated by hour of the day and day of the week. This will help the DBA identify trends in database write activity. Next is an example of the STATSPACK script that averages the queue length values by hour of the day:

rpt_dbwr_hr.sql (Pre 8.1.7 only)

set pages 999;

column c1 heading "Write request length" format 9,999.99
column c2 heading "Write Requests"       format 999,999
column c3 heading "DBWR checkpoints"     format 999,999

select distinct
   to_char(snap_time,'HH24') mydate,
   avg(a.value/b.value)                      c1
from
   stats$sysstat  a,
   stats$sysstat  b,
   stats$snapshot sn
where
   sn.snap_id = a.snap_id
and
   sn.snap_id = b.snap_id
and
   a.name = 'summed dirty queue length'
and
   b.name = 'write requests'
and
   a.value > 0
and
   b.value > 0
group by
   to_char(snap_time,'HH24')
;

Here is the output from this script. We can now easily take this output and plot a graphical representation on the data from an Excel spreadsheet (see Figure 9-13).

Yr.  Mo Dy  Hr.  Write request length
---------------- --------------------
00                               1.11
01                               2.60
02                               2.51
03                               2.43
04                               1.99
05                               1.91
06                               1.84
07                               1.55
08                                .96
09                                .98
10                                .80
11                                .75
12                                .76
13                                .74
14                                .74
15                                .71
16                                .61
17                                .99
18                                .97
19                                .93
20                                .86
21                                .89
22                                .86
23                                .95

Figure 9-67: Average queue length after write completion by hour of day

Here we see that the DBWR is busiest in the early morning hours between midnight and 8:00 a.m. This is because this database does its batch updates during this processing window.

We can slightly alter this script and aggregate the average queue length, summarized by the day of the week. Here, we take the averages and group them by day.

rpt_dbwr_dy.sql (pre 8.1.7 only)

Set pages 999;


column c1 heading "Write request length" format 9,999.99
column c2 heading "Write Requests"       format 999,999
column c3 heading "DBWR checkpoints"     format 999,999
Column c4 noprint

select distinct
   to_char(snap_time,'day') mydate,
   decode(to_char(snap_time,'day'),'sunday',1,'monday',2,'tuesday',3,
   'wednesday',4,'thursday',5,'friday',6,'saturday',7) c4,
   avg(a.value/b.value)                      c1
from
   stats$sysstat  a,
   stats$sysstat  b,
   stats$snapshot sn
where
   sn.snap_id = a.snap_id
and
   sn.snap_id = b.snap_id
and
   a.name = 'summed dirty queue length'
and
   b.name = 'write requests'
and
   a.value > 0
and
   b.value > 0
group by
   decode(to_char(snap_time,'day'),'sunday',1,'monday',2,'tuesday',3,
   'wednesday',4,'thursday',5,'friday',6,'saturday',7),
   to_char(snap_time,'day')
;

Here is the output. Again, it is simple to create a graph from this output.

Yr.  Mo Dy  Hr.  Write request length
---------------- --------------------
sunday                           1.96
monday                           2.31
tuesday                           .43
wednesday                         .10
thursday                         1.53
friday                            .18
saturday                          .02


 

Figure 9-14 shows the graph. Here, we see that the overall efficiency of the database writer is fine, but the peak write times are on Monday, Wednesday, and Sunday.

Figure 9-68: Average queue length after write completion by day of week

In summary, the database writer processes will work fine for most all Oracle databases without modification. However, when you detect that the summed dirty queue length is too high, you can look at increasing the number of database writer processes.


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter