15.3 Tuning Oracle IO Usage | Oracle High Performance Tuning for 9i and 10g

< Day Day Up >

15.3 Tuning Oracle I/O Usage

Different types of applications require different approaches to tuning. OLTP databases need a quick response time for small transactions. Data warehouses or batch processing requires high throughput of large transactions. A number of factors are important with respect to tuning of I/O with Oracle Database. The two most important factors with respect to I/O are efficiency and recoverability. Do not sacrifice recoverability to gain a little extra efficiency unless downtime and partial data loss can be tolerated. For an OLTP database downtime is usually not acceptable. For most databases data loss is not acceptable.

Striping. Evenly distribute I/O by physically distributing files.
Seek Time. An adequate number of disks with adequate seek times.
Contention. High levels of contention due to high I/O request waits can be relieved using striping where files are spread across multiple disks.
Mirroring. Mirroring is a process of real-time maintenance of multiple copies of data. Mirroring can be expensive in I/O time.
Block Size and Multiple Block Reads. Block size can have a drastic effect on I/O but with Oracle9i Database and later, which allow multiple-sized block tablespaces, this issue is more manageable. The DB_FILE_MULTIBLOCK_READ_COUNT parameter can have a profound effect on access speed depending on application and database type, allowing many blocks to be read at once. Larger transactions benefit from a larger block size and reading of multiple blocks in a single read. The same can be detrimental to small transactions since full table scans are more likely and simply too much data is read. The DB_FILE_MULTIBLOCK_READ_COUNT parameter is discussed in other chapters.

The V$FILESTAT performance view can be somewhat useful in assessing I/O for a database. Notice how busy the SYSTEM, RBS (rollback), DATA, and INDX datafiles are. These activity levels and ratios are consistent with a DML intensive OLTP database, there is far more index activity (INDX) than table activity (DATA).

SELECT fs.phyrds "Reads", fs.phywrts "Writes"       ,fs.avgiotim "Average I/O Time", df.name "Datafile" FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;                          Average  Reads   Writes     I/O Time   Datafile ------   ------   ----------   --------   6724     2423   -1.890E+09   C:\ORACLE\ORADATA\TEST\SYSTEM01.DBF     29       27     76687938   C:\ORACLE\ORADATA\TEST\OEM01.DBF     29       27   -558789658   C:\ORACLE\ORADATA\TEST\CWMLITE01.DBF     29       27   -558789658   C:\ORACLE\ORADATA\TEST\DRSYS01.DBF   3105   167903   1647296225   C:\ORACLE\ORADATA\TEST\RBS01.DBF     29       27    712165534   C:\ORACLE\ORADATA\TEST\USERS01.DBF     29       27   1270955192   C:\ORACLE\ORADATA\TEST\ODM01.DBF     29       27   1347643130   C:\ORACLE\ORADATA\TEST\TOOLS01.DBF  73836    81038    335849851   C:\ORACLE\ORADATA\TEST\DATA01.DBF     34       27    239184070   C:\ORACLE\ORADATA\TEST\XDB01.DBF 541943   397992   -1.362E+09   C:\ORACLE\ORADATA\TEST\INDX01.DBF     29       27   1944776757   C:\ORACLE\ORADATA\TEST\OBJ01.DBF     29       27   -1.194E+09   C:\ORACLE\ORADATA\TEST\SMALLDATA.DBF

The next query looks at I/O activity for all datafiles in all table-spaces, including the temporary sort tablespace called TEMP.

COL ts FORMAT a10 HEADING "Tablespace"; COL reads FORMAT 999990; COL writes FORMAT 999990; COL br FORMAT 999990 HEADING "BlksRead"; COL bw FORMAT 999990 HEADING "BlksWrite"; COL rtime FORMAT 999990; COL wtime FORMAT 999990; SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"       ,fs.phyblkrd AS br, fs.phyblkwrt AS bw       ,fs.readtim "RTime", fs.writetim "WTime" FROM v$tablespace ts, v$datafile df, v$filestat fs WHERE ts.ts# = df.ts# AND df.file# = fs.file# UNION SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"       ,ts.phyblkrd AS br, ts.phyblkwrt AS bw       ,ts.readtim "RTime", ts.writetim "WTime" FROM v$tablespace ts, v$tempfile tf, v$tempstat ts WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

This query does tell much. In general, reads appear much more numerous than writes, except for rollback segments and temporary sort space. This is normal for temporary sort space. Read values in rollback segments are indicative of transactions read from rollback to maintain data consistency since what is being read has already changed. This type of activity needs to be minimized at the application level if possible and if other performance factors are not compromised. For an OLTP database block to I/O rates should be as close as possible. Also read times should be much faster than write times, probably faster than in this case. This disparity could be application related and a result of read consistency, reading data from already changed data in rollback.

Tablespace   Reads   Writes   BlksRead   BlksWrite    RTime    WTime ----------   -----   ------   --------   ---------   ------   ------ DATA          6496     2708      20402        2708    27470    55010 INDX         26560    12080      76474       12080   172513   257221 OBJECTS          5        2          5           2       22        0 OEM              5        2          5           2       38        0 RBS            502     7622        502        7622     3598   121476 SMALLDATA        5        2          5           2       15        0 SYSTEM        2324      226       5889         226     2754     5283 TEMP        108686   124975     108686      124975    22295    17654 USERS            5        2          5           2       37        0

Let's get a slightly different picture and use the same query but produce relative rates.

COL ts FORMAT a10 HEADING "Tablespace"; SELECT ts.name AS ts       ,ROUND(fs.phyrds/(fs.phywrts+1),1) "Read/Write"       ,ROUND(fs.phyblkrd/(fs.phyrds+1),1) "Blocks/Read"       ,ROUND(fs.phyblkwrt/(fs.phywrts+1),1) "Blocks/Write"       ,ROUND(fs.readtim/(fs.writetim+1),1) "Read/Write Time" FROM v$tablespace ts, v$datafile df, v$filestat fs WHERE ts.ts# = df.ts# AND df.file# = fs.file# UNION SELECT ts.name AS ts       ,ROUND(ts.phyrds/(ts.phywrts+1),1) "Read/Write"       ,ROUND(ts.phyblkrd/(ts.phyrds+1),1) "Blocks/Read"       ,ROUND(ts.phyblkwrt/(ts.phywrts+1),1) "Blocks/Write"       ,ROUND(ts.readtim/(ts.writetim+1),1) "Read/Write Time" FROM v$tablespace ts, v$tempfile tf, v$tempstat ts WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

In this query output we can see relative ratios. Note that these numbers are not necessarily meaningful because they are application dependent. In an OLTP database there should be more write activity than read activity and preferably fewer blocks per read, and definitely fewer blocks per write. A data warehouse would be exactly the opposite. More blocks per read imply full scans of one type or another. Full table scans in particular are not generally good for OLTP databases unless there is a mix of OLTP and DSS activity. Otherwise data model complexity causes full scans to occur frequently as a result of SQL code using large convoluted joins, amongst other possibilities.

Tablespace   Read/   Blocks/   Blocks/        Read/              Write      Read     Write   Write Time ----------   -----   -------   -------   ---------- DATA           2.4       3.1         1           .5 INDX           2.2       2.9         1           .7 OBJECTS        1.7        .8        .7           22 OEM            1.7        .8        .7           38 RBS             .1         1         1            0 SMALLDATA      1.7        .8        .7           15 SYSTEM        10.2       2.5         1           .5 TEMP            .9       1 1         1           .3 USERS          1.7        .8        .7           37

The result might be a little clearer with 0 rounding.

Tablespace   Read/   Blocks/   Blocks/        Read/              Write      Read     Write   Write Time ----------   -----   -------   -------   ---------- DATA             2         3         1            0 INDX             2         3         1            1 OBJECTS          2         1         1           22 OEM              2         1         1           38 RBS              0         1         1            0 SMALLDATA        2         1         1           15 SYSTEM          10         3         1            1 TEMP             1         1         1            1 USERS            2         1         1           37

15.3.1 RAID Arrays

The RAID acronym stands for Redundant Array of Inexpensive Disks; lots of small cheap disks. RAID arrays, in numerous levels of sophistication, and vast differences in cost, some exorbitant, can provide enormous increases in performance, loss recoverability and reliability. There are different types of RAID architectures. RAID types used with Oracle databases are generally RAID 0, RAID 1, RAID 0+1, and RAID 5.

RAID 0. RAID 0 is striping. Striping is a process of splitting files into little pieces and spreading those pieces over multiple disks. RAID 0 can provide fast random read and write access performance but nothing in the way of rapid recoverability and redundancy. RAID 0 is the most efficient method for OLTP databases but a little dangerous. RAID 0 could possibly be insured for recoverability and availability using clustering failovers or failover standby databases for automated, instant, up-to-the-moment replacement of service loss.
RAID 1. RAID 1 is mirroring. Mirroring is a simple process of multiple copies of files, making duplicate entries into multiple files every time a change is made to a file. RAID 1 can cause I/O bottleneck problems with respect to high-usage areas of disk storage, namely high-usage database objects such as frequently written tables. On the contrary, read access can be extremely fast since some highly sophisticated RAID arrays can read more than one mirror copy of a file at the same time, allowing for parallel reads. RAID 1 is potentially appropriate for sequential access for redo logs and index spaces in OLTP databases, and in data warehouses. RAID 1 is not appropriate for random access of table rows from indexes using ROWID pointers.
RAID 0+1. This option combines the best aspects of RAID 0 and RAID 1, providing both striping for read and write access performance plus mirroring recoverability and parallel read access. RAID 0+1 is not as fast as RAID 0 but faster than RAID 1.
RAID 5. RAID 5 provides a more simplistic version of mirroring where only parity rather than a complete duplicate is provided. RAID 5 can be particularly effective in very expensive RAID array architectures with special buffering RAM built onboard with the RAID array itself. RAID 5 is best used for sequential reading and not random write access. RAID 5 is not appropriate for OLTP databases other than to contain log files and perhaps indexes.

Once again further details on problem detection and tuning of I/O are discussed in later chapters. The next chapter will deal with tuning network usage.

< Day Day Up >