15.3 Tuning Oracle IO Usage
|
| < 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 > |
|
EAN: 2147483647
Pages: 164