Calculating % Busy CPU on Oracle Databases When Not Present in AWR Report
*Previously posted on the Microsoft Tech Community Data Architecture Blog
For Oracle 19c AWR reports for Multi-tenant DBs on Exadata reports, the %Busy, CPU, Cores, memory and other data isn’t present. These are vital data points many of us use to determine vCPU calculations in sizing.
Never fear- we still have the data provided in the AWR report we received from the customer to fill in these sections or at least calculate the value.
Memory, CPU, Cores
Most of the missing data is in the Exadata section of the report:
How Much CPU is Available?
Now, to create gather a % Busy for CPU usage from what we have in the AWR, we’ll need the following values:
- CPU Count
- Elapsed Time
Using this info, we can create the following calculation to discover the amount of CPU available on the RAC node in the Exadata:
CPU Count * Elapsed Time * 60 seconds = CPU_VAL
So, for this example, this was an AWR on a 32 CPU machine for 300 minutes.
32 * 300 * 60 seconds = 576000 seconds of CPU available.
Suddenly, the DB CPUs value doesn’t look so huge anymore, does it?
How Busy is the CPU for Oracle?
A disclaimer needs to be added as there are some holes in the AWR capture for CPU busy, especially around “wait for CPU”, but to come up with a % busy CPU, we have the numbers we need to create this value now that’s missing in the AWR.
(CPU_VAL – DB CPUs)/(DB CPUs) = % Busy CPU
Now we have our % Busy, which for this node of the RAC database on the Exadata was 24.65, or 25%.
If you’re lucky enough to have access to the customer’s database server, you could also just use this valuable post from my peep, Kyle Hailey and run a query to collect the info. Kyle also goes into the challenges and holes in the AWR data that if you want to dig in deep vs. the estimates we require for sizing, would require ASH data to fill in.
col metric_name for a25 col metric_unit for a25 SELECT metric_name, value,_metric_unit FROM v$sysmetric WHERE metric_name like ‘%Host CPU%’;
If you run into this challenge with an Oracle 19c sizing estimate, hopefully this post will help keep you from pulling your hair out!
