[Laskey99] Section 11.3. Disk Allocation

11.3.
In a typical installation, Oracletends to be I/O bound. Database transactions areslowed by waiting for one or more input/output operations, ratherthan by waiting for the CPU to complete an operation. Because thedatabase is so dependent on I/O, overall Oracle performance can beimproved, sometimes dramatically, by improving the overallperformance of the I/O system on the host server. While there areseveral ways I/O performance may be improved (for example, by usingfaster disk drives or more controllers), one relatively easy way toimprove I/O is by carefully allocating physical disk resources toOracle.
Each time an Oracle table is updated, the following I/O operationsare performed:
The write to the table
Reads and writes to any indexes
Reads and writes to the temporary tablespace if large sorts or joins are involved
Writes to the rollback segment for the table
Writes to the rollback segment for any indexes
Writes of the redo log buffer to disk for the table
Writes of the redo log buffer to disk for any indexes
Writes to the data dictionary if new extents are needed
In addition, if the database is running in archivelog mode, then atany given time the ARCH process may read from one of the online redolog files and write a copy to the archive log destination.
11.3.1. Disk Layout
The more independent physical disks that can be dedicated to Oraclefiles, the better the I/O operation of the system is likely to be.Carefully planning the location of each Oracle file will yieldsignificant improvements in the performance of the database. Use thefollowing guidelines when laying out the disk subsystem:
Allocate separate disks for data, redo logs, and archive files.
Keep data and index segments for a given table in separate tablespaces, on separate disks.
Use a separate disk for rollback segments.
Keep the system tablespace on a separate disk or on a lightly used disk.
Try to keep Oracle files on different disks from user filesystems.
An ideal disk layout might look like the layout shown inTable 11.2.
Table 11.2. Ideal Disk Layout
Disk Contents
01 Controller A Oracle software ($ORACLE_HOME ), control file 1
02 Controller A SYSTEM tablespace
03 Controller A DATA tablespace
04 Controller A Redo log group 1, member 1 Redo log group 3, member 1
05 Controller A Redo log group 2, member 1 Redo log group 4, member 1
06 Controller A Redo log group 1, member 2 Redo log group 3, member 2
07 Controller A Redo log group 2, member 2 Redo log group 4, member 2
08 Controller B TOOLS tablespace
09 Controller B TEMP tablespace, control file 2
10 Controller B ROLLBACK tablespace
11 Controller B INDEX tablespace
12 Controller B Archivelog destination
13 Controller B Application software
14 Controller B User files, exports, etc.
Since this system has two controllers, we have made the followingdecisions:
Locate the INDEX tablespace on a different controller from the DATA tablespace.
Rationale: During inserts and updates, there will be heavy activity as rows are inserted and indexes are updated. Distributing I/O across two controllers provides more I/O bandwidth.
Locate the ROLLBACK tablespace on a different controller from the DATA tablespace.
Rationale: During heavy inserts and updates, there will be a significant number of rollback records written, so placing the ROLLBACK tablespace on a separate controller improves I/O bandwidth.
Allocate two members for each log group.
Rationale: This protects against the loss of a log file by providing Oracle log file mirroring.
Alternate the locations of each log group across two different disks.
Rationale: While one log file is being archived, the next file can be written to without I/O contention.
Locate the archive log destination on a different controller from the online log files.
Rationale: When running in archivelog mode, the ARCH process can read the online log file from one controller while writing to the archive log destination with the other controller.
11.3.2. About RAID
Recently, there has been a high degreeof interest in the use of RAID (redundant arrays of inexpensivedisks) technology over a wide range of server configurations. Avariety of RAID implementations, or levels, is now available, assummarized inTable 11.3.
Table 11.3. RAID Implementations
RAID Level Description Advantages Disadvantages
RAID-0 Block striping, no parity Faster reads No recovery from disk failure
RAID-1 One-to-one disk mirroring Fully protected from single disk failure, no degradation of I/O speed Twice as many disks are required
RAID-0+1 One-to-one mirroring with striping Faster reads, fully protected from single disk failure Twice as many disks are required
RAID-3 Byte-level striping with dedicated parity disk Faster reads, fully protected from single disk failure, only one extra disk required per array Slower writes, slower recovery from failure
RAID-4 Block-level striping with dedicated parity disk Faster reads, only one extra disk required per array Slower writes, slower recovery from disk failure
RAID-5 Block-level striping with distributed parity Faster reads, fully protected from single disk failure, only one disk required per array, faster recovery from disk failure Slower writes
RAID-6 Block-level striping with dual distributed parity Faster reads, fully protected from failure of any two disks Slower writes, requires two extra disks for each array, slower recovery from disk failure
RAID-7 Performance-enhanced RAID-5 Faster reads, fully protected from single disk failure, only one disk required per array, faster recovery from disk failure Slower writes
RAID-8 Performance-enhanced RAID-5 Faster reads, fully protected from single disk failure, only one disk required per array, faster recovery from disk failure Slower writes
A careful examination of this table suggests that there is no clearchoice of RAID technologies, nor is it even clear that RAID should beused. In general, RAID-5 is the most popular RAID level, since it issupported by a wide range of hardware manufacturers and is relativelyinexpensive to implement. However, RAID-5 has a performance penaltyassociated with it. In most RAID-5 implementations, there is asignificant performance degradation when performing write operations,due primarily to the need to read, recalculate, and write thedistributed parity information. While RAID-5 may be appropriate forfairly static tablespaces used primarily for read operations, avoidthe use of RAID-5 disks for files with high write rates. Theseinclude the rollback segments, TEMPORARY tablespace, SYSTEMtablespace, redo logs, and tables with high amounts of inserts,updates, and deletes.
With the falling price of disk drives, RAID-0+1 may be the bestall-around choice for both performance and protection from failure.Although twice as many disk devices are required as for traditionalnon-RAID disk implementation, the increases possible in readperformance, combined with the ability to recover from a diskfailure, makes this level attractive.
Some operating systems, for example, Windows NT, provide RAIDcapability as an operating system feature. While these RAIDimplementations may be attractive, they come at a very high resourcecost. Since these RAID configurations are implemented in software,rather than by using specialized RAID hardware, they consume a largeamount of CPU, memory, and controller capacity.
One word of caution when using RAID: do not create, or allow creationof, multiple logical disks on a single RAID array. While this may bepractical for a file server configuration, use of such aconfiguration in an Oracle database environment will result inoverall poor performance.

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint
Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next
Related Content

Disk Arrays: Not a Choice Anymore
From: Oracle Database 10g Performance Tuning: Tips & Techniques
Redundant Array of Independent Disks (RAID)
From: Microsoft® SQL Server™ 2005 Administrator’s Companion
Oracle and Disk I/O Resources
From: Oracle Essentials, 4th Edition
Overview of Common RAID Levels
From: Microsoft® SQL Server™ 2000 Administrator’s Companion
RAID levels overview and considerations
From: Database Performance Tuning on AIX
Selecting the appropriate RAID level
From: SQL Server 2008: Administration in Action
Summary
From: DB2® for Solaris™: The Official Guide
What High Availability Options Are There?
From: Microsoft® SQL Server High Availability
RAID Technology
From: Microsoft® SQL Server 2005 Unleashed
Answers
From: MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, Exam 70-228, Second Edition