Oracle 11gR2 I/O Performance Tuning: Implementing I/O Performance Improvements

April 5, 2011

The previous article in this series discussed how to:

• Evaluate AWR reports for indicators of any I/O performance bottlenecks
• Formulate initial hypotheses on how well my database's storage infrastructure is responding to different application workloads

• Improve I/O response time via intra-database solutions
• Improve I/O response time through enhancements to the I/O subsystem

The conundrum of 'repeatable' TPC workloads

What I really need to prove is that once I've applied changes to my database's storage subsystem to improve its performance, the database application workload is now performing better or worse after these changes. The only way to do that is to execute precisely the same application workload as many times as necessary, and then use the tools at hand — OS utilities, AWR and/or Statspack reports, ADDM analyses, and so forth — to obtain unequivocal proof on the storage system's performance. Fortunately, Oracle 11g provides me with exactly the tools to handle this requirement: Database Replay, part of the Real Application Testing (RAT) suite. (Yes, I know it's a separately licensed feature of Oracle 11g; I've documented its potential extensively in a prior set of articles on Oracle 11g Release 1 new features.)

Generating and capturing a realistic, repeatable workload

NOTE: I've included the complete set of upgraded sample code here; should you decide to adapt it for your demonstration purposes, you should only need to modify the Oracle database name and destinations for your development database's data files. These modules are only for demonstration of these Oracle 11g I/O tuning concepts and database features; they are not intended to be run within a production environment.

Preparing for a successful database replay

• So that my test database can always be returned to the same state before I begin a new round of testing, I'll leverage one of the most helpful features that Oracle added in Release 10gR2: the ability to "rewind" a database back to the same point in time using Flashback Database. This feature requires the use of flashback logs that are automatically written to the database's Fast Recovery Area to rewind the database back to a chosen point in time. Note that the database must be in ARCHIVELOG mode to enable flashback log generation.
• To make it easier to manage the initial starting point of all testing, I'll declare a guaranteed restore point that will quite literally guarantee that Oracle 11gR2 will retain all archived redo logs and flashback logs needed to rewind the database back to an exact point in time just before the Database Capture operation started.
• I'll also turn off automatic generation of AWR snapshots; instead, I'll opt to allow Database Replay to control AWR snapshot creation immediately before and just after a Capture or Replay operation.

-----
-- Modifies AWR Snapshot settings:
-- 1.) Snapshots are NEVER taken automatically 
-- 2.) Snapshots will be retained FOREVER (MAX_RETENTION, or 100 years)
-- 3.) The default number of SQL statements consuming the most resources
--     (normally, 30 for high-resource Snapshots will be retained forever (MAX_RETENTION, or 100 years)
---
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
 interval => DBMS_WORKLOAD_REPOSITORY.MAX_INTERVAL
       ,retention => DBMS_WORKLOAD_REPOSITORY.MAX_RETENTION
,topnsql => 'DEFAULT'
    );
END;
/

-----
-- Bring database into ARCHIVELOG mode and turn on Flashback Logging
-----
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

-----
-- Create the GOLDENDB guaranteed restore point
-----
DROP RESTORE POINT goldendb;
CREATE RESTORE POINT goldendb GUARANTEE FLASHBACK DATABASE;

Starting workload capture

Generating the application workload

Figure 7.1: Oracle 11gR2 Enterprise Manager: Workload Capture Active Indicator

Completing workload capture

Finding room for improvement

Reviewing the captured workload's performance statistics

The first thing I can do to improve the database's I/O performance is to shift these datafiles to faster media or, barring that possibility, at least shift those datafiles to media that's isolated from the other tablespaces that participate in the workload. Fortunately, I've got an excellent trick up my sleeve as an Oracle DBA because it's a relatively simple matter to migrate my database's "hottest" data files to a file system that's uniquely constructed for effective database I/O: Oracle 11gR2 Automatic Storage Management (ASM).

Replaying a captured workload

My database already had an ASM disk group, +DATA, but I've made sure that this disk group's underlying ASM disks are on a different HDD separate from the original datafiles for the AP_DATA, AP_IDX and EXAMPLE tablespaces. (Of course, if this were a production environment I'd have to enlist the help of my storage administrator to find out if these HDD disk partitions were actually on a different physical device, but since I'm wearing both "hats" in my case, I know that these partitions were created on the proper physical device.) Before I migrate the tablespaces to ASM, however, I'll first "rewind" my database to its prior guaranteed restore point:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO RESTORE POINT GOLDENDB;
SQL> ALTER DATABASE OPEN RESETLOGS;

Migrating the tablespaces to ASM

Once the image copy backups are created, I'll take the three tablespaces offline while I switch them to their new destination. Obviously, in a production environment, I'd need to perform this intra-database "switchover" during an off-peak period to minimize disruption to my application user community. But the good news here is that these tablespaces would be unavailable only for the time it would take RMAN to apply any pending redo log transactions for those tablespaces.

Listing 7.2 shows the resulting output from these RMAN commands as well as contents of my database's alert log as these datafiles were successfully "switched" to the image copy backups on the +DATA ASM disk group, and then recovered successfully. Once the recovery was complete, I simply brought the tablespaces back online, and application workloads can now be accepted again.

Replaying a captured workload

To accomplish this, I'll first have to preprocess the previously recorded application workload using the DBMS_WORKLOAD_REPLAY.PROCESS_WORKLOAD procedure:

SQL> EXEC DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('DBRCONTROL');

SQL> EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('DBR_REPLAY_100','DBRCONTROL');
SQL> EXEC DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY;

wrc system/oracle@tpcdb replaydir=/home/oracle/DBRControl

SQL> EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY;

Figure 7.2: Oracle 11gR2 Enterprise Manager: Completing the Active Workload Capture As the workload progresses toward its conclusion, I can view how much longer or shorter it's taking to complete via this handy panel (see Figure 7.3):

Figure 7.3: Oracle 11gR2 Enterprise Manager: Completing the Active Workload Capture

Evaluating the new I/O configuration's effect

• For the AP_DATA tablespace's datafile, the average reads/second has improved from 2 reads/second to 3 reads/second. In addition, the average writes/second has increased from 3 to 5, and the average buffer wait time has improved almost threefold from almost 45ms down to slightly more than 15 ms).
• The AP_IDX tablespace's datafile has improved its read performance, as it was accessed about 29% more times (3325 reads vs. 4266 reads, or 6 vs. 9 reads per second). And even while it was written to much more frequently (3,688 time vs. 6,377 times) while executing an identical workload, the number of buffer waits decreased significantly (4.523 vs. 887).

• Finally, the EXAMPLE tablespace's datafile was hardly written to at all during both the capture and replay of the workload, but it was read from significantly more times (18,033 vs. 47,771) within the same period of time.

Next steps

• How to take advantage of Oracle 11gR2's new Flash Cache features to extend the database buffer cache without adding more memory to my database servers
• What application workload capture and replay features are offered in Quest Software's Benchmark Factory

References and additional reading

• E16760-05 Oracle Database 11gR2 PL/SQL Packages and Types
• E10881-02 Oracle Database 11gR2 New Features
• E10595-05 Oracle Database 11gR2 Administrator's Guide
• E10713-03 Oracle Database 11gR2 Concepts
• E10820-02 Oracle Database 11gR2 Reference
• E10500-02 Oracle Database 11gR2 Storage Administrator's Guide