Here, lets finish this. Here's the meta link article.
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. 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_block_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. The parameter db_block_lru_latches has been made hidden in oracle 9i.
Things to know and watch out for.... ==================================== --
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 db_block_lru_latches. If it does, then the number of DBWRs will be minimized to equal the number of db_block_lru_latches and the following message is produced in the alert.log during startup: ("Cannot start more dbwrs than db_block_lru_latches.\n"); However, the number of lru latches can exceed the number of DBWRs. --
3. dbwr_io_slaves are not restricted to the db_block_lru_latches; i.e., dbwr_io_slaves >= db_block_lru_latches.
Should you use DB_WRITER_PROCESSES or DBWR_IO_SLAVES? =====================================================
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;
4) the availability of asynchronous I/O (from the OS). There is NOT a definite answer to this question but here are some considerations to have when making your choice. Please note that it is recommended to try BOTH (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 consider a single DBWR writer process; If async I/O is not available then use dbwr_io_slaves. -- If the system is a uniprocessor(1 CPU) then implement may want to use dbwr_io_slaves. Implementing db_io_slaves or db_writer_processes comes with some overhead cost. 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. In some cases, it may be acceptable to disable I/O slaves and run with a single DBWR process.
Other Ways to Tune DBWR Processes =================================
It can be easily seen that reducing buffer operations will be a direct benefit to DBWR and also help overall database performance.
Buffer operations can be reduced by:
1) using dedicated temporary tablespaces
2) direct sort reads
3) direct Sqlloads
4) performing direct exports.
In addition, keeping a high buffer cache hit ratio will be extremely beneficial not only to the response time of applications, but the DBWR as well.
Oracle it's not just a database it's a lifestyle!
-------------- BTW....You need to get a girlfriend who's last name isn't .jpg