By examining different database applications and database functions, the fundamental I/O profile used to access the disk can be determined. What this determines is whether a particular dataset will be accessed either sequentially or randomly. The key high-level application types include transaction processing, decision support systems, and batch processing. In this section we will examine the various access patterns on the database files and later apply these patterns to how the drives eventually get configured. The distinct file types to be examined are data and index files, transaction logs, and import/export files.
OLTP is usually characterized as many users acting on a small subset of data throughout the database. The I/O profile resulting from this load is very heavy random reads and writes across the data and index files. The transaction logs, however, are hit with a heavy stream of sequential write operations of 2K or less.
DSS is characteristic of multiple users executing complicated joins and aggregations on large sets of data. Even though many of the operations could use some sequential processing, contention with other users and join and indexing operation result in a fairly random access pattern to the data and index files. Usually, if the database is dedicated to DSS no updates will be done to the database during heavy query load. In this case, no I/O will occur on the transaction logs. If on-line updates are applied to the database, it must be remembered when configuring the drive subsystem that log file response time may still be important to the overall performance of the system.
Batch processing is the most likely application to produce significant sequential I/O on the data files. The types of activities referred to here often occur after hours and usually in isolation of other activities. Batch processing involves database dumps, database loads, detail report processing, and index creation.