Subject: DBWR in 8i Type: FAQ Creation Date: 14-APR-2000 1. HOW HAS DBWR CHANGED IN 8I? Answer ------ 1. The Operational DBWR and DBWR changes. In Oracle7, the algorithm used by DBWR caused it to incur frequent waits when one buffer in a batch set caused the entire batch to be delayed. This wait was usually caused by a slow disk (I/O) operation. Additionally, this wait also caused delays in scanning and accumulating buffers for the next batch. This is rectified by the Oracle8.1 DBWR. Now DBWR will continuously write, without waiting for the previously issued calls; and thus, will not limited by the slowest disk. This implementation allows DBWR to be inherently asynchronous in nature, regardless of the OS efforts. Moreover, improved throughput is achieved by overlapping buffer scans and buffer writes. Oracle7 DBWR could only perform asynchronous I/O if the platform supported the function calls. If the platform did not support this feature, then the alternative was to use multiple database writers (db_writers). Multiple db_writers was used to simulate async I/O by way of master-slave processing. Oracle8.1 DBWR has evolved from batch oriented message driven writer into a queue management driven writer process. Figure 1 illustrates this change by showing the various messages for DBWR in Oracle7 and in Oracle81. Oracle81 SQL> select description from x$messages where dest like 'DBW%'; DESCRIPTION ---------------------------------------------------------------- DBWR interrupt action DBWR write buffers Oracle7 SVRMGR> select description from x$messages where indx in ('9','10','11'); and description like 'write %'; DESCRIPTION ------------------------------------------------------------ write dirty buffers when idle - timeout action write dirty buffers/find clean buffers write checkpoint-needed buffers/recovery end The main objective of the asynchronous DBWR is to avoid IO peaks. This is done by allowing DBWR to perform aggressive writing and queue management, which will allow a consistent write pace. The foundations for the new DBWR were laid down in Oracle8.0, which introduced two flavors of multiple DBWRs; DBWR IO slaves (dbwr_io_slaves) and DBWR processes (db_writer_processes). These two new approaches have been implemented to allow greater I/O throughput for DBWR write processing . Note, these two implementations are mutually exclusive. The main advantage to implementing Oracle8 DBWR I/O slaves and DBWR processes, is the programmatic simplicity that is afforded. The new DBWR IO code has now been kernalized, and thus is more generic. In the past the slave IO code was in the OSD layer, thus making it very port specific. a. DBWR IO slaves In Oracle7, the multiple DBWR processes were simple slave processes; i.e., unable to perform async I/O calls. In Oracle80, the slave database writer code has now been kernalized, and true asynchronous I/O is provided to the slave processes, if available. This feature is implemented via the init.ora parameter dbwr_io_slaves. With dbwr_io_slaves, there is still a master DBWR process and its slave processes. . This feature is very similar to the db_writers in Oracle7, except the IO slaves are now capable of asynchronous I/O on systems that provide native async I/O, thus allowing for much better throughput as slaves are not blocked after the I/O call. Slave processes are started at the database open stage (not instance creation), and thus will probably be assigned process id 9 through x, where x is the number of slave processes. The names of the DBWR slave processes are different than the slaves of Oracle7. For example a typical DBWR slave background process maybe : ora_i103_testdb. Where i indicates that this process is a slave IO process. 1 indicates the IO adapter number 3 specifies the slave number Therefore if dbwr_io_slaves was set to 3 then the following slave processes will be created: ora_i101.testdb, ora_i102_testdb and ora_i103_testdb. p97050 15304 1 0 08:37:00 ? 0:00 ora_i102_pmig1 p97050 15298 1 0 08:36:56 ? 0:00 ora_smon_pmig1 p97050 15296 1 0 08:36:56 ? 0:00 ora_ckpt_pmig1 p97050 15302 1 0 08:37:00 ? 0:00 ora_i101_pmig1 p97050 15292 1 0 08:36:55 ? 0:00 ora_dbw0_pmig1 p97050 15290 1 0 08:36:55 ? 0:00 ora_pmon_pmig1 p97050 15294 1 0 08:36:56 ? 0:00 ora_lgwr_pmig1 p97050 15306 1 0 08:37:01 ? 0:00 ora_i103_pmig1 This examples illustrates how DBWR processes hash to working set. Note, the buffer ranges that each manages. ? db_block_lru_latches = 2 ? dbwr_io_slaves = 6 Note, the master dbwr is writer number 0. SQL> select set_id, dbwr_num, start_buf#, end_buf# from x$kcbwds; SET_ID DBWR_NUM START_BUF# END_BUF# ---------- ------------------ --------- --------------- 1 0 0 99 2 0 100 199 Note, from the above listing, that master DBWR is the only one that manages the working sets. b. Multiple DBWRs. Multiple database writers is implemented via the init.ora parameter db_writer_processes. This feature was enabled in Oracle8.0.4, and allows true database writers; i.e., no master-slave relationship. If db_writer_processes is enabled, then the writer processes will be started after PMON has initialized. The writer processes can be identified (OS level) by viewing the ps command output . In this example db_writer_processes was set to 3. The sample ps output shows the following. Note, the DBWR processes are named starting from 0 and there is no master DBWR process; all are equally weighted. p97050 1472 1 0 10:48:18 ? 0:00 ora_dbw2_pmig1 p97050 1474 1 0 10:48:18 ? 0:00 ora_dbw3_pmig1 p97050 1466 1 0 10:48:17 ? 0:00 ora_pmon_pmig1 p97050 1478 1 0 10:48:18 ? 0:00 ora_ckpt_pmig1 p97050 1470 1 0 10:48:18 ? 0:00 ora_dbw1_pmig1 p97050 1480 1 0 10:48:18 ? 0:00 ora_smon_pmig1 p97050 1468 1 0 10:48:18 ? 0:00 ora_dbw0_pmig1 p97050 1476 1 0 10:48:18 ? 0:00 ora_lgwr_pmig1 With Oracle8 db_writer_processes , each writer process is assigned to a LRU latch set. Thus, it is recommended to set db_writer_processes equal to the number of LRU latches (db_lru_latches) and not exceed the number of CPUs on the system. For example, if db_writer_processes was set to four and db_lru_latches=4, then each writer process will manage its corresponding set; i.e., each writer will write buffers from its appropriate LRUW list and asynchronously, if available,. Allowing each writer to manage at least one LRU latch provides a very autonomous and segregated approach to Cache management. This examples illustrates how DBWR processes hash to working set. ? db_block_lru_latches = 4 ? dbwr_processes = 2 Note, the buffer ranges that each DBWR manages. SQL> select set_id, dbwr_num, start_buf#, end_buf# from x$kcbwds; SET_ID DBWR_NUM START_BUF# END_BUF# ---------- ---------------- ------------- ------------- 1 0 0 49 2 1 50 99 3 0 100 149 4 1 150 199 Although both implementations of DBWR processes may be beneficial, the general rule, on which option to use, depends on the following : (1) the amount write activity; (2) the number of CPUs (the number of CPUs is also indirectly related to the number LRU latch sets); (3) the size of the buffer cache; and (4) the availability of asynchronous I/O (from the OS). The following is not necessarily a top down checklist approach to determine which option use, but rather an outline of the considerations. Note, it is best to try both options (not simultaneously) against your system to determine which best fits the environment. ? If the buffer cache is very large (100,000 buffers and up) and the application is write intensive, then db_writer_processes may be beneficial. Note, the number of writer processes should not exceed the number of CPUs. ? If the application is not very write intensive (or even a DSS system) and async I/O is available, then use dbwr_io_slaves or even a single writer processes; If async I/O is not available then use dbwr_io_slaves. ? If the system is a uniprocessor then implement dbwr_io_slaves. Note, a uniprocessor system will have db_lru_latches set to 1. Implementing db_io_slaves or db_writer_processes comes with some overhead cost. Enabling these features, requires that extra shared memory be allocated for IO buffers and request queues and extra CPU cycles . Multiple writer processes (and IO slaves) are advanced features, meant for high IO throughput. Implement this feature only if the database environment requires such IO throughput. As stated earlier, in some cases (if async I/O is available), it may be prudent to disable I/O slaves and run with a single DBWR in async I/O mode. Review the current throughput and examine possible bottlenecks to determine if it is feasible to implement these features. Caveats and Concerns 1. Multiple DBWRs and DBWR IO slaves cannot coexist. If both are enabled, then the following error message is produced: ksdwra("Cannot start multiple dbwrs when using I/O slaves.\n"); Moreover, if both parameters are enabled, dbwr_io_slaves will take precedence. 2. The number of DBWRs cannot exceed the number of working sets. If it does, then the number of DBWRs will curtailed to equal the number of working sets and the following message is produced in the alert.log during initialization time : ksdwra("Cannot start more dbwrs than db_block_lru_latches.\n"); However, the number of sets can exceed the number of DBWRs. 3. The maximum number of pending asynchronous i/o's is dictated by SGA variable SSTMXIOB . 4. dbwr_io_slaves are not restricted to the number of sets; i.e., dbwr_io_slaves >= db_block_lru_latches. However, when enabling DBWR IO slaves, the master DBWR will manage the working sets. To enable the new DBWR process to efficiently the following structures are initialized in each writer processs PGA at startup (not an exhaustive list) 1. The number of dbwrs 2. max async writes 3. quota for high, medium, low priority writes 4. postme flag for LGWR 5. number of buffers in buffer cache 6. max IO size (max write size) Although the Oracle8.1 DBWR is significantly changed, there are very few end user visible parameters that affect DBWR directly. The following (hidden) parameters influence DBWR behaviors : The following parameters affect (and override the defaults) how quotas are applied: _db_block_high_priority_quota _db_block_medium_priority_quota _db_block_low_priority_quota The following parameters affect (and override the defaults) priority settings for write-types _db_block_ping_pri _db_block_incr_pri _db_block_ircv_pri _db_block_aging_pri _db_block_mrcv_pri _db_block_user_ckpt_pri _db_block_tbsp_ckpt_pri _db_block_reuse_pri References --------- NONE