Estimate Tool for Sizing Oracle Workloads to Azure IaaS VMs
*Previously on the Microsoft Data Architecture Blog in the Tech Community
An an Oracle SME at Microsoft, it’s always important to find new ways to make the few of us that specialize in Oracle scalable. One of those is to find ways to provide documentation, blog posts and tools available to others so they can do some of the work we’re commonly brought into.
Infra vs. the RDBMS
It’s a standard practice in a lift and shift to simply take the existing server and move it to the cloud. This is rarely the best approach with an Oracle database, but with many IaaS initiatives, it’s easy to lump these in with application and file servers when the knowledge level gets thin with RDBMS environments. To properly migrate, there are a few things that need to be considered:
1. Was the server properly sized to begin with for the database workload?
2. Was the hardware over-provisioned to support the workload for years, lacking the flexibility of the cloud?
3. Are there optimization tasks that need to be addressed before moving to the cloud for long-term satisfaction?
The first two steps in this puzzle can be addressed with the sizing estimate tool that I’ve uploaded to github and will be discussing today. It’s a simple Excel spreadsheet, when combined with data from an Automatic Workload Repository, (AWR) report, (or Statspack if working with standard edition of Oracle) can provide the valuable data needed to size a VM for the workload.
Download the following files from the Github repository:
Defaults settings for AWR in any 11.2.0.4 Oracle database and above is set to a retention of 8 days of history and has snapshots taken once per hour. To properly size an environment, the window of the report, taken by entering a beginning and ending snapshot ID must encompass the entire workload that will be moved, (If you have month end reports, consider a 1 week run that includes the month end reports in the week’s worth of data.) If the database is RAC, get a “RAC-based” AWR report, (awrgrpt.sql from the command line if you don’t have Cloud Control) so you can enter in the appropriate values for each node, as this will be important to consolidate the nodes down to a single VM.
Find and Fill
Following the instructions, fill out the information for DB CPU, % Busy, SGA, PGA and then depending on the version of the AWR, take the time to find the correct IO stats information. The instructions go into deeper detail on this step. I also highly recommend looking at the recommendation for the PGA and SGA, using those for the values instead of what has been allocated if the report says either or both are undersized.
Once you’ve filled out the data, the secondary worksheet will provide you with the estimates of what will be required for vCPU, IO and memory. It will also provide a factor for peak loads, which need to be considered if the workload can vary extensively.
The first worksheet, (the one filled out with AWR data) will look like the following:
The numbers at the bottom of the sheet are used to calculate variations in peak loads vs. what was collected. For some of our customers, especially with the pandemic’s impact to business, they will ask to have higher “peak variance” to ensure they are ready for a return to business, seeing now as a good time to migrate to the cloud.
All data entered on the first worksheet, then corresponds to the database names, instance names and host names you enter on the second worksheet. You don’t enter any other information on the second page, as it will take the data from the first and calculate the sizing for you.
If the database is a RAC environment, it will consolidate it down to a single instance.
The user is then able to take that information, vCPU, memory, IO and use the Azure Pricing Calculator to size out a VM and storage disk to handle the workload they need to move to Azure for Oracle.
Disclaimer- This tool won’t work for Oracle Exadata, (which is an engineered system) or certain products such as SPARC, which may require some different calculation conversations to go to vCPU. That’s where #3 in the list in the beginning of the article comes in and requires an SME to come in a review at a much deeper level vs. just sizing out the Oracle database for an Azure VM.