Home > Workload Solutions > Oracle > Best Practices > AMD-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Introduction to Best Practices for Oracle > Oracle Best Practices for AMD-based PowerEdge and PowerMax systems
The development of the Oracle best practices program had one primary goal: improve customer outcomes using Dell EMC infrastructure. In today’s world, learning what best practices to use can be a challenge. Having to read multiple manuals to find out how an Oracle database should be optimally designed takes a lot of time. The goal of this Oracle best practice program is to centralize and validate recommendations that will make you successful in running a database on our infrastructure.
Our approach to validating best practices starts with gathering recommendations from multiple sources and categorizing the guidelines into different layers of the database infrastructure. For example, in the case of the optimal PowerEdge configuration for databases the team pulled best practices from current manuals and held meetings to review the findings with PowerEdge experts. This approach means we actively worked with experts and engineers responsible for each part of the Oracle infrastructure. Below is a list of each infrastructure layer that was validated for best practices, in the order tested:
Before starting the validation work the Engineering team conducted internal load testing to determine the workload profile. HammerDB was used to generate an Online Transaction Processing Workload (OLTP) that simulates enterprise applications. The goal of generating a significant load on the Oracle infrastructure was to ensure the system was sufficiently taxed to show how best practices optimized performance. In this case, an overall CPU utilization of 80 percent was the initial target. The HammerDB workload configuration is shown in the following table:
Table 1: Hammer DB workload configuration
Setting name |
Value |
Total transactions per user |
1,000,000 |
Number of warehouses |
5,000 |
Minutes of ramp up time |
5 |
Minutes of test duration |
60 |
Use all warehouses |
Yes |
User delay (ms) |
500 |
Repeat delay (ms) |
500 |
Iterations |
1 |
With this HammerDB configuration, each best practice was validated in an hour-long workload test (5 minutes ramp up time plus 60 minutes test duration). This approach of running a workload for one hour to validate each best practice was used to ensure the database system reached a consistent state of execution. Reaching a consistent run state has the benefit of validating whether the configuration is stable and if the best practice shows value over time.
Of the HammerDB parameters used, “Use all warehouses” forces the use of all 5,000 warehouses. The result of forcing the use of all warehouses means the workload will generate more I/O on the storage array. This led to the discovery that an optimal storage configuration was required if the team was going to satisfy the 80 percent CPU utilization. Thus, the first set of best practices compares baseline database performance without an optimal storage configuration to a database configuration with an optimal storage configuration.
Within each set of best practices, the team sequentially tested a best practice or a set of practices. For example, with the storage configuration there were a total of seven configurations:
The first test within the storage configuration best practices was optimizing PowerMax storage groups. The goal was to show how optimizing PowerMax storage groups improves performance of the Oracle database. With each additional test, the working theory was that we would observe a gain in performance. Thus, with the last test in changing the PowerMax front-end I/O modules we would achieve the overall optimal storage design. This building upon prior best practices has the benefit of showing that when best practices are deployed together, overall database performance becomes optimized.
All best practices are not created equal. To enhance the value of best practices, we have identified which configuration changes produced the greatest results. This should enable anyone reviewing the best practices to easily identify which recommendations will have the most value. Best practices were categorized as follows:
When reviewing the best practices, the day and value of the recommendations are combined. Here are some examples:
With this way of ranking best practices, the hope is that customers can very quickly decide which recommendations will provide the best value. For example, two or three best practices from the section on storage optimization might provide most of the value. Investing time in implementing these best practices provides the greatest return. This approach can be taken for each layer of the database system until all Day 1, highly recommended best practices are completed. We believe this approach differentiates how we present best practices and provides a pathway for customers to have the best return on investment.
Best practices are broad recommendations designed to apply to most Oracle databases using Dell EMC infrastructure, but not every database. It is important to recognize that every database workload and system is different and thus, the value of these best practices will vary from system to system. As with any configuration or change to a database system, the best approach tests and validates the change before implementing the best practice on a production system. Thus, we recommend testing all best practices before implementing the changes in production.