Home > Workload Solutions > Oracle > White Papers > Oracle ASM on ScaleIO Best Practices > Oracle Database parameter settings
The following section lists suggested parameter settings to maximize I/O throughput with ASM on Dell EMC ScaleIO storage.
Note: These suggestions are only guidelines. Exact settings will depend on the application workload.
These settings should be set in the INIT.ORA or SPFILE.
Default setting: 0, Unit of Measure: Seconds.
The archive_lag_target parameter determines the number of seconds allowed to elapse before a redo log switch is forced.
Forcing redo log switches helps to limit the amount of data lost in the event of a failure that destroys on the online redo logs.
Dell EMC recommends setting the archive_lag_target to 900 seconds which provides a redo log switch at least every fifteen minutes.
Default setting: FALSE (10g), TYPICAL (11g, 12c)
The db_block_checksum parameter determines if the DB Writer process will calculate a checksum and store it in the cache header of data blocks when they are written to disk.
If the parameter is not set to OFF, the checksums are verified when the blocks are read.
When the parameter is set to FULL, the database also checks and re-computes the checksums during UPDATE and DELETE operations.
Table 11. db_block_checksum settings
Setting |
Performance overhead |
Process effect |
OFF |
None |
None |
TYPICAL |
2% |
Checksums generated and checked when blocks are read. |
FULL |
5% |
Checksums generated and checked when blocks are read. Checked and re-generated during UPDATE and DELETE operations. |
TRUE |
2% |
Same as TYPICAL |
FALSE |
None |
Same as OFF |
Default setting: FALSE
The db_block_checking parameter controls if the Oracle database will perform block checking for all user data blocks. Block checking is always enabled for SYSTEM blocks, and this parameter will enable it for non-SYSTEM blocks as well.
When enabled, Oracle will verify the integrity of every block of data to protect against memory and storage corruption.
Enabling this option can degrade performance by as much as 15 percent.
With the advance data corruption protection mechanisms built into ScaleIO, Dell EMC recommends setting the db_block_checking parameter to FALSE.
Default setting: 8192, Unit of measure: Bytes
The db_block_size parameter setting is covered in detail in the previous section.
Default setting: (10g and 11g: 16, 12c platform dependent) Unit of measure: Database Blocks
The db_file_multiblock_read_count (MBRC) parameter is used by Oracle for multi-block I/O operations including full table scans and index fast full scans.
During these multi-block operations, Oracle will request multiple blocks at a time from the storage subsystem.
Since Oracle 10g, the database will determine the optimum value automatically and adjust large block operations accordingly.
Default setting: CPU/8, Unit of measure: Number of processes.
The tight integration of ScaleIO with Linux and the power of modern servers, storage devices and networking mean that it is possible that the infrastructure is able to sustain higher write loads that the default db_writer_processes parameter allows.
The number of DB Writers can therefore become a bottleneck in write intensive databases. Setting the db_writer_processes to CPU/4, or even CPU/2 is acceptable in these scenarios.
Default setting: TRUE
The disk_asynch_io parameter enables asynchronous I/O to the storage subsystem. Without this option enabled, all I/O will be synchronous.
Since Oracle ASM by-passes the traditional file system, the disk_asynch_io parameter is the only option for controlling synchronous or asynchronous I/O to database files in ASM.
Async I/O offers significant performance benefits to most Oracle databases.
Dell EMC recommends setting disk_asynch_io to TRUE where it is supported by the Operating System. Check Oracle documentation to ensure async I/O is supported on your implementation.
Default setting: 0, measured in seconds.
The fast_start_mttr_target parameter allows the DBA to specify the number of seconds the database takes to perform crash recovery of a single instance.
In effect what this does is to control how aggressively the database DB writers flush dirty blocks from the DB Block Cache to the ASM disks.
When left at the default setting of zero, the DB Writers may not flush dirty blocks from the cache for an extended period of time. Database ACID compliance is guaranteed by the redo logs.
Default setting: NONE
The filesystemio_options parameter is used to govern how Oracle interacts with file-based storage.
By manipulating this parameter, the DBA may affect the I/O to the file system as shown in Table 12.
Table 12. Input/output settings
|
Buffered I/O |
Direct I/O |
Synchronous I/O |
NONE |
DIRECTIO |
Asynchronous I/O |
ASYNC |
SETALL |
The parameter still honors the disk_asynch_io parameter. If this parameter is set to FALSE, then setting filesystemio_options to async will have no effect.
Since ASM bypasses the file system layer and provides Oracle direct access to the ASM files, the filesystemio_options parameter has no effect when all files are placed on ASM.
Dell EMC recommends setting filesystemio_options to SETALL since some database files, notably RMAN backup sets, may still be generated on file systems not created from ASM volumes.
Table 13 lists the parameters and recommended settings.
Table 13. Parameters and recommended settings
Parameter |
Recommended setting |
archive_lag_target |
900 |
db_block_checksum |
FALSE |
db_block_checking |
FALSE |
db_block_size |
8192 for OLTP, 16384 for OLAP |
db_file_multiblock_read_count |
Do not change |
db_writer_processes |
CPU/4 |
disk_asynch_io |
TRUE |
fast_start_mttr_target |
120 |
filesystemio_options |
SETALL |