This workaround is updated regularly. Additional monitoring and tuning notes are included from time to time.
DISK_ASYNCH_IO = FALSE
In this case it should be noted, that deactivating the async I/O can have a negative impact on database performance. To obtain better performance, you have the following options.
1. If you are using Solaris 2.6 or higher and your database is operating on a UFS file system, then you should activate the mount option "forcedirectio" for each file system on which a database file is located (redologs, data files, control files, archive files, and so on). This change must be made in the /etc/vfstab file and is activated as soon as the file system in question is mounted the next time. (either by a manual "umount" and "mount" or after the next system reboot).
If the I/O through put still does not suffice afterwards, then you can improve the I/O behavior on the DB side either by raising the number of "db_writer_processes" or "db_writer_slave":
2. Increasing the db_writer_processes
a) If there are not enough LRU latches configured to supply the db_writer_processes, then raising the number makes no sense and in the end only poor parallel processing of the I/O operations or none at all are the result.
b) You must monitor this setting to find the optimum setting for each individual customer because the definitive number of db_block_lru_latches and db_writer_processes depend on the hardware configuration and the use of the system by the application.
c) Starting point for setting the parameters (you must monitor!)
db_block_lru_latches => CPU_COUNT for the database * 2
The value should only be set high if the 'misses' are higher than 3% in V$LATCH (for the 'latch wait list' divide the number in the misses column by the number in the gets column)
ATTENTION: If no processor sets are defined on the SUN system or no domaining has been set, then this question might not be able to be answered or may only be answered after a lot of time and effort! These may need to be set up or adjusted.
db_block_checkpoint_batch =>
This parameter is used to prevent the I/O system from flooding with checkpoints and also to close the checkpoints quickly enough. You should check if the writing of checkpoints takes too long. This parameter should be set to a value which allows the check point to be closed before the next log switch. Following the explanation given, it should be set either too high or too low. Setting a higher value allows the check points to be closed more quickly. If you set a value which is simply too high, Oracle reduces the value.
db_writer_processes =>
You should set this parameter as follows: {(lower than or equal to the number of physical I/O access paths to the sapdata) and (lower than or equal to the number of LRU latch)}. This parameter specifies how many database writer processes the system should work with. It does not help to keep raising the parameter if there is not the equivalent number of LRU latches. As soon as the db_writer_slaves are set, only one database writer process is used regardless of what value you have set the db_writer_processes to. If one of these processes encounters a serious error, the database crashes.
3. Increasing the db_writer_slaves
a) Only one database writer process is used regardless of how high you have set the db_writer_processes. This parameter reads from the LRU latches and supplies the I/= slaves with requests.
b) If a slave does not report back in 80 seconds, the database writer process crashes (and the entire database along with it). This situation usually occurs with the following activities: Reorganizations of data files, upgrade, creation of a lot of data files, data migration. From a technical point of view, the following question must be answered: are the I/O operations fast enough to prevent this situation. Because you know about the situation, a conversion and confirmation can take place if this is required as long as activities of this type are carried out. If no one has the time to make the required setting (1)=(increasing db_process_writers), it is faster and better to implement the other variant (2)=(increasing the slaves).
c) Starting point for setting the parameter (you must monitor this)!
DBWR_IO_SLAVES =
<the number of physical I/O accesses to the sapdata disk>
LGWR_IO_SLAVES =
<the number of physical I/O accesses to the sapdata disk>
If this has not already happened, the log writer and the DB writer process should be placed in a separate processor set. You should monitor this setting and the setting depends on the total number of existing number of processors and the other applications which are running on the machine.
Frequently asked questions (FAQ) about I/O slaves:
======================================
1. What is the reason for I/O slave?
I/O slaves are special processes which accelerate I/O performance.With Oracle8, the file-I/O-Interface was revised to use "non- blocking I/O calls" for the interfaceI/O slaves provide the solution for "non-blocking I/O request" if a platform does not support I/O asynchronously and if the database writer requires additional processes for the I/O when the data load is very heavy. In Oracle 7, several of these DBWR processes were used. With Oracle 8, slaves are used by the DBWR I/O in their place. I/O slaves can be specified for DBWR, LGWR, ARCHS and backup processes.
2. Why was this change made between Oracle7 and Oracle8?
Several db_writer for DBWR are implemented on the OSD level in Oracle7.This special feature should also be possible for other processes. The design of Oracle8 with I/O slaves in part resulted from this request. These could theoretically be used on each server process. I/O slaves are presently implemented for DBWR, LGWR, ARCH and backup processes.
3. When should you use I/0 slaves?
First of all, only if there is an I/O bottle neck and then I/O slaves are usually used if a platform does not support I/O asynchronously or the execution of the asynchronous I/O is not satisfactory.
4. Which initial parameters control the I/O slave?
The significant parameters for Oracle8:
parameter value default
-----------------------------------------------
dbwr_io_slaves 0...>100 0
lgwr_io_slaves 0...>100 0
arch_io_slaves 0...>100 0
backup_disk_io_slaves 0...>100 0
backup_tape_io_slaves true/false false
_io_slaves_disabled true/false false
disk_asynch_io true/false true
Tape_asynch_io true/false true
5. What is the reason for disk_asynch_io and tape_asynch_io?
These parameters are generic parameters and replace all platform-specific parameters, such as use_async_io, async_read, async_write.Now and again you need to deactivate the asynchronous I/O set up by the operating system. Implementing the asynchronous I/Os does not yield good performance on many operating systems or may be incorrect.
6. Are slaves dynamically started, or started during the "Instance startup"?
Presently, I/O slaves of the DBWR are started with the first I/O request directly after the database is opened.If, for example, dbwr_io_slaves = 4 is set, then four I/O slaves are spawned for the DBWR.The additional I/O slaves of LGWR, ARCH, backup are dynamically started according to use.In the future this may change so that all processes are started during "Instance startup".
7. How are the tasks distributed between DBWR and its slaves?
As a "parent process", the DBWR is responsible for all activities. The I/O activity proceed from the DBWR, that is it spawned those I/O slaves. The DBWR slaves do the I/O at the request of the DBWRs.
8. What is the naming convention of I/O slaves?
The process name of the I/O slaves is as follows: ora_iNnn<SID> where i is the indicator that it is an I/O slave
N: the I/O adapter number of 1 ... F
nn: the slave no. 01 ... 0z and 10 ...zz ...
9. Do I need I/O slaves if asynchronous I/O is activated?
Normally NO
In systems that are very large and overburdened, it may be useful to use I/O slaves. In this case the I/O behavior should be monitored.Fine tuning is absolutely required.
10. What needs to be taken into consideration if I use I/O slaves in conjunction with an asynchronous I/O?
- processing overhead (for example, more context switches)
11. What are the recommended values for <adapter>_io_slaves?
In general:In the case of low I/O activity you should set the value to 2.During heavier I/O activity you can raise it to 4.These are only rough guide.The simulation of the asynchronous I/O of course has costs (shared memory, context).Owing to values that are too high a system can reach its borders quite easily.Therefore you should monitor the system after any change so that the value conforms with the current system parameters.
12. How can I monitor the performance of I/O slaves?
Look for physical writes, physical reads, logical reads, write requests to v$sesstat. This value must correlate with v$sess_io and v$filestat.