The following list highlights default settings that we recommend maintaining for optimal storage performance:

• For mission critical Oracle databases, we recommend separating the following database components into different ASM disk groups: 
  • GRID disk group for Oracle grid infrastructure and Voting disks (if RAC is configured).
  • DATA disk group for database files
  • REDO disk group for online Redo logs
  • TEMP disk group for Temp and FRA disk group for flashback logs and archive logs
• Follow Oracle ASM best practices to create ASM disk groups for GRID, DATA, REDO, FRA, and TEMP.
• Use multiple I/O controllers in VM hard disk configuration to distribute storage volumes and VM hard disks to multiple I/O controllers for I/O load balance.

As part of the storage layer tuning, we have tested the following parameters:

1. PowerStore Multiple Volumes 
2. PowerStore Performance Policy

Multiple volume ASM Disk Groups

Storage configuration can directly influence database performance. This best practice validates that adding multiple storage LUNs for ASM disk groups optimizes database performance. 

Overview

To determine if a best practice provides value, it had to be tested against a baseline configuration of the database to validate performance.  In the baseline configuration for database volumes, the DATA, REDO, and FRA disk group each consists of one LUN. Table 16 shows the baseline volume groups and LUNs configuration. 

The baseline storage configuration followed the principle of separating database files into dedicated volume groups. For example, the Data files, Online Redo logs, and FRA files are in the same volume group, but separated from the Grid volume group and the Temp volume group. This allows the DBA to only snapshot the database without including the Grid and Temp files which are not necessary for a functional database snapshot.  This also provides space savings if replication is used.  

Table 17: Baseline volume group configuration

Table 18 shows the optimized PowerStore storage configuration. The volume groups remain intact, but additional LUNs were added for the DATA, REDO, and FRA disk groups. Using multiple LUNs for Oracle ASM disk groups improves database performance. The primary benefit of using multiple LUNs is that the operating system creates an I/O queue path per LUN. Since Oracle ASM spreads data evenly across all disks in a disk group, this would reduce any I/O bottleneck and provide higher I/O concurrency providing performance optimization and utilization. The following table shows the optimized PowerStore storage configuration for the Oracle database. In the optimized storage configuration, DATA, REDO, and FRA disk groups each has four LUNs. Compared to the baseline configuration, there are four times the number of I/O queue paths. 

Table 18: Optimized PowerStore storage configuration

Once the additional LUNs are created, these LUNS can be added to the ASM disk groups. The size of the LUNs for each ASM disk group should be determined by the size of the database.  The PowerStore storage array has thin provisioning feature, meaning LUNs should be created larger than required.  However, to truly benefit using thin-provisioned LUNs on the PowerStore, the use of Oracle “autoextend” clause when creating tablespaces is required.  For detailed PowerStore best practices and features, see Storage Best Practices. 

Recommendation

Our results indicated that having multiple LUNs for the ASM disk groups increased performance significantly for the following metrics:

• New Order per Minute (NOPM)
• Transactions per Minute (TPM)
• PowerStore IOPS
• Server CPU utilization

Based on these results, it is considered as a Day 1, Highly Recommended best practice.

Implementation steps

Before adding additional three LUNS/volumes to each ASM disk group, we performed the following tasks to present the additional volumes to the guest VM RHEL OS where the Oracle database resides.

• Present these volumes to the ESXi host and created the corresponded datastores based on these volumes
• Created the hard disks from these data stores and presented these hard disks to the guest VM Red Hat Enterprise Linux OS as VMDKs. 
• Use these devices as a whole disk (/dev/sd?)
• Created udev rules to assign Linux 0660 permission and ownership of these devices to the grid user and create alias name such as ‘/dev/oraudev/disks/ora-data2’ for each device. 

After performing these tasks, the following table shows the device names, their corresponding volume groups, and the ASM disk groups. 

Table 19: Volume groups, data stores, UDEV devices and ASM disk groups

For the baseline configuration, each ASM disk group of DATA, REDO, and FRA only has a single volume. For example, DATA disk group has one volume: ‘/dev/oraudev/disks/ora-data1’. To add the additional three volumes to the ASM disk groups, DATA, REDO, and ARCH, ensure that all four LUNs in the same disk group are of the same size.  Perform the following SQL statement in the ASM instance to add the three ASM disk groups, DATA, REDO, and FRA:

SQL> ALTER DISKGROUP data ADD DISK

'/dev/oraudev/disks/ora-data2' NAME DATA_0001,

'/dev/oraudev/disks/ora-data3' NAME DATA_0002,

'/dev/oraudev/disks/ora-data4' NAME DATA_0003;

SQL> ALTER DISKGROUP redo ADD DISK

'/dev/oraudev/disks/ora-redo2' NAME REDO_0001,

'/dev/oraudev/disks/ora-redo3' NAME REDO_0002,

'/dev/oraudev/disks/ora-redo4' NAME REDO_0003;

SQL> ALTER DISKGROUP fra ADD DISK

'/dev/oraudev/disks/ora-fra2' NAME FRA_0001,

'/dev/oraudev/disks/ora-fra3' NAME FRA_0002,

'/dev/oraudev/disks/ora-fra4' NAME FRA_0003;

It is recommended to set up the ASM disk groups with multiple LUNs during the initial configuration but if this is an existing database, you can run these SQL statements to add volume to ASM disk groups while the database is still running. It is preferable to run these statements outside of the database workloads’ peak time, as adding disk volume operations results in data rebalancing and may cause a significant performance overhead. The rebalancing power is defined by the ASM_POWER_LIMIT initialization parameter (default value = 1, range to 1024). The higher the limit, the faster a rebalance operation may complete, and the rebalance results in more performance overhead.

To monitor the status of the rebalancing operation, run this SQL statement to check the operation status:

SQL> select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM OPERATION;

   INST_ID OPERA STAT      POWER      SOFAR   EST_WORK   EST_RATE EST_MINUTES

---------- ----- ---- ---------- ---------- ---------- ---------- -----------

         1 REBAL WAIT          1          0          0          0           0

         1 REBAL RUN           1      14185     179546       7085          23

         1 REBAL DONE          1          0          0          0           0

In this example, the output shows three rebalancing operations: the first is done (bottom), the second is running (middle), and the third is waiting (top). For the second row, the report shows:

• EST_WORKD=179,546 - estimated number of Allocation Units that must be moved
• SOFAR = 14,185 - number of Allocation Units that have been moved so far
• EST_RATE=7,085 - number of Allocation Units that are moved per minute
• EST_MINUTES= 23 – remaining estimated amount of time (23 minutes) =

PowerStore: Volume Performance Policy

Volume performance can be managed in PowerStore by adjusting the performance policy to high, medium, or low. Selecting a performance policy sets a relative priority for the volume by using a share-based system that prioritizes the higher performance policy when resources are constrained. The performance policy does not impact on system behavior unless some volumes have a low performance policy setting while other volumes are set to medium or high.

This section discusses the Volume Performance Policy of the PowerStore. This best practice validates that changing the volume performance policy for the database volumes from the default (medium) to “High” may optimize database performance in certain configurations.

Overview

To determine if changing the volume performance policy provides value, it had to be tested against the baseline using the default value (medium). In the baseline configuration, the database volumes for the Oracle DATA, REDO, and FRA ASM disk groups were configured with the “medium” performance policy.

Table 19 below shows which volumes were configured with the performance policy of “High”.

Table 20: Volume Performance Policy Settings

Recommendation

There was no change in performance based on the test results after changing the volume performance policy to High for the Oracle REDO disk group.  Although, changing the volume performance policy for the REDO disk group yielded no gain in performance, it is still recommended as this could potentially help improve database performance when the PowerStore storage array is heavily used due to workload consolidation.  Therefore, this is considered as a Day 3, Fine Tuning best practice.  

Implementation steps

To change the volume performance policy for the volumes, use the PowerStore Manager and follow the instructions from PowerStore Manager Overview.

The following figure shows the performance policy for the volumes of VM1.

Figure 13: Volume Performance Policy settings