Oracle db file parallel write and log file parallel write wait events

One. DB file Parallel Write wait event

Quote from the following blog:

Http://oradbpedia.com/wiki/Wait_Events_-_db_file_parallel_write

DB file Parallel write

The db file parallel write wait event belongs to the Oracle database writer (DBWR) process because it is the only process that writes blocks from the SGA to the data file. When it is written, the DBWR process compiles a set of dirty blocks, hands the batch to the operating system, and waits for the db file parallel write event to complete I/O.
Although user sessions never encounter db file parallel write wait events, this does not mean that they are never affected. If write-completion waits or idle buffer wait events are displayed in a user session, they may be affected by parallel write events for the db file.

If a user session needs to modify a block that happens to be in a DBWR write batch, it must wait for the write completion wait event until the block is fully written to disk. If the bulk is large, or the I/O subsystem is slow, the DBWR process will need to wait for the extra time I/O completes, so a user session is being written to the block.

If a user session is experiencing an idle buffer wait event, and the wait count increases steadily, this means that there is insufficient free blocks in the SGA. This can happen if the buffer cache is too small, or if the DBWR cannot keep up with the block dirty rate. One of the reasons why the DBWR process cannot keep up with dirty blocks is that it spends too much time on parallel write events in db files.

Parameters:

P1 = number of files that Oracle is writing.

P2 = number of blocks to write.

P3 = The total number of I/O requests that are identical to P2 because no multiple I/O is used.
Because P1 and P2 report the number of files and blocks, not the number of absolute files and blocks, DBAs cannot determine which objects are being written. However, a user session that waits for a write completion wait or an idle buffer wait event indicates the absolute file and block number in its P1 and P2 values.

Common causes and measures

DB file Parallel Write latency is usually the symptom of slow I/O subsystem or poor I/O configuration. This includes improper layout of the database files, incorrect I/O controller ratios, stripe size and/or RAID level errors, and insufficient disk (that is, there are several high-capacity disks with multiple low-capacity disks). The DBA needs to see the average I/O time. A good database and I/O subsystem should provide an average I/O wait time of no more than 2 pct seconds.

If this is a problem, the DBA should check I/O configuration by mapping I/O routes from the mount point to the Controller and controller to the Physical disk group and ensuring that the database files are properly placed. The command for this feature is the platform. Specifically and unfortunately, administrator privileges are usually required. For storage systems configured with the Veritas Volume Manager on the Sun platform, DBAs may be able to use the VXPRINT-HT command. DBAs should also use SAR-D,IOSTAT-DXN or equivalent tools to monitor usage (i.e. I/O throughput and bottlenecks) from the operating system level. If some disks are hit for a long time (that is, almost 100% busy) and the average queue length is greater than 3, the DBA needs to reschedule some database files.

In addition to ensuring that the I/O subsystem is configured correctly and that the database files are properly placed, DBAs should also enable the DBWR process (if the platform) to provide non-blocking I/o (disk_async_io = TRUE). Supports asynchronous I/O.

Large write batches increase the DBWR I/O wait time, especially in environments where data files are improperly placed. A deterministic flag that is too large for a write batch is when the user session starts waiting for the write to complete wait event. Prior to Oracle 8i, the _db_block_write_batch parameter determined the DBWR write batch size, and the value can be seen in x $ KVII. It is listed as the DB Write program IO cluster. In 8i and later, this parameter is replaced by _db_writer_chunk_writes and listed as a DBWR write block. A new parameter _db_writer_max_writes has been introduced to limit the number of outstanding DBWR I/Os. The DBA should ensure that the bulk size is not too large to cause write full waits and longer db files to be written in parallel, and will not be too small to cause long dirty queues and idle buffer waits. Also, keep in mind that since 8i, Oracle has made improvements that should break the write batch problem and DBAs should not be confused. Write-completion wait events are common in versions prior to 8i.

Prior to Oracle 8i

Sql> SELECT * from x $ kvii where Kviitag = ' KCBSWC ';

Oracle 8i and later

Sql> SELECT * from x $ kvii where Kviitag in (' KCBSWC ', ' KCBSCW ');

When the Db_block_max_dirty_target parameter is set too low, it can also cause over-waiting for a db file to be written in parallel and write a full wait event. This parameter is used to affect the amount of time that is required for execution. Instance recovery. When the number of dirty buffers exceeds the value of the parameter, DBWR writes the dirty buffer to the disk. This is called an incremental checkpoint. A smaller value provides a shorter instance recovery time, but it can cause the DBWR process to become more active, especially in active databases that are modifying a large number of buffers. This in turn may lead to excessive write completion waits and longer db file parallel write times. This parameter is hidden in 9i, and DBAs should not care about it.

Diagnosis

For system-level diagnostics, Query the V $ system_event view to determine if the average_wait is a problem.

Sql> SELECT * from v $ system_event where event = ' db file parallel write ';

When you are in V$system_event, you also look for accompanying events.

Sql> SELECT * FROM v $ system_event

Sql> where event in (' Write Complete Waits ', ' free buffer waits ');

This event occurs in DBWR. It indicates that DBWR is performing parallel writes to files and blocks. When the last I/O goes to disk, wait for the end. Wait Time:

Wait until all of the I/Os is completed.

Parameter Description

Requests this indicates the total number of I/O requests, which'll be the same as blocks.

Interrupt

Timeout This indicates the timeout value of Centiseconds to wait for the IO completion.

Two. log file parallel write wait event

Quote from the following blog:

Http://oracle-dox.net/McGraw.Hill-Oracle.Wait.Interf/8174final/LiB0036.html

Log file Parallel write

Log file Parallel write wait events have three parameters: file, block, and request. In Oracle database 10g, this wait event belongs to the system I/O wait class. Keep the following key ideas in mind when processing log file parallel write wait events.

(1) Log file parallel write events belong to the LGWR process only.

(2) Slow LGWR can affect the foreground process submission time.

(3) Important log file parallel write wait time is most likely an I/O problem.

Common causes, diagnostics and operations

Because the db file parallel write wait event belongs only to the DBWR process, the log file parallel write wait event belongs to the LGWR process only. When it is written, the LGWR process writes the redo buffer to the online redo log by issuing a series of system write calls to the operating system. The LGWR process waits for write completion for the log file parallel write event. When the LGWR process satisfies the _log_io_size threshold, it has a 1MB redo entry in the log buffer, and when it is released by the DBWR process, the redo block is written once every three seconds when the rollback is committed.

Although user sessions never experience log file parallel write wait events, they may be affected by slow LGWR processes. A slow LGWR process can magnify the log file synchronization wait, and the user session waits during commit or rollback. The user session will not receive a commit or rollback completion acknowledgement until LGWR completes the write. The 7th Chapter has more details about the log file synchronization wait event.

The key database statistics to view are the system-level time_waited and average_wait of log file parallel write and log file synchronization wait events, because they are interrelated:

Sql> Select Event,time_waited,average_wait from v $ system_event where (' log file parallel write ', ' log file Sync ');

EVENT time_waited average_wait
------------------------- ----------- ------------
Log file parallel write 11315158.508570816
Log file Sync 7518513.497255756

If the average wait time for log file parallel writes is greater than 10ms (or 1cs), this typically indicates slow I/O throughput. Fixed the same as db file parallel write wait. If the redo log is on a bare device and the operating system supports asynchronous I/O, the asynchronous write is enabled. For redo logs on the file system, use synchronous direct write.
Unfortunately, you cannot produce multiple LGWR processes. In this case, the key is that there is nothing else to share the installation point of the Redo log file. Ensure that the controller serving the installation point is not overloaded. Moving the redo log to a higher speed disk will also help.
We strongly recommend that you avoid putting redo logs on RAID5 disks, but we also understand that many times you do not have to choose or speak. You can vent your frustration at www.baarf.com.
In addition to increasing I/O throughput, you can also reduce the number of redo entries. This will provide some relief, but not cure. Whenever possible, use the nologging option. The index should be created or rebuilt using the nologging option. This option should also be used for ctas operations.

Attention:

The nologging option does not apply to normal DML operations, such as insertions, updates, and deletions. Objects created with the nologging option are not recoverable unless the backup is performed prior to corruption. If additional backups must be made, I/O saved through non-logging will be used for the backup. The database in force logging mode will record all changes except for changes in the temporary tablespace, regardless of tablespace and object settings.

Lower submission rates at the expense of higher rollback segment usage can also provide some mitigation.

The high commit frequency causes the LGWR process to be over-active, and when coupled with slow I/O throughput, only the log file parallel write waits will be magnified.

The application may process a large amount of data in a loop and commit each change, which causes the log buffer to be excessively flushed. In this case, the application is modified to submit at a lower frequency. There may also be many short sessions logged into the database that are executed. Fast DML operations and logoff.

In this case, you may need to review your application design. You can use the following query to find out who is submitting frequently:

Sql> Select Sid,value from v $ sesstat where statistic#= (select Statistic#from v $ statname WHERE name = ' user commits ') Order BY value Desc;

-Another evidence of excessive commits is high redo wastage.

Sql> Select B.name, A.value, round (sysdate-c.startup_time) Days_old from V$sysstat A, v$statname B, v$instance c WH ere a.statistic# = b.statistic# and B.name in (' Redo wastage ', ' redo size ');

NAME VALUE Days_old
--------------- --------------- ---------------
Redo Size 249289419360 5
Redo Wastage 2332945528 5

Check the job scheduler to see if hot backups are running during peak hours. They can create a large number of redo entries, which in turn increases the log file parallel write waits. Hot backups should run during off-peak hours, and the tablespace should be removed from hot backup mode as soon as possible.

Finally, be careful not to plug LGWR at a time with too many redo entries. This can occur in large log buffers because the One-third threshold is also large and more redo entries are saved. When the One-third threshold is met, a background write is performed if the LGWR process has not been activated. and the number of redo entries may be too much, lgwr one time, causing the extended log file to wait for parallel writes. So the idea is to stream LGWR write. This can be achieved by reducing the one-third threshold value that is controlled by the initialization parameter _log_io_size.

By default, _log_io_size is the 1/3 or 1MB of log_buffer (whichever is smaller), expressed as a log block. Query x $ kccle. The log block size of the Lebsz. Typically, it is 512 bytes.

For example, if Log_buffer is 2,097,152 bytes (2MB) and the log block size is 512 bytes, the default value for _log_io_size is 1,365 log blocks used. In this size, the LGWR process becomes lazy, usually only written at the end of the transaction (synchronous write) or awakened from a 3-second timeout. You should set _log_io_size to be equivalent to 64K. This way, you can still have a larger log buffer to accommodate spikes in buffer space after the checkpoint, but when there are 64K redo entries in the buffer, the write will start, assuming no user commits or rolls back, LGWR sleep does not time out during this time.

Notes:

This method is not without overhead. LGWR write operations require redo copy and redo write latches. Therefore, a more efficient LGWR process will increase the load on these latches. If these latches currently have high sleeps, do not reduce the _log_io_size. However, if the condition allows you to change the _log_io_size, you must monitor its impact over time by querying the V $ latch view. Ensure that the baseline is obtained before the change is implemented.

You can use the following query to find the average number of redo log blocks per write and the average LGWR I/O size in bytes:

Sql> Select V (a.value/b.value) + 0.5,0) as Avg_redo_blks_per_write,round ((a.value/b.value) + 0.5,0) * C.lebsz as Avg_io _size from V $ sysstat A, V $ sysstat b,x $ kccle c where C.lenum = 1 and a.name = ' redo blocks written ' and b.name = ' Redo writ Es ';

Avg_redo_blks_per_write avg_io_size
----------------------- -----------
8 8192

Oracle db file parallel write and log file parallel write wait events