Calculating % Busy CPU on Oracle Databases When Not Present in AWR Report

Published Jul 01 2021 02:21 PM 1,058 Views
Microsoft

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:

kegorman_0-1625174189164.png

 

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!

 

Related:  How to size an Oracle Workload in Azure.

%3CLINGO-SUB%20id%3D%22lingo-sub-2508908%22%20slang%3D%22en-US%22%3ECalculating%20%25%20Busy%20CPU%20on%20Oracle%20Databases%20When%20Not%20Present%20in%20AWR%20Report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508908%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20Oracle%2019c%20AWR%20reports%20for%20Multi-tenant%20DBs%20on%20Exadata%20reports%2C%20the%20%25Busy%2C%20CPU%2C%20Cores%2C%20memory%20and%20other%20data%20isn%E2%80%99t%20present.%26nbsp%3B%20These%20are%20vital%20data%20points%20many%20of%20us%20use%20to%20determine%20vCPU%20calculations%20in%20sizing.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENever%20fear-%20we%20still%20have%20the%20data%20provided%20in%20the%20AWR%20report%20we%20received%20from%20the%20customer%20to%20fill%20in%20these%20sections%20or%20at%20least%20calculate%20the%20value.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-286865464%22%20id%3D%22toc-hId-286865495%22%3E%3CSTRONG%3EMemory%2C%20CPU%2C%20Cores%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3EMost%20of%20the%20missing%20data%20is%20in%20the%20Exadata%20section%20of%20the%20report%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22kegorman_0-1625174189164.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293027iC28BF7B2404FB87E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22kegorman_0-1625174189164.png%22%20alt%3D%22kegorman_0-1625174189164.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--1520588999%22%20id%3D%22toc-hId--1520588968%22%3E%3CSTRONG%3EHow%20Much%20CPU%20is%20Available%3F%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3ENow%2C%20to%20create%20gather%20a%20%25%20Busy%20for%20CPU%20usage%20from%20what%20we%20have%20in%20the%20AWR%2C%20we%E2%80%99ll%20need%20the%20following%20values%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ECPU%20Count%3C%2FLI%3E%0A%3CLI%3EElapsed%20Time%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EUsing%20this%20info%2C%20we%20can%20create%20the%20following%20calculation%20to%20discover%20the%20amount%20of%20CPU%20available%20on%20the%20RAC%20node%20in%20the%20Exadata%3A%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%3ECPU%20Count%20*%20Elapsed%20Time%20*%2060%20seconds%20%3D%20CPU_VAL%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ESo%2C%20for%20this%20example%2C%20this%20was%20an%20AWR%20on%20a%2032%20CPU%20machine%20for%20300%20minutes.%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%3E32%20*%20300%20*%2060%20seconds%20%3D%20576000%20seconds%20of%20CPU%20available.%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ESuddenly%2C%20the%20DB%20CPUs%20value%20doesn%E2%80%99t%20look%20so%20huge%20anymore%2C%20does%20it%3F%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-966923834%22%20id%3D%22toc-hId-966923865%22%3E%3CSTRONG%3EHow%20Busy%20is%20the%20CPU%20for%20Oracle%3F%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3EA%20disclaimer%20needs%20to%20be%20added%20as%20there%20are%20some%20holes%20in%20the%20AWR%20capture%20for%20CPU%20busy%2C%20especially%20around%20%E2%80%9Cwait%20for%20CPU%E2%80%9D%2C%20but%20to%20come%20up%20with%20a%20%25%20busy%20CPU%2C%20we%20have%20the%20numbers%20we%20need%20to%20create%20this%20value%20now%20that%E2%80%99s%20missing%20in%20the%20AWR.%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%3E(CPU_VAL%20%E2%80%93%20DB%20CPUs)%2F(DB%20CPUs)%20%3D%20%25%20Busy%20CPU%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ENow%20we%20have%20our%20%25%20Busy%2C%20which%20for%20this%20node%20of%20the%20RAC%20database%20on%20the%20Exadata%20was%2024.65%2C%20or%2025%25.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%E2%80%99re%20lucky%20enough%20to%20have%20access%20to%20the%20customer%E2%80%99s%20database%20server%2C%20you%20could%20also%20just%20use%20this%20valuable%20post%20from%20my%20peep%2C%20%3CA%20href%3D%22http%3A%2F%2Fdboptimizer.com%2F2011%2F07%2F21%2Foracle-cpu-time%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EKyle%20Hailey%3C%2FA%3E%20and%20run%20a%20query%20to%20collect%20the%20info.%26nbsp%3B%20Kyle%20also%20goes%20into%20the%20challenges%20and%20holes%20in%20the%20AWR%20data%20that%20if%20you%20want%20to%20dig%20in%20deep%20vs.%20the%20estimates%20we%20require%20for%20sizing%2C%20would%20require%20ASH%20data%20to%20fill%20in.%3C%2FP%3E%0A%3CPRE%3Ecol%20metric_name%20for%20a25%3CBR%20%2F%3Ecol%20metric_unit%20for%20a25%3CBR%20%2F%3ESELECT%20metric_name%2C%20value%2C_metric_unit%3CBR%20%2F%3EFROM%20v%24sysmetric%3CBR%20%2F%3EWHERE%20metric_name%20like%20%E2%80%98%25Host%20CPU%25%E2%80%99%3B%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20run%20into%20this%20challenge%20with%20an%20Oracle%2019c%20sizing%20estimate%2C%20hopefully%20this%20post%20will%20help%20keep%20you%20from%20pulling%20your%20hair%20out!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERelated%3A%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fdata-architecture-blog%2Festimate-tool-for-sizing-oracle-workloads-to-azure-iaas-vms%2Fba-p%2F1427183%22%20target%3D%22_self%22%3EHow%20to%20size%20an%20Oracle%20Workload%20in%20Azure.%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2508908%22%20slang%3D%22en-US%22%3E%3CP%3EThere's%20been%20another%20Oracle%20AWR%20report%20change%20and%20this%20time%20its%20for%20Oracle%20Exadata%20with%20multitenant.%26nbsp%3B%20How%20do%20you%20capture%20the%20data%20you%20need%20to%20size%20it%20for%20Azure%3F%26nbsp%3B%20We%20got%20you%20covered!%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2508908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EData%20Architecture%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Jul 01 2021 02:21 PM
Updated by: