Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck
 

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_slaves This 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_processes Starting 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_latches This 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 Oracle9i and is automatically set by Oracle.

• log_checkpoint_interval This 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_timeout This 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.

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

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)

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.

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)

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).

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)

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

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 "Oracle9i 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.