Hello Graham,
You're hitting on a topic that we discuss on a regular basis- we take this
into consideration and have "fudge factors" built into our worksheet that
we adjust depending on the workload. We also have scripts to pull the top
five peak workloads in a given AWR retention, but I found that they often
demonstrated a peak for one type of resource and the week averages with the
fudge factors were more accurate when considering the choice in VM series
and size, along with storage solution. You can see my quick post I did on
this here: Why a One-Week Report for AWR Sizing in Azure (dbakevlar.com)
<https://dbakevlar.com/2020/10/why-a-one-week-report-for-awr-sizing-in-azure/>
Thank you,
*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
about.me/dbakevlar
On Fri, Feb 19, 2021 at 2:29 PM Graham Wood <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hi Kellyn,
Are you saying take a single AWR report for a one week period and use that
for sizing? Given the dangers of averaging , a topic that many on this list
have been vocal on over the years, I would expect that to be problematic in
very many cases. Almost all systems have some temporal workload variations
such as day/night, weekday/weekend, and month end, even in these days of
everything online 24x7. I can imagine that the one week average will always
lead to a very lowball number, even after applying the recommended CPU
fudge factors. But then, if the idea is to 'size to win the business'...
A next level dig into the AWR data should be able to greatly increase the
accuracy of the estimate, even if it was only used to identify the temporal
nature of the workload of each system.
Cheers, Graham
On Wednesday, February 17, 2021, 01:20:57 PM PST, Kellyn Pot'Vin-Gorman <
dbakevlar@xxxxxxxxx> wrote:
Hey Cary,
I know I do this for Azure and we're about to embark on writing this up as
part of a book for Apress, but, at a high level, there's a process and
here's the overall reasoning behind why we do what we do:
1. You realize that few are very good at sizing out machines for
database workloads as they think they are.
2. Database workload resource needs change over time.
3. Existing hardware is purchased to serve the datacenter for an extended
period of time.
Due to this, the process for a datacenter move and license review, (we
suffer from Oracle's policy for 2:1 penalty on CPU:vCPU with
hyperthreading) is-
For each database environment, we collect a single AWR report for a one
week window.
1. The data points for CPU, memory, IOPs, %busy CPU, CPU/Core, SGA/PGA is
all documented for each production database- Don't do this for non-prod.
We take the averages and any missing data for Exadata into consideration
using a "fudge factor" table and translate it to what the workload would
require to run NOW.
2. This is listed out in a spreadsheet, which can easily compare what is
required vs. what is allocated in the hardware.
3. Values are assigned for a percentage of resources from the production
workload that they wish to grant to stage, test, dev, etc.
4. Consolidate workloads for databases that make sense.
5. Consider the amount of resource usage that can be saved if a more
modern backup utility can be used than RMAN.
Most customers discover they aren't using the cores that they thought they
were and can save money on Oracle licensing.
Hope this helps and I do have a worksheet for Azure that could be easily
updated to just do straight CPU instead of Azure calculations if
interested. It's what we use here to do the work.
*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
about.me/dbakevlar
On Wed, Feb 17, 2021 at 12:45 PM Cary Millsap <cary.millsap@xxxxxxxxxxxx>
wrote:
Hi everybody, from freezing cold Texas.
I have a friend who's embarking on a big project to reduce the number of
servers and licenses his company has to pay for and maintain (presumably
using VMs and PDBs and all that). Do you know of any good sources for
studying up on how to do a good job on a project like this?
Thank you,
Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>*
and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition
<https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*
