Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vinay.

Asked: August 02, 2016 - 12:27 pm UTC

Last updated: October 19, 2023 - 12:44 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi,
When i am trying to analyze my database performance through AWR reports, my findings are

Wait Wait Time
Total Avg(ms) %DB Time
DB CPU 2,731,581.78 75.49
* System I/O db file parallel write 882,568.74 6.31 24.39
* User I/O cell single block physical read 253,037.88 0.53 6.99
* System I/O RMAN backup & recovery I/O 146,138.27 27.23 10.96

When i check my CPU utilization on the server it never crossed 50% Utilization overall.

My system is Exadata machine with x86_64 x86_64 x86_64 GNU/Linux

Database version is 12.1.0.2

Every AWR report gives me the same findings.

Question:
With CPU being 50% free and top wait event is CPU, does it mean that my system is not properly tuned to use CPU?

CPU Cores 24
CPU Threads 48


Am i supposed to be looking at different metric that relates to this?

and Chris said...

Ummm, maybe?

Thing is, you can't look at an AWR report and say whether your database is "properly tuned".

You can easily write routines that chew CPU. Such as calculating square roots over and over. Submit a series of jobs to do this. Your CPU usage will go up and "DB CPU" (should) be your top event. So you'll look at your AWR report and see high CPU utilization and this as the top event.

Does this mean your system is "tuned"?

Of course not!

Obviously you wouldn't do this in the real world. But without context you don't know whether the CPU using activities are required or not. There might be lots of code doing unnecessary work. There might be none. I can't tell you that.

The thing that really matters is user response time. How long do people actually sit, waiting for the application to respond. To find this you need to profile the individual calls. Some might have 99% of their "waits" as CPU. Others 1%.

Look at user response times, profile and take appropriate action if necessary. If your users are happy with the situation, there's nothing for you to do!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vinay Kumar Aluri, August 03, 2016 - 9:46 am UTC


AWR for DB constrained by CPU or not??

Narendra, October 05, 2023 - 10:34 pm UTC

Hello Connor/Chris,

Apologies in advance if I am not providing enough information for you to comment on but I have been (literally) reeling from shock when an Oracle Engineer today claimed that our database workload is constrained by CPU and we need to add more CPU to get better performance for application batch job that is taking 6 times longer on oracle 19c database running on 14 cores (but shared by other databases too) as compared to previous 12.1.0.2 database running on 8 cores. (looks like can't paste screenshots here)

I am confused about 2 things
1. Why would database be deemed as CPU-bound when host is reporting over 32% idle time? I have monitored host CPU usage while the job runs and it always remains around 80% but less than 85%

2. Under what circumstances, operating system will report high system load (24) but average CPU utilization (67%)?

Below are the stats from node 1, where almost all of the batch job executes. The OS is 64-bit Linux 7.9.

Elapsed Time => 179.92 minutes
DB Time => 466.73 minutes

Foreground Wait Classes

DB CPU => 20383.81 seconds

The "OS Statistics By Instance" section from 2-node RAC 19c database global report is showing below numbers:
Instance => 1
No. of CPUs => 14
No. of cores => 14
Load Begin => 24.86
Load End => 20.89
%Busy CPU => 67.19
%User CPU => 38.09
%Sys CPU => 22.74
%WIO CPU => 0.13
Idle CPU => 32.81



Chris Saxon
October 19, 2023 - 12:44 pm UTC

Hard to say exactly why the engineer believes the system is CPU bound. If you doubt their analysis then ask them for an explanation of precisely why they think this. Put the questions you've sent us to them.

1. It could be that the batch process itself is CPU bound, not the database as a whole. That said, unless the process uses (or can be easily changed to use) parallel processing, adding more CPUs is unlikely to help.

2. What other work is the system doing? e.g. is there lots of I/O that could account for this extra work?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.