|Oracle8i Data Warehousing Guide
Release 2 (8.1.6)
Part Number A76994-01
This chapter explains some of the hardware and input/output issues in a data warehousing environment, and includes the following topics:
To avoid I/O bottlenecks during parallel processing or concurrent query access, all tablespaces accessed by parallel operations should be striped. As shown in Figure 4-1, tablespaces should always stripe over at least as many devices as CPUs; in this example, there are four CPUs.
Stripe tablespaces for tables, indexes, rollback segments, and temporary tablespaces. You must also spread the devices over controllers, I/O channels, and/or internal buses.
It is also important to ensure that data is evenly distributed across these files. One way to stripe data during loads, use the FILE= clause of parallel loader to load data from multiple load sessions into different files in the tablespace. To make striping effective, ensure that enough controllers and other I/O components are available to support the bandwidth of parallel data movement into and out of the striped tablespaces.
Your operating system or volume manager may perform striping (operating system striping), or you can perform striping manually through careful data file allocation to tablespaces.
We recommend using a large stripe size of at least 64KB with OS striping when possible. This approach always performs better than manual striping, especially in multi-user environments.
Operating system striping is usually flexible and easy to manage. It supports multiple users running sequentially as well as single users running in parallel. Two main advantages make OS striping preferable to manual striping, unless the system is very small or availability is the main concern:
Stripe size must be at least as large as the I/O size. If stripe size is larger than I/O size by a factor of 2 or 4, then certain trade-offs may arise. The large stripe size can be beneficial because it allows the system to perform more sequential operations on each disk; it decreases the number of seeks on disk. The disadvantage is that it reduces the I/O parallelism so fewer disks are simultaneously active. If you encounter problems, increase the I/O size of scan operations (going, for example, from 64KB to 128KB), instead of changing the stripe size. The maximum I/O size is platform-specific (in a range, for example, of 64KB to 1MB).
With OS striping, from a performance standpoint, the best layout is to stripe data, indexes, and temporary tablespaces across all the disks of your platform. For availability reasons, it may be more practical to strip over fewer disks to prevent a single disk value from affecting the entire data warehouse. However, for performance, it is crucial to strip all objects over multiple disks. In this way, maximum I/O performance (both in terms of throughput and number of I/Os per second) can be reached when one object is accessed by a parallel operation. If multiple objects are accessed at the same time (as in a multi-user configuration), striping automatically limits the contention.
You can use manual striping on all platforms. To do this, add multiple files to each tablespace, each on a separate disk. If you use manual striping correctly, your system will experience significant performance gains. However, you should be aware of several drawbacks that may adversely affect performance if you do not stripe correctly.
First, when using manual striping, the degree of parallelism (DOP) is more a function of the number of disks than of the number of CPUs. This is because it is necessary to have one server process per datafile to drive all the disks and limit the risk of experiencing I/O bottlenecks. Also, manual striping is very sensitive to datafile size skew which can affect the scalability of parallel scan operations. Second, manual striping requires more planning and set up effort that operating system striping.
Oracle8i Concepts for information on disk striping and partitioning. For MPP systems, see your platform-specific Oracle documentation regarding the advisability of disabling disk affinity when using operating system striping.
Local striping, which applies only to partitioned tables and indexes, is a form of non-overlapping disk-to-partition striping. Each partition has its own set of disks and files, as illustrated in Figure 4-2. There is no overlapping disk access, and no overlapping of files.
An advantage of local striping is that if one disk fails, it does not affect other partitions. Moreover, you still have some striping even if you have data in only one partition.
A disadvantage of local striping is that you need many more disks to implement it--each partition requires multiple disks of its own. Another major disadvantage is that after partition pruning to only a single or a few partitions, the system will have limited I/O bandwidth. As a result, local striping is not optimal for parallel operations. For this reason, consider local striping only if your main concern is availability, and not parallel execution.
Global striping, illustrated in Figure 4-3, entails overlapping disks and partitions.
Global striping is advantageous if you have partition pruning and need to access data only in one partition. Spreading the data in that partition across many disks improves performance for parallel execution operations. A disadvantage of global striping is that if one disk fails, all partitions are affected.
There are two considerations when analyzing striping issues for your applications. First, consider the cardinality of the relationships among the objects in a storage system. Second, consider what you can optimize in your striping effort: full table scans, general tablespace availability, partition scans, or some combinations of these goals. These two topics are discussed under the following headings.
To analyze striping, consider the following relationships:
Figure 4-4 shows the cardinality of the relationships among objects in a typical Oracle storage system. For every table there may be:
Goals. You may wish to stripe an object across devices to achieve one of three goals:
To attain both Goal 1 and Goal 2, having the table reside on many devices, with the highest possible availability, you can maximize the number of partitions p and minimize the number of partitions per tablespace s.
For highest availability but the least intra-partition parallelism, place each partition in its own tablespace. Do not used striped files, and use one file per tablespace. To minimize Goal 2 and thereby minimize availability, set f and n equal to 1.
When you minimize availability you maximize intra-partition parallelism. Goal 3 conflicts with Goal 2 because you cannot simultaneously maximize the formula for Goal 3 and minimize the formula for Goal 2. You must compromise to achieve some benefits of both goals.
Goal 1: To optimize full table scans. Having a table on many devices is beneficial because full table scans are scalable.
Calculate the number of partitions multiplied by the number of files in the tablespace multiplied by the number of devices per file. Divide this product by the number of partitions that share the same tablespace, multiplied by the number of files that share the same device. The formula is as follows:
You can do this by having t partitions, with every partition in its own tablespace, if every tablespace has one file, and these files are not striped.
If the table is not partitioned, but is in one tablespace in one file, stripe it over n devices.
There are a maximum of t partitions, every partition in its own tablespace, f files in each tablespace, each tablespace on a striped device:
Goal 2: To optimize availability. Restricting each tablespace to a small number of devices and having as many partitions as possible helps you achieve high availability.
Availability is maximized when f = n = m = 1 and p is much greater than 1.
Goal 3: To optimize partition scans. Achieving intra-partition parallelism is beneficial because partition scans are scalable. To do this, place each partition on many devices.
Partitions can reside in a tablespace that can have many files. There could be either
Striping affects media recovery. Loss of a disk usually means loss of access to all objects stored on that disk. If all objects are striped over all disks, then loss of any disk stops the entire database. Furthermore, you may need to restore all database files from backups, even if each file has only a small fraction of its total data stored on the failed disk.
Often, the same OS subsystem that provides striping also provides mirroring. With the declining price of disks, mirroring can provide an effective supplement to backups and log archival, but not a substitute for them. Mirroring can help your system recover from device failures more quickly than with a backup, but is not as robust. Mirroring does not protect against software faults and other problems that an independent backup would protect your system against.
You can effectively use mirroring if you are able to reload read-only data from the original source tapes. If you have a disk failure, restoring data from backups could involve lengthy downtime, whereas restoring it from a mirrored disk would enable your system to get back online quickly.
RAID-5 technology is even less expensive than mirroring. RAID-5 avoids full duplication in favor of more expensive write operations. For "read-mostly" applications, this may suffice.
For more information about automatic file striping and tools you can use to determine I/O distribution among your devices, refer to your operating system, server, and storage documentation.
Be careful when configuring the I/O subsystem that the throughput capacity of the disks that you will be accessing at any given time does not exceed the throughput capacity of the I/O controllers. You must balance the number of disks with the total number of I/O controllers to prevent bottlenecks at the controller level.
It is important for data warehouses that the staging file systems perform well. Staging file systems are used to store flat files while these files are loaded into the data warehouse. Most operating systems that support OS striping support creating file systems on striped devices.
If you intend to have more than one process reading from or writing to a file system simultaneously, you should create the file system on a device that is striped over the same number of devices as the number of processes that you intend to use. For example, if you will be using five Parallel SQL*Loader processes to load five flat files into the database, the file system where the flat files reside should be striped over five devices. Alternatively, place each flat file in a separate unstriped file system.