Oracle UNIX Administration Tuning Factors that Influence Disk I/O Oracle UNIX/Linux Tips by Burleson Consulting

Oracle Tuning Factors that Influence Disk I/O

      As you know, one of the primary goals of all Oracle tuning activities is to reduce disk I/O. We will be discussing these techniques throughout this book, but we need to mention them here so you will understand how the instance parameters can affect disk I/O. There are three areas where the settings for Oracle have a direct impact on the amount of disk I/O. The settings for the Oracle instance (init.ora) impact disk I/O, the settings for Oracle objects (tables and indexes) affect disk I/O, and the execution plans for Oracle SQL also have a direct impact on disk I/O.

1. Oracle instance? There are several database instance parameters that have a direct impact on lowering physical disk I/O:

• Large db_block_size? The block size of the database has a dramatic effect on the amount of disk I/O. As a general rule, the larger the block size, the less the disk I/O.

• Large db_block_buffers? The greater the number of data buffers, the smaller the chance that Oracle will need to perform disk I/O.

•  Multiple database writers (DBWR) processes? Multiple database writer background processes allow for more efficient writing to the datafiles.

•  Large sort_area_size? The greater the sort_area_size in RAM, the less disk sorting will take place in the TEMP tablespace.

•  Large online redo logs? The larger the online redo logs, the less frequent the log switches.

2. Oracle objects? Inside the database, settings for table and indexes can reduce physical disk I/O

• Low pctused? The smaller the value of pctused, the less I/O will occur on subsequent SQL inserts.

• Low pctfree? If pctfree is set to allow all rows to expand without fragmenting, the less disk I./O will occur on subsequent SQL selects.

• Reorganizing tables to cluster rows with indexes? If tables are placed in the same physical order as the most frequently used index, disk I/O will drop dramatically.

3. Oracle SQL? Within SQL statements, there are many techniques to reduce physical disk I/O:

• Preventing unnecessary full table scans using indexes or hints? This is the most important way to reduce disk I/O because many SQL queries can use indexes to reduce disk I/O.

• Using bitmapped indexes? The use of bitmapped indexes will reduce full table scans on tables with low-cardinality columns, thereby reducing disk I/O.

•  Applying SQL hints? Many hints make SQL run faster and with less disk I/O. For example, the USE_HASH hint will reduce disk I/O by performing joins within SGA memory, reducing calls for database blocks.

Now that we have reviewed some of the things that we can do within Oracle to reduce disk I/O, let’s take a close look at the nature of disk I/O and examine the internal workings of the disk I/O subsystem.

Oracle Internals and Disk I/O

      From an Oracle perspective, most databases can be characterized as either online transaction processing (OLTP) systems or decision support (DSS) systems. The patterns of I/O vary greatly between a data warehouse and decision support type of application and one that processes online transactions. While OLTP  may appear random, upon closer inspection, we will see clear areas of impact to the Oracle database, and understand methods to alleviate I/O contention.

The db_file_multiblock_read_count and disk I/O

Oracle has an init.ora parameter that controls the rate for which blocks are read when long contiguous data blocks are requested. The db_block_size parameters can have a dramatic impact on system performance. In addition, there is an important relationship between db_block_size and the db_file_multiblock_read_count parameter. At the physical level in UNIX, Oracle always reads in a minimum of 64K blocks.

Therefore, the values of db_file_multiblock_read_count and db_block_size should be set such that their product is 64K. For example:

8K blocks

db_block_size = 8,192

db_file_multiblock_read_count = 8

16K blocks

db_block_size = 16,384

db_file_multiblock_read_count = 4

Again, the db_file_multiblock_read_count is most beneficial for systems that perform frequent full table scans, such as data warehouses.

The Database Writer Process and Disk I/O

 We may remember that earlier in this chapter we stated that the database writer (DBWR) background processes are responsible for writing dirty data blocks into disk.

 For highly active databases, the database writer is a very important Oracle function since the DBWR processes govern the rate at which changed blocks are written to disk. Let’s begin with a brief overview of the functions of the DBWR and see how it writes data blocks to disk.

 When Oracle detects that a data block in the buffer cache has been changed, the data block is marked as “dirty”. Once marked as dirty, the block is queued for a database writer process, which writes the block back to the disk. The DBWR background processes have two responsibilities:

• Scanning the buffer cache, looking for dirty buffers to write

• Writing the dirty buffers to the disk

 It is very important to note that every operating system has implemented disk I/O very differently. Hence, the internal process of writing data blocks is specific to the operating system.

 Tuning the database writer processes is very important. Within the Oracle data buffer, read-only data blocks can age-out of the buffer but dirty blocks must be retained in the data buffer until the database writer has copied the block to disk.

      Oracle offers two init.ora parameters for implementing multiple database writers:

• dbwr_io_slaves? This is a method whereby a master database writer process spawns additional slave processes to handle the database writes. This option is also used on database servers where asynchronous I/O is not supported. Some UNIX server systems (such as Solaris and AIX) support asynchronous I/O. If your platform does not support the asynchronous I/O, you can simulate the asynchronous I/O by defining I/O slave processes.

• db_writer_processes? Starting with Oracle 8.0.5, Oracle8 supports true multiple DBWR processes, with no master/slave relationships. This parameter requires that the database server support asynchronous I/O.

 Remember, you should only implement multiple database writers when you have a clear indication of writing backlogs. Implementing db_io_slaves or db_writer_processes comes at a cost in server resources. The multiple writer processes and IO slaves are intended for large databases with high IO throughput, and you should only implement multiple database writers if your system requires the additional I/O throughput.

 In addition, there are several other init.ora parameters that affect the behavior of the DBWR processes:

• db_block_lru_latches?  This is the number of LRU latches for database blocks. You cannot set db_writer_process to a value that is greater than db_block_lru_latches.

•  log_checkpoint_interval?  This controls the number of checkpoints issued by the DBWR process. Frequent checkpoints make recovery time faster, but it may also cause excessive DBWR activity during high-volume update tasks. The minimum value for log_checkpoint_interval should be set to a value larger than the largest redo log file

•  log_checkpoint_timeout?  This should be set to zero.

Note: Multiple db_writer_process and multiple dbwr_io_slaves are mutually exclusive. If both are set, the dbwr_io_saves parameter will take precedence.

Now that we understand how the DBWR processes work, let’s see where we can go to find information about their performance.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.