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

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 in Chapter 9, 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_cache_size The greater the number of data buffers, the smaller the chance that Oracle will need to perform disk I/O.

• Utilize multiple blocksizes The largest supported blocksizes in Oracle9i for your platform should be reserved for index tablespaces, and smaller blocksizes help the speed of bitmap index DML.

• 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 tables 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. This can be done with single table clusters or by re-sequencing the table rows with CTAS.

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. For highly-updated bitmap index columns, using smaller blocksizes with bitmaps reduces the costs for DML.

• 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.

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.

Oracle File Organization Techniques

Regardless of whether or not you use RAID, it is very important for the Oracle Remote DBA to identify all high-volume and high-activity tables and move them into isolated tablespaces. By keeping the high-volume tables in a separate tablespace, the Oracle administrator can manipulate the datafiles in the tablespace to minimize I/O contention on the disk, as shown in Figure 8-1.

Figure 8-39: Segregating Oracle tables into separate tablespaces

Without segregation, some tablespaces may have hundreds of tables and indexes, and it is impossible to tell which objects are experiencing the high I/O. The stats$filestatxs table will provide details about read and write I/O at the file level, but it is often difficult to tell which tables are causing the high I/O because a file may contain many objects.

With segregation, the Remote DBA can generate STATSPACK file I/O reports from the stats$filestatxs table that show all read and write I/O for the datafile. If the Oracle datafile maps to only one table, we can easily see the total I/O for the table. Later in this chapter we will see a STATSPACK script called rpt_io_pct.sql that serves this purpose.

Because of the high-speed transaction-oriented nature of most Oracle applications, we generally see high activity in specific areas within each tablespace. While this chapter describes the basics of I/O load balancing for Oracle datafiles and tablespaces, the settings for the individual tables will also have a profound influence on the performance of the entire database. We will look at disk I/O within Oracle and examine several areas:

• Transient disk hot spots

• Disk I/O patterns within highly active tables

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

---

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.