I don’t believe I have ever asked if you worked for Tim, can’t imagine it
frankly 😉
On Fri, Feb 19, 2021 at 6:50 PM Kellyn Pot'Vin-Gorman <dbakevlar@xxxxxxxxx>
wrote:
JB,
No, his description isn’t and adding the challenge of platform confusion
for Microsoft folks when attempting to request the data and terminology
translation issues, the important focus has to be collecting values that
can provide value vs. exact calculations.
The best DBAs have control issues and to ask them to run a script from
Microsoft on their oracle database becomes a huge roadblock vs. a minor
challenge with the AWR request.
And please stop asking me if I work for Tim and what that’s like, peeps-
Tim and I are on the same team.
Regards,
Kellyn
On Fri, Feb 19, 2021 at 5:33 PM John Beresniewicz <
john.beresniewicz@xxxxxxxxx> wrote:
One could probably write a SQL script to pull the relevant 10 data points
for the last century of AWR and have something decent and small to work
with data-wise.
AWR is pretty much massive overkill for almost any well-defined analysis.
It’s the kitchen sink, something for everyone,
I hope Tim’s description is at least somewhat hyperbolic wrt Oracle DBAs.
JB
On Fri, Feb 19, 2021 at 4:22 PM Tim Gorman <tim.evdbt@xxxxxxxxx> wrote:
To clarify...
The problem is requesting the AWR report from customers.
What we'd *really *prefer to ask of them is "*please provide an AWR
report from the most peak (peakiest?) workload period*". We have
provided a SQL*Plus script to help them find their "busiest" times HERE
<https://github.com/tigormanmsft/az-oracle-sizing/blob/master/busiest_awr.sql>,
which displays the top 5 "busiest" AWR snapshots (by CPU and I/O) currently
in their repository. It should be easy. And fast.
But it is neither easy nor fast.
Only rarely have we ever encountered someone who said, "*sure I can do
that*", and then generate a report as requested.
More often than not, it seems that we *blow their minds *and then we
never get what we ask for. The problem might be the game of *telephone*,
where it seems that person whom we are asking cannot simply forward our
request directly to the DBA team, but instead feels compelled to
reinterpret what we requested, eventually sending a message "*run RAW
report for busy work*" to their DBA team. I kid you not; that one
happened.
If we get past that, then we get the genius DBAs who say, "*What's an
AWR report?*", yes in this day and age, that happens. We include links
to Oracle documentation on AWR in our request email to forestall that
response. It just means that a 30-second operation takes 3 weeks.
If we get past that, then we get the geniuses who argue back that the
AWR report is full of sensitive information, and cannot possibly be shared
outside their organization. We then point out that it is just an HTML file
and they can easily edit out any sensitive information using "sed" or "vi",
but by that point everyone is yelling in alarm and a pointy haired boss
blurts out "*you must absolutely guarantee that there is no sensitive
information in any AWR report, ever, anywhere*" and calls the legal
department.
If we get past that, then we get other geniuses who argue back, "*How
does Microsoft know what an AWR report is?*" and "*What could they
possibly know what an AWR is used for?*"
Then we invariably get the smartest person in the room, who states
loudly and authoritatively "*That's not what an AWR report is used for,
it is used for tuning SQL statements*" and asserts that any conclusions
we reach are garbage.
If we get past all that, then more often than not we get a random AWR
report from a random time period, showing DB Time of 13 seconds while
Elapsed Time is 30 minutes, essentially useless.
As a result, we try make a simplest possible request, with the least
room for misinterpretation, "*Please give us an AWR report for a one
week period*", which is simple, doesn't get too distorted in
transmission, and is FAR better than nothing. If their response is
intelligent, then we'll explain about busy/peak periods and attempt to get
a good AWR report.
Even then things go awry.
Kellyn once received a zip file with 168 one-hour AWR reports. *I KID
YOU NOT*. We had this customer's DBA stating loudly that it was
impossible to run an AWR report on more than one snapshot, hence the 168
reports. He claimed he had been working as a DBA since 1995 or so, and it
has always been that way. Kellyn wouldn't share the name of this guy,
because I would have driven straight to his house and throttled him on his
doorstep, which is bad for business, but oh so good for the soul.
*So, the upshot is: you are absolutely right. The averaging from a
one-week report is awful. But it is way better than nothing, and in too
many cases, nothing is what we would have to work with unless we made the
most simple trouble-free request possible.*
When I started at Microsoft, I started writing a shell script to extract
what we need, but I stopped before ever using it, because few customers
would ever run it, and I'm utterly convinced that I would get phone calls
six months later from customers asserting that their database never again
worked correctly after running the script.
So, if you're working with good people, you should be able to get that
perfect AWR report from the peak workload period.
On 2/19/2021 2:57 PM, Kellyn Pot'Vin-Gorman wrote:
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>*
*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
about.me/dbakevlar
