Home > Workload Solutions > Oracle > Guides > Reference Architecture Guide—Dell EMC Ready Solutions for Oracle: Design for Unity All Flash Storage > Use cases, test methods, and test results
In this use case, we loaded data into Oracle Database 12cR2 on the Unity 650F storage array to understand the impact of data compression and deduplication, also referred to as data reduction. We tested this reference architecture by loading approximately 1.2 TB of data into the Oracle 12cR2 test database using the SLOB multiple schema model. The native SLOB schema contains highly redundant data and, therefore, is highly compressible. To remove redundancy and to showcase the Unity array’s data compression capabilities in the worst-case scenario, we used a custom PL/SQL script to insert randomized and unique data into the SLOB schema (for details, see Appendix A).
After loading approximately 1.2 TB of data into the Oracle 12cR2 test database, a space savings of 29.12 percent was realized from the data reduction features in the storage layer, as shown in the following figure.
Figure 6. Space savings for Oracle Database 12cR2 running on Unity 650F storage
As shown in the following figure, the CLI (sqlplus) interface reflects the schema capacity as seen by the Oracle database. The database sees the entire 1,224.91 GB of the data loaded in the IOPS tablespace, which validates that all the reduced physical capacity as seen on the storage is due to the Unity array's data reduction feature and is transparent to the database.
Figure 7. CLI interface (sqlplus) showing schema capacity as seen by the database
In this test, we loaded 100 percent unique data that was generated by the PL/SQL program. Through the Unity 650F storage compression capabilities, we reduced the amount of storage space required by 29 percent on a loaded data volume of 1,225 GB. The actual data reduction numbers might vary, depending on the workload, quality of data, or other factors that are unique to any typical organization.
Note: The compression numbers that are shown in this guide were generated by Dell EMC engineers on in-house equipment and are for reference purposes only.
The compression feature of the Dell EMC Unity 650F storage array generates storage cost savings (CAPEX) and TCO savings for a typical organization. For a similar demonstration of storage data reduction in Oracle Database 18c, see Use Case 1: Deduplication and compression of Oracle Database 18c in Chapter 4.
In this use case, we created a standalone Oracle Database 12cR2 on a Dell EMC PowerEdge R840 server, as shown in the following figure. We ran the performance test for 30 minutes using SLOB on an OLTP workload featuring an 80/20 read/write mixture. This database features an 8 KB block size with Automatic Storage Management (ASM) in a coarse-striped and externally redundant configuration.
Figure 8. Performance testing on 12cR2 DB running on Unity 650F (Test 1)
During the stress test, we collected performance data from the AWR report that was generated by the Oracle database. For database and SLOB parameter settings used during all test cases, see Appendix A. The following table shows the performance metrics that we captured from AWR for Test 1. We used these values as the baseline numbers for comparison in Use Cases 3 and 4.
Table 6. Test 1 performance results
Performance metric |
Value |
IOPS |
101,727 |
Database server CPU utilization (%) |
25 |
Database bandwidth (MB/s) |
805 |
Database response time (milliseconds) |
0.32 |
Transactions per second (TPS) |
6,869 |
The performance metrics in Table 6 show that the IOPS value was over 100,000 with an average database server CPU utilization of 25 percent and a database response time of 0.32 milliseconds (ms). The database server had plenty of capacity for performing other tasks while running this Oracle Database 12c.
The database bandwidth was healthy (805 MB/s) and the average response time for queries was quite fast at 0.32 ms. Also, the database performed 6,869 TPS, which means the commits and rollbacks were happening very quickly. We use the Test 1 results as a baseline to later compare these results to those that we obtained while creating snapshots in Use Case 3.
This use case shows the performance efficiency of Oracle Database 12cR2 on the Unity 650F storage array. In Chapter 4, we compare the results in Table 6 to the results we obtained when running the upgraded Oracle Database 18cR1 on the Unity 650F array.
The goal of this use case is to study the performance impact of creating Unity snapshots on Oracle Database 12cR2. This use case involves two tests—Test 2 and Test 3:
Note: Before taking the snapshots, we created a consistency group on the Unity storage array and added all Oracle database volumes to it. Dell EMC recommends taking snapshots of database volumes at the consistency group level rather than at the individual database-volume level to guarantee that the Oracle database snapshots can mount and restart successfully on the database host.
The following figures show the methodology for Tests 2 and 3.
Figure 9. Test 2 methodology
Figure 10. Test 3 methodology
The goals of Tests 1, 2, and 3 were to:
The following figure shows the comparison of IOPS among Test 1 (Use Case 2), and Tests 2 and 3 (Use Case 3) on Oracle Database 12c.
Figure 11. Total IOPS for Test 1, Test 2, and Test 3
As shown in Figure 11, with the snapshots created in Test 2 and Test 3, including the SLOB stress testing in Test 3, the IOPS dropped less than 0.7 percent in comparison to Test 1 (the baseline test). The IOPS numbers from these three tests prove that, despite requiring more system resources like drive I/O to handle metadata writes, this reference architecture generates impressive IOPS numbers.
The following figure shows the database server CPU utilization as captured by the AWR report for Tests 1, 2, and 3.
Figure 12. Database server CPU utilization (%) comparison for Tests 1, 2, and 3
Creating two snapshots caused virtually no change in CPU utilization in Test 2 and Test 3. In Test 3, which included stress testing with the SLOB tool after creating snapshots, CPU utilization decreased very slightly as compared to Test 2. Tests 2 and 3 prove that snapshot creation and stress testing do not have a major impact on the Oracle Database 12cR2 server running on the Unity 650F storage array.
Any data that is written to either the baseline database or to the snapshot database is redirected to a new write location in the same storage pool. The Unity storage array uses metadata to track data blocks belonging to the base objects, and snapshots of metadata consume more storage system resources such as CPU and memory to handle metadata updates. Even considering the metadata updates, there was no increase in database CPU utilization (Test 3) as compared to Test 1 (the baseline test).
The following figure compares database bandwidth in terms of MB/s for Oracle Database 12cR2 for Tests 1, 2, and 3.
Figure 13. Comparative analysis of database bandwidth for Test 1, Test 2, and Test 3
Figure 13 demonstrates that creating two snapshots and performing OLTP operations such as SLOB data loading do not adversely impact the bandwidth. Therefore, there is no impact on the performance of Oracle Database 12cR2 running on the Unity 650F storage array. On the contrary, the database bandwidth increased by 3 to 4 percent during performance stress testing and snapshot creation.
The following figure shows that the database response remained the same while snapshots were created during stress testing (Test 2) and when snapshots were created before the stress testing (Test 3) when compared with the baseline number (Test 1). Therefore, creating snapshots had no impact on the latency performance of the baseline Oracle Database 12cR2 running on the Unity 650F storage array, which is notable performance considering that there was also virtually no increase in the CPU utilization.
Figure 14. Database response time during Test 1, Test 2, and Test 3
Transactions per second (TPS) is also known as transaction throughput. The following figure shows the TPS results in Tests 1, 2, and 3. The figure shows a minimal drop in TPS of less than 1.3 percent in Test 2 and Test 3 compared with the baseline number from Test 1.
Figure 15. TPS during Test 1, Test 2, and Test 3
The performance metrics from Tests 1, 2, and 3 show that there was minimal impact from creating snapshots and applying stress testing, and there was no performance impact when running an Oracle Database 12cR2 on the Unity 650F storage array. This ability to maintain performance is helpful when you have to create multiple copies of the production database while the production OLTP workloads are running in parallel.
To further determine the storage capacity savings that is realized through the use of the deduplication and compression feature of the Unity 650F storage array during a data change, we increased the data that was loaded during Use Case 1 by 5 percent and captured the savings in storage capacity. The following diagram shows the comparison of storage capacity before and after the 5 percent data increase.
Figure 16. Data reduction achieved in 12c database after 5% data insertion
We performed Use Case 4 to find the data reduction percentage after inserting data in six new SLOB schemas with 100 percent randomized and unique data generated by a PL/SQL program. After loading the data in six new schemas, we achieved a space savings of 26.15 percent, as shown in Figure 16. As described in Chapter 4, we achieved similar savings by running the same tests on an Oracle Database 18cR1.