Oracle on Azure- Sizing vs. Optimizing

As the flood gates open up on Oracle for Azure IaaS, working in an efficient manner has become a necessity.  We’re building out partners to help, but there are ways to empower our customers and those doing this work to make us all successful-  hopefully this post will assist.

After I posted the Estimate Tool for Sizing Oracle Workloads on Azure, I realized more guidance around AWR reports would be beneficial.  These tips will provide help to any migration, not just one to Azure, so read and reap the rewards!

Separate and Conquer

A sizing estimate isn’t the same thing as an optimization exercise.  Where optimizing a database workload is about the ‘what if’, a sizing estimate is about ‘what it takes now’ and shouldn’t be confused. They can be combined into a single offering to a customer, but it’s important to know, sizing takes the full workload calculations and sizes out the resources for vCPU, memory and IO/throughput required in the cloud vs. just taking what’s been already allocated for on-prem and using those [often] inaccurate infrastructure numbers.

Why are the infrastructure numbers often wrong?  For one, the host may be sized out to support the database for years and it can be difficult to know what resource needs will be required in 1, 2 or 5 years down the road.  As new objects, code and processes are added to a database over time, what was sized doesn’t match what is needed.  The second issue is how we architect for the cloud.  One of the biggest benefits is that the cloud is flexible and there’s no need to size a server out for years down the road.  You’re not purchasing hardware and you can scale as needed.  If you lift and shift the infrastructure, the values don’t represent what is being used or what is needed now.

An optimization exercise digs into the top database processing consuming those resources and identifies what steps can be taken to do so more efficiently.  It identifies waste and incorrect configurations that need to be addressed.

If a customer or account team request a sizing estimate to move a database to the cloud and understand that this is a different ask than an optimization exercise, then the next step is to ensure that the Oracle specialist has the right information to perform the task accurately.

Simplify

AWR data is our bread and butter.  The report is a monstrous buffet of data and it’s important that we remove extra complexity and ensure the best data is sent to accomplish the request.

1.  The report should cover the workload that we need to shift-  not a subset or a sample.
   1. 8 days are retained in the AWR by default
   2. Try to get at least a one week report, but if the customer has increased the retention time on their AWR, a larger window report is always beneficial.
   3. If there are month ending or year ending processing, discuss it with the customer team if it needs to be captured or how impacting it would be not to include it.
   4. Don’t simply accept a variety of AWR reports as you would for optimization.  We’re looking at workload calculations, not details on processes.
2. Ensure you have all the databases included to be migrated.
   1. The only time this isn’t required is if the customer wants to just estimate percentages for non-production, but all production databases should be included if the customer has requested a size for the cloud.
3. Run the correct AWR report for the database type:
   1. All command line reports are available from the following directory on the database server, ($ORACLE_HOME/rdbms/admin)
   2. Single instance is the awrrpt.sql
   3. If a RAC database, use the awrgrpt.sql, (global) not the awrrpti.sql, (instance level)
4.  Don’t send multiple reports from short intervals hoping to trap processes-  this doesn’t help with a sizing estimate.  I will reiterate- short intervals are for an optimization exercise, (and there are better ways to trap that kind of information, too.)
5. Name the reports with clear identifiers for the database specialist.
   1. Customer identifier
   2. database name
   3. database use, (prod, dev, test)
   4. snapshots-  beginning and end
6. Use correct report format-  where I used to say I didn’t care, as of 12c, some additional data was added into the HTML version of the report for some Oracle features.  Consider using the HTML report over the TEXT format due to this.

Don’t Boil the Ocean

The goal of the sizing estimate is to size the workload.  Although I do think it’s a good idea to inspect the database usage, (OLTP/OLAP) version, settings and parameters, to take on a full resiliency review of the database if you’re new to this type of work, it takes considerable time to understand how to perform Oracle database optimization.

Collect the sizing information from the AWR and place it into the Excel sizing estimate worksheet first.  Don’t get distracted by the usage or other data and overthink the process.  Verify the entries from the AWR and remember, database version and features can impact where the values reside on the AWR.  IOPS and DB CPU being two values that can be widely dispersed on different reports.  Take the time to verify you have the correct numbers entered.

If a customer sends you multiple reports for a single database, an hour report when a week of data was requested or any other scenario where the result will be inaccurate information for you to work from, let all stakeholders know immediately and truthfully stress that this will make it impossible for you to size effectively.  The biggest mistake when performing a sizing estimate is to assume anything.  I don’t expect the customer or other technical specialists to know how to do the work that I’m doing, but I do expect them to respect my decision when I state I don’t have what I need to perform my tasks accurately to completion.  Keep in mind- inaccurate data for the sizing estimate is just as worthless as the on-prem infrastructure numbers and we’re simply wasting everyone’s time, so getting the right data is worth it.