Database Reference
In-Depth Information
While writing dirty blocks by DBWR involves a random write operation, writing redo logs by LGWR involves a
sequential write operation. In order to guarantee that a data change made by a transaction is fully recoverable, the
transaction would have to wait until all the redo logs for the transaction and the system change number (SCN) of the
transaction are written to the online redo logs. In an OLTP database with high-volume transactions, this sequential
writing operation by LGWR can be the performance bottleneck that holds up these database transactions. A high
number of 'log file sync' wait events shown in the AWR report is a good indication of this performance bottleneck.
In order to optimize database performance, we need to focus on how to improve the LGWR's I/O performance by
allocating the online redo logs on storage that has high IOPS and low I/O latency.
Although user transactions don't have to wait for the DBWR directly. a slow DBWR writing operation could still
delay user transactions. As we know, at a database checkpoint, the DBWR process needs to write all the dirty blocks
to the data files in the storage. A slow DBWR process can delay the checkpoint operation; for instance, if during the
checkpoint the logwriter needs to reuse a log file that has redo information on a dirty block. The logwriter process has
to wait until DBWR finishes writing the dirty block for the checkpoint. The logwriter wait caused by the slow DBWR
process will slow down the user transactions. In this case, you will see the “Checkpoint not complete, cannot allocate
new log” message in the alert.log file. This indicates that the logwriter had to wait and transaction processing was
suspended while waiting for the checkpoint to complete. Therefore, the speed of writing dirty blocks to data files by
DBWR impacts database performance.
Having explained the requirements of storage I/O under different database workloads and its impact on
database performance, in the next section I will discuss the technology options that we should consider in the storage
architecture design for the Oracle RAC Database.
RAID Configuration
As you saw in the last section, the availability and performance of a RAC Database depends on the shared storage
infrastructure configuration. One of the most important storage configurations is RAID (Redundant Array of
Inexpensive Disks), which was introduced as a way to combine multiple disk drive components into a logical unit.
The following are some of the commonly used levels of RAID configuration adopted to enhance storage reliability and
performance:
RAID 0 for block level striping : data blocks are striped across a number of disks in a sequential
order. This is often used to improve storage performance as it allows reading data from
multiple disks in parallel. As RAID 0 doesn't provide mirroring or parity, any drive failure will
destroy the array.
RAID 1 for mirroring : two or more disks have exact copies of the data. This is often used to
achieve reliability against disk failures, as it saves more than one copy of the database. This
can improve the data read performance, as the data read can get the data from the faster of
multiple copies. But it will slow down the data writes, as the controller needs to write more
than one copy of the data. It also cuts the storage capacity and the number of disks in half.
This option requires minimal two disk drives. RAID 1 doesn't provide striping.
RAID 1+0 for mirroring and block level striping : As shown on the right side of Figure 5-2 , this
option create mirror sets by mirroring the disks, then stripe data blocks such as B1, B2 . . .
across these mirrored sets so it is also called as “stripe of mirrors”. It provides both reliability
and performance improvement.
RAID 0+1 for block level striping and mirroring : As shown on the left side of Figure 5-2 , this
option create two striping sets each with data blocks such as B1, B2 . . . , and then let them
mirror each other, so it is also called as “mirror of stripes”. This option achieves both reliability
and performance improvement.
 
Search WWH ::




Custom Search