1. Introduction In the last year or two, enterprise class flash storage devices have entered the marketplace.

One of the high end examples of such devices is Oracle's Sun F5100 Flash Array (hereafter just F5100.)

The F5100 provides impressive raw I/O performance when compared to conventional disks.
For example Table 2.2 shows that small I/Os (8K) on an F5100 are about an order of magnitude faster than small I/Os on 15K SAS disks and that large I/O's (1MB) are about 3 times faster on an F5100 than on SAS disks.
Intuitively, these types of I/O advantages, should translate into serious performance gains for Oracle database applications when conventional disks are replaced by F5100 devices. In this paper it is shown how, and under what circumstances, this will occur.

The focus of the paper is on three Oracle application areas
• on-line transaction processing (OLTP) – characterized by transactions which retrieve and update just a handful of rows
• decision support (DSS) – characterized by queries that scan very large numbers of rows
• database maintenance tasks – which include such diverse activities as database backup and recovery, index creation and database loads

It will be seen that in each of these areas, the use of F5100 flash devices for database storage can improve overall performance by as much as a factor of 4 or 5, or by as little as just a few percent. The wide variations show that flash may not speed up all applications.

The degree of improvement seen with any particular application depends upon certain characteristics of that application. For example if an application consumes almost all of the I/O channel bandwidth in a disk-based configuration, replacing disk with flash will provide little or no performance benefit.

In addition to investigating the use of flash for database storage, the use of flash for redo logs is also considered. The guidelines for optimizing performance with flash-based redo logs will turn out to be somewhat different than those for database files.

As stated above, the approach of this paper is to investigate the performance implications of replacing all conventional disks in an Oracle database with flash devices. Prior studies of flash in the Oracle database environment have looked at performance improvements resulting from mixed flash and conventional disk configurations.

The organization of the rest of the paper is as follows:
• Section 2 compares some of the underlying characteristics of flash and disks.
• Sections 3 and 4 describe the configurations used for the tests that were performed.
• Section 5 discusses how the use of flash storage can improve query performance in decision support environments.
• Section 6 does the same for OLTP environments.
• Section 7 describes how the use of flash can speed up database maintenance operations.
• Section 8 summarizes the conclusions
• In addition, there are two appendices which show the SQL statements used for all the tests.

Full article...