SSD and Enterprise Storage: 4 effective parameters for performance measurement

A solid-state drive (SSD) is a data storage device that uses solid-state memory to store persistent data. An SSD emulates a hard disk drive interface, thus easily replacing it in most applications. Unlike mechanical hard disk drives, solid state disks are made up of silicon memory chips and have no moving parts. As with hard disks, the data is persistent in SSDs when they are powered down. A common method is to keep as much application data as possible in server memory, thereby reducing the frequency with which the application must retrieve data from the physical HDDs, as this process has much longer read or write latency than server memory.

Understanding parameters for measuring Oracle DB Benchmark

Deploying SSD in place of hard disk drives can result in immediate performance gains and can eliminate the bottlenecks caused by mechanical hard disk I/O latency. Oracle Performance Tuning with Solid State Disk provides a comprehensive guide that enables DBAs to make the transition to SSD successfully. By accelerating Oracle databases, applications can handle more transactions, more concurrent users and deliver higher profits and productivity gains. SSD is especially useful for Oracle undo logs, redo logs and the TEMP tablespace, but it can be used with any Oracle data file for unbelievable access speed.

The blog discusses the process of identifying the I/O subsystem problems, analyzing what to put on the SSD and understanding the Automatic workload repository (AWR).

1. Identifying I/O Subsystem Problems

The I/O subsystem is a vital component of an Oracle database. Oracle database is designed so that if an application is well written, its performance should not be limited by I/O. Tuning I/O can enhance the performance of an application if the I/O system is operating at or near capacity and is not able to service the I/O requests within an acceptable time.

If your system is experiencing I/O subsystem problems, the next step is to determine which components of your Oracle database are experiencing the highest I/O and in turn causing I/O wait time. Better performance can be achieved by isolating these hot data objects to an SSD file system.

2. Analyzing What to Put on Solid State Disk

There are two types of operations in Oracle that utilize the high speed disk subsystem: database reads and database writes. Oracle database reads should be as fast as possible and allow for maximum simultaneous access. In order to support the highest possible read speed the disk assets must provide for low latency access to data from multiple processes.

With an SSD, latency is virtually eliminated and data access is immediate. SSD architecture also allows for many high-bandwidths I/O ports, each supporting simultaneous random access without performance degradation. The speed of a Solid State Disk cannot be slowed down by mechanical limitations and its access latency is improved by several orders of magnitude.

3. Analyzing Oracle AWR Report

We can analyze the IO and wait interface statistics by:

• Oracle Enterprise Manager
• AWR and STATSPACK reports.
• Custom Scripts

The Oracle Enterprise Manager provides excess of data and reports for Oracle database activity. There are custom scripts also available for identifying the hot data objects. AWR (Automatic Workload Repository) and STATSPACK reports allow us to take a focused look at specific time intervals.
Reading the AWR Report
This section contains detailed guidance for evaluating each section of an AWR report. The key segments in an AWR report include:

• Report Summary Section: This gives an overall summary of the instance throughout the snapshot period, and it contains important aggregate summary information.
• Cache Sizes: This report displays the size of each SGA region after AMM has changed them. This information can be compared to the original init.ora parameters at the end of the AWR report.
• Load Profile: This section shows important rates expressed in units of per second and transactions per second.
• Shared Pool Statistics: This is a good summary of changes to the shared pool during the snapshot period.
• Top 5 Timed Events: This is the most important section in the AWR report. It shows the top wait events and can quickly show the overall database bottleneck.

Custom Scripts utilize the V$ series of views to generate reports showing I/O distribution, timing data and wait statistics. For data and temp file-related statistics, the v$filestat and v$tempstat tables are utilized. For wait interface information, the v$waitstat, v$sysstat and v$sesstat tables can be utilized.
A look at the OS IOSTAT command confirms that the I/O subsystem is undergoing an extreme amount of stress.
We had used IOSTAT and VMSTAT for collecting 5 second intervals during the entire duration of the testing. This gave us real-time data on RAM paging and CPU enqueues. In this benchmark, we used AWR reports to identify the performance metrics by referring various sections available.

4. Identifying the Most Frequently Accessed Tables

The I/O Stats Section in AWR reports shows all the important I/O activity for the instance and shows I/O activity by tablespace, data file, and includes buffer pool statistics.
From the AWR report generated during the initial Benchmark TPC-C test, the segment I/O statistics section reported the information used to isolate specific objects that would benefit from being placed on SSDs. The segments with the most logical reads and physical reads are presented in below tables. These segments should be considered as possible candidates to be placed on SSDs.
Table 1: Segments by Logical Reads
Table 2: Segments by Physical Reads

For our specific example, the user indexes such as C_ORDER_LINE_I1, C_ORDER_I1, and C_STOCK_I1 indexes and tables such as C_CUSTOMER, C_ORDER_LINE and C_STOCK involving the largest number of reads were selected to move to SSD.

The Oracle database stores data on the files that are accessed in the V$FILESTAT table. This table starts gathering information as soon as a database instance is started. When a database instance is stopped, the data in the V$FILESTAT table is cleared. Therefore, if the database instance is routinely stopped, it is important to capture the data from the V$FILESTAT table before the data is cleared. It is possible to create a program to gather this data and move it to a permanent table.
The following fields are available from V$FILESTAT:

• FILE#: Number of the file
• PHYRDS: Number of physical reads done
• PHYBLKRD: Number of physical blocks read
• PHYWRTS: Number of physical writes done
• PHYBLKWRT: Number of physical blocks written

A simple query and report from the V$FILESTAT table will indicate which Oracle database files are frequently accessed. Adding PHYRDS and PHYWRTS gives the total I/O for a single file. By sorting the files by total I/O, it is possible to quickly identify the files that are most frequently accessed. The most frequently accessed files are good candidates for moving to SSD.