Home > Workload Solutions > Oracle > Best Practices > Intel-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Storage Best Practices > Day Three Best Practices > Oracle: ASM Fine Grained Striping of REDO Logs
Automatic Storage Management (ASM) provides the capability to define the allocation unit (AU) size of storage. This best practice validates the recommendation to use fine-grain striping for the database redo logs.
Category | PowerMax Storage |
Product | Oracle |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine-tuning |
Overview
Oracle ASM uses AUs to stripe data across each disk in the disk group. The size of the AU determines its stripe-depth. By default, the ASM allocation unit size is 1 MB and 4 MB for version 12.2 and later. The default AU size is named Coarse Striping and is optimal for OLTP workloads. When configuring ASM AU size, there are factors that influence I/O performance. In this infrastructure, the interconnection between PowerMax and the PowerEdge R740 servers defines how ASM storage performs.
During normal database operations, most changes to the database are captured to the redo logs. The process of capturing database changes generates continuous large sequential writes to the redo logs. As the PowerMax breaks large continuous writes into smaller I/Os, a performance gain can be realized as storage latencies lower. ASM offers fine-grain striping for the redo logs, providing the capability to move from a 1 MB / 4 MB allocation unit size down to a smaller 128 KB size.
In this best practice, the database team changed the ASM AU size for redo and archive to fine-grain striping. The following table shows the baseline configuration used for coarse grain striping for all of the ASM disk groups. The right-most column of Table 1 shows that redo and archive disk groups were changed to fine-grain stripe size.
Table 1: Baseline configuration used for coarse grain striping
Parent storage group | Child storage group | Baseline ASM AU stripe size | Best practice of ASM AU stripe size |
SAGE_DB1 | DB1_DATA | Coarse | Coarse |
| DB1_REDO | Coarse | Fine |
| DB1_ARCH | Coarse | Fine |
SAGE_GRID |
| Coarse | Coarse |
SAGE_FRA |
| Coarse | Coarse |
SAGE_TEMP |
| Coarse | Coarse |
It is important to note that archive logging was not on for validating this best practice, meaning the performance results reflect only the change in AU size for redo logs.
Recommendation
Changing the redo log to use ASM fine-grain striping showed no performance improvement. Test results showed no improvement for the following:
Overall, the only positive gain was a slight decrease in Log File Parallel Write times. While there were no substantial performance improvements for this OLTP work, customers with other workloads might achieve better results. In-memory databases and large batch data workloads could push a greater volume of writes to the redo logs and would benefit more from fine-grain redo logs.
The recommendation for fine-grain striping of the redo logs is a Day 3, fine-tuning activity. Customers with large sequential write workloads on the redo logs will benefit the most from this best practice.
Implementation Steps
To set the FINE granted striping for REDO and ARCH disk groups, run these SQL statements in the ASM instance:
ALTER DISKGROUP REDO ALTER TEMPLATE ONLINELOG ATTRIBUTES (FINE);
ALTER DISKGROUP ARCH ALTER TEMPLATE ARCHIVELOG ATTRIBUTES (FINE);
With these fine-striping settings, the fine-striping writes 128 KB data to each ASM Disk in the REDO and ARCH disk groups in a round robin fashion: 128 KB goes to the first disk, then the next 128 KB goes to the next disk, until complete. The fine-grained stripe size always equals 128 KB in any configuration – this can lower I/O latency for sequential writes.
References
Deployment Best Practices for Oracle Database with Dell EMC PowerMax