Oracle provides the back-end RDBMS plumbing for many of the world’s most demanding applications. It structures data to allow powerful queries and transactional systems to operate. Improving the performance of these critical applications is often the most important way an IT department can help an organization. The traditional approach to improving performance, adding additional processing power, will often do little or nothing to improve Oracle performance. This is because the processor, no matter how fast, finds itself constantly waiting on mechanical storage devices for its data. While every other component in the “data chain” moves in terms of computation times and the raw speed of electricity through a circuit, hard drives move mechanically, relying on physical movement around a magnetic platter to access information. In the last twenty years, processor speeds have increased at a geometric rate. At the same time, however, conventional storage access times have only improved marginally. The result is a massive performance gap, felt most painfully by database servers, which typically carry out far more I/O transactions than other systems. Super fast processors and massive amounts of bandwidth are often wasted as storage devices take several milliseconds just to access the requested data. When servers wait on storage, users wait on servers. This is I/O wait time, a problem the RamSan was designed to solve.

• Complete queries faster and reduce commit time. RamSan solid state disks leverage semiconductor based storage to dramatically reduce the latency of an I/O access to microseconds - not milliseconds like traditional disks.
• Support more users. Enabling more queries to run in parallel stresses storage as more I/Os have to be completed in the same time. The RamSan delivers scalability by supporting hundreds of thousands of I/O per second verse the 100-200 that an enterprise disk can handle.
• Accelerate OLAP systems. Complex analysis queries can require full table scans that will use all available storage bandwidth. The typical solution of adding more and more drives to scale performance can end up filling a whole rack with expensive power hungry disks for demanding OLAP applications. RamSan products provide massive bandwidth in a small package.

Determining if an Oracle Database is I/O Bound

Oracle tracks what it is doing and what it is waiting for. This information is easy to access from an Oracle Statspack or AWR report. Texas Memory Systems sponsors StatspackAnalyzer.com, a free resource to the Oracle community which analyzes Statspack and AWR reports. Determining if an Oracle database is I/O bound can be as simple as looking for I/O related wait events taking up the majority of the database time.

White Papers

ASM and RamSan Best Practices

Oracle Automatic Storage Management (ASM) is an all-inclusive approach to storage management, performance, and availability. ASM is an excellent tool for managing mixed storage environments where both SSD and HDD technologies are being used. It is an intrinsic block manager database that balances file access across disks and disk subsystems to eliminate hotspots and increase storage efficiency. By striping extents across diskgroup members and mirroring across failgroups, ASM can give RAID performance and protection at the file-level of databases. ASM can also be used in conjunction with high-end disk arrays for mixed-storage file management.

ASM operates as a lightweight Oracle database. Since multiple databases can be clients to a single ASM instance, a single pool of disks can be efficiently used for various databases. ASM will not automatically separate storage areas in diskgroups; it is still the duty of the architect to specify storage for specific roles. The practice of tiered storage, implemented with homogenous diskgroups, maximizes the speed of the RamSan products.

Following these guidelines will allow you to derive the greatest benefits from ASM:

1. Separate the ASM Home from that of any normal database.
2. Use homogenous diskgroups.
3. Assign task-specific diskgroups for tiered storage.
4. Take advantage of multipathing.
5. Allocate enough capacity for groups where disk failures are probable.
6. Use external redundancy for arrays that provide mirroring and replication.
7. Use Preferred Mirror Read and Fast Mirror Resync to get the most performance from your RamSan products.

Using ASM with tiered storage utilizing RamSan products gives greater value. Savvy usage of ASM will unburdened disk storage, allowing you to capitalize on your investment by gaining higher numbers of transactions through lower latency and higher throughput.

For more information, please download PDF white paper:
Best Practices for Using ASM with RamSan SSDs

Faster Oracle Performance with Solid State Disks

You can improve your Oracle database performance by using solid state disks to accelerate the most resource-intensive data. Often, additional processing power alone will do little or nothing to improve Oracle performance. This is because the processor, no matter how fast, finds itself constantly waiting on mechanical storage devices for its data. In the last twenty years, processor speeds have increased at a geometric rate. At the same time, however, conventional storage access times have only improved marginally.

When servers wait on storage, users wait on servers. This is I/O wait time. Solid state disks are designed to solve the problem of I/O wait time by offering 250x faster access times (.02 milliseconds instead of 5) and 80x more I/O transactions per second (400,000 instead of 5000) than RAID.

One important step in improving your database’s performance is to identify I/O performance bottlenecks and identify components that are the best candidates for migration to a solid state disk. Looking at system performance is the best way to identify I/O wait time. Depending on your operating system, various tools can be used, such as Performance Monitor, in Microsoft Windows or top, iostat and sar, in UNIX. Oracle also provides the Statspack, AWR and ADDM utilities to monitor database performance. Use these tools to determine if you are having I/O subsystem problems.

The next step is to determine which components of your database are causing the I/O wait time. Inspect these components:

• entire database
• redo logs
• indices
• temporary tablespaces
• rollback data
• frequently accessed tables

These components are frequently the cause of I/O wait time and moving them to solid state disk, which offers much faster access times, will provide a dramatic improvement in application performance.

In summary, this white paper discusses methods for improving Oracle database performance using solid state disks to accelerate the most resource-intensive data that slows performance across the board. It discusses methods for identifying I/O performance bottlenecks, and it points out components that are the best candidates for migration to a solid state disk. An in-depth explanation of solid state disk technology and possible implementations are also included.

For more information, please download PDF white paper:
Faster Oracle Performance with Solid State Disks

Oracle, TPC-C, and RamSans

The process of documenting how a particular system configuration will perform for a particular type of load is called a benchmark. A benchmark provides a performance metric that can be used to judge future performance or performance of other system configurations against the base benchmark. Modeling typical user loads and transactions is critical to provide an accurate baseline measurement of performance.

If your system will be performing many small transactions with numerous inserts, updates, and deletes, then a test that measures online transaction performance (OLTP) would be the proper benchmark. The Transaction Processing Performance Council provides an online transaction processing benchmark, TPC-C, that is useful for measuring OLTP performance. This benchmark simulates typical user transactions in accessing a database.

In the series of tests described in this paper, we use the TPC-C benchmark. This paper demonstrates using TPC-C to compare and contrast hard disk drive (HDD) and solid state disk (SSD) based IO subsystems. The main system has four dual socket quad core 2GHz, 16GB memory Dell servers using InfiniBand interconnects for an Oracle 11g 11.1.0.7 RAC database. The I/O subsystem is connected through a Fibre Channel switch to two RamSan-400s, a single RamSan-500, and two JBOD arrays of 45 10K disks each.

The results of the benchmark show when the system peaks in its ability to process transactions (TPS) and the highest number of users that it can support. For the HDD IO subsystem, the system peaked at 1051 TPS and 55 users. The SSD IO subsystem peaked at 3775 TPS and 245 users.

For more information, please download PDF white paper:
Oracle, TPC-C, and RamSans

Oracle, TPC-H, and RamSans

The process of documenting how a particular system configuration will perform for a particular type of load is called a benchmark. A benchmark provides a performance metric that can be used to judge future performance or performance of other system configurations against the base benchmark. Modeling typical user loads and transactions is critical to provide an accurate baseline measurement of performance.

The Transaction Processing Performance Council provides a benchmark, TPC-H, used to mimic applications that have long or complex selects, reports, and large inserts and updates, but few deletes. Generally the TPC-H benchmark mimics the behaviors of a data warehouse (DWH) or decision support system (DSS) in pseudo-star schema layouts. The TPC-H benchmark performance is usually tied to the IOPS and bandwidth of the underlying IO subsystem.

In the series of tests described in this paper, we use the TPC-H benchmark. This paper demonstrates using TPC-H to compare and contrast hard disk drive (HDD) and solid state disk (SSD) based IO subsystems. The main system has four dual socket quad core 2GHz, 16GB memory Dell servers using InfiniBand interconnects for an Oracle 11g 11.1.0.7 RAC database. The I/O subsystem is connected through a Fibre Channel switch to one RamSan-400, one RamSan-320, one RamSan-500, and two RAID5 arrays of 14 disks each.

The test scenario involved using either one or eight streams of queries with no delete, insert, or update transactions with the goal of determining the effects on performance by placing the data and indexes on SSD verses placing them on HDD.  The results for the single stream show the RamSan drives outperforming the HDD arrays by an average factor of 2.95, that is, a performance improvement of 295%! For the eight stream scenario, the average improvement factor was 391 (391%).

For more information, please download PDF white paper:
Oracle, TPC-H, and RamSans

Quick Statspack and AWR Analysis

Mike Ault, TMS's Oracle Guru, performs detailed analyses on results from Oracle's Statspack and Automatic Workload Repository reports. These two tools provide a means to gather and monitor performance metrics and statistics, but once you've gathered the data, how do you interpret it and use it to increase your system's performance? This white paper provides a comprehensive checklist to follow for those wanting to learn how to get the most from their system.

Some of the highlights from his list include advice concerning restricting a report to the time-frame in which a problem occurs and also verifying that the report covers the node that is experiencing the problem. In reviewing the load profile section of the report, pay attention to the ratio of hard parses to parses. A high ratio can indicate problems with bind variables or versioning.

The Top Five Wait Events is arguably the most important part of the report. You can find evidence of memory starvation or excessive full table scans as well as problems with your log files or temporary tablespaces. Mike also offers plenty of tips for what to look at in the Instance Activity Status, such as SQL*Net Round Trips from Client and from DBLink. High numbers here could indicate that array passing for results is not being used efficiently. I've hit on just a few of the 28 items in Mike's list. Please download and read the full list for more information.

For more information, please download PDF white paper:
Quick Statspack and AWR Analysis

The Ultimate Oracle Architecture: OPERA (Oracle Performance Enhancing Reliable Architecture)

Oracle databases are often the thread that holds the fabric of an organization together. The Oracle system must perform well, ensure reliability, and be cost effective. But what do these requirements entail?

Performance – Queries, reports, and screens must return in an acceptable period of time to the requestor. What is “acceptable” is negotiable in some cases; in other cases your feet are held to the fires of various service level agreements (SLA).

Reliability – A single system fault must not be able to bring the system down.

Cost – A solution has to deliver cost effective performance and reliability.

In this paper we will show a new Oracle system architecture that fulfills all of the above requirements. By leveraging the available features of Oracle itself, and new technologies available from Texas Memory Systems (TMS), a high-performance, reliable, and cost-effective system can easily be constructed.

For more information, please download PDF white paper:
The Ultimate Oracle Architecture: OPERA

Using Oracle ASM Preferred Read Failure Groups with RamSans

The concept of the Preferred Read is not a new idea, but is now implemented in Oracle’s ASM volume management in Oracle 11g. The concept is to read from the storage that can present the needed data at a lower latency. Initially, this was designed for WAN or site-specific storage in order to avoid higher-latency site connections. By restricting data reads to the local storage, the application would be able to service requests at nominal read speeds while writes were the only communication needed to traverse the long haul site link. This is a feature that is available to most Operating Systems with their included volume manager and as a feature to Symantec/Veritas through the title Preferred Plex. This paper discusses the merits of using PRG technology with Oracle ASM and RamSan technology.

As each node of a cluster runs its own instance of ASM, each node can also define its own preferred read failgroup. This allows for each node to operate at top performance with its local storage. Since the concept of preferred reads is to read from a lower latency mirror, the strategy of preferring the reads from a RamSan mirrored to HDD can also be employed. This allows RamSans to be deployed with full redundancy at a much lower cost by mirroring to HDD.

Higher numbers of transactions through lower latency and higher throughput from unburdened disk storage by utilizing preferred read mirrors illustrates the raw performance gain of using ASM with tiered storage. This paper provides a detailed example of this concept and the results of a test comparing a RamSan system to HDD system.

For more information, please download PDF white paper:
Using Oracle ASM Preferred Read Failure Groups with RamSans

Using the TMS Write Accelerator

Texas Memory Systems is now offering the Write Accelerator product for databases. The Write Accelerator offers relief from slow downs caused by, as its name implies, waits for writes. In the Oracle universe, data writes are not usually an issue, as Oracle uses the delayed block clean out process that does what is known as lazy-writes, only writing dirty blocks back to disk when absolutely needed. However, there are three aspects of Oracle writes that need write speeds to be as fast as possible. These are writes to the redo logs, writes to the temporary tablespace and writes to the undo tablespace.

As transactions grow more numerous and longer in duration the redo logs see a considerable increase in stress levels. This increase in stress shows up as redo log related wait events in the Oracle wait interface. In order to alleviate this stress DBAs usually end up moving redo logs to separate file systems, sometimes even different separate disk RAID areas. Redo logs are also getting larger as the number of concurrent transactions they service increase in number and size. One parameter available in Statspack and AWR reports that tells if your redo logs are oversized is the redo wastage statistic. If this value is large in ratio to the number of redo write events then your logs are probably oversized.

Temporary tablespaces are the swap areas of Oracle. If a process requires more data than can be held in memory, then temporary space is used. The only way to reliably see if there is excessive temporary usage is to monitor the IO to the temporary tablespace. If temporary tablespace IO becomes the major source of IO operations in the database, its IO operations can significantly degrade the overall performance of the array. The temporary tablespace should be placed in an “isolated” area from the other tablespace datafiles to prevent its IO operations from degrading their performance.

Undo writes occur when a transaction changes data. Undo segments are generated to hold before-image records of data that is being changed. In a high manual transaction environment, undo tablespace reads and writes can become a major source of IO in the system. If you have optimally sized and placed your undo segment tablespaces on their own RAID and yet due to transaction load you still see excessive wait times, then the Write Accelerator from Texas Memory Systems will solve your problems. In tests using the Write Accelerator technology, moving the undo processing for the above test procedure to the Write Accelerator improved processing time by up to 50 percent or more allowing 50 percent or more transactions to be processed in the same amount of clock time.

Many applications can see immediate improvements by moving all three sources of write waits - redo, temporary and undo- to a high speed device such as solid state drives. After removing these sources of write contention, a careful analysis of the database can also reveal other sources of read/write issues such as hot indexes and tables that will also benefit from being moved to SSD. This paper explains how to monitor each of these sources of write waits and provides in-depth examples.

For more information, please download PDF white paper:
Using the TMS Write Accelerator

Validating Your I/O Subsystem: Coming out of the Black Box

For most people the IO subsystem for their servers is a black box. You pick up the phone and call the storage people and tell them how much room you need and it mysteriously appears at the server interface ready for use. Unfortunately while the capacity (as defined in megabytes) is easy to confirm, the performance (defined by input and output operations per second (IOPS) and latency (milliseconds, ms) may be more difficult to determine. In this paper we will explore different methodologies to obtain the IOPS numbers for your application.

One such methodology is through benchmarking your application. Modeling your database provides a firm foundation on which to predict future needs based on current trends. To do this accurately, you must know what your normal user load and transaction mix are.

We discuss the usage of IOMETER and Oracle's IO benchmarking utility, ORION, to generate storage workloads. These tools allow simulation of an environment without having to install Oracle. Since they don't process any of the sent or received data, the processing power requirements of the server running the tests are minimal.

For more information, please download PDF white paper:
Validating Your I/O Subsystem