Database CPU Usage

[Mark Davidson]

Where can I find information on calculating how much CPU an entire database
instance is using (on average)? I have several databases running and I
need to understand how much each is using. For example, if my current
utilization is 40%, what % is each database.

Regards,
Mark

[Mladen Gogala]

I don't think that is possible. I am not sure that CPU accounting from
the Oracle side is quite accurate. There may be some commercial auditing
tools that could do that, but other than that, I believe that this kind
of information simply cannot be computed.

May I ask you why would you need such an information?  I would have a
monitor running during the DB rush hour traffic and see what processes
are consuming the most CPU. I would then simply generalize. You can use
sar -u to see whether you're experiencing a CPU shortage. It looks like
this:

***@umajor:/var/log/sysstat$ sar -u -f sa09
Linux 4.15.0-22-generic (umajor)     10/09/2018 _x86_64_    (8 CPU)

12:00:01 AM     CPU     %user     %nice   %system %iowait    %steal    
%idle
12:10:01 AM     all      5.36      0.00      1.75 19.75      0.00     73.14
12:20:01 AM     all      5.29      0.81      3.19 19.81      0.00     70.90
12:30:01 AM     all      4.83      1.78      5.02 19.14      0.00     69.23
12:40:01 AM     all      5.01      1.78      4.98 18.22      0.00     70.01
12:50:01 AM     all      5.43      1.86      5.11 20.39      0.00     67.20
01:00:01 AM     all      2.63      1.89      4.55 20.83      0.00     70.11
01:10:01 AM     all      5.77      1.77      5.31 18.93      0.00     68.23
01:20:01 AM     all      5.69      1.78      5.26 18.63      0.00     68.65
01:30:01 AM     all      3.79      1.77      4.82 14.84      0.00     74.79
02:30:01 AM     all      0.00      0.00      0.00 0.00      0.00      0.00
02:40:01 AM     all      4.30      0.03      1.03 0.10      0.00     94.54
02:50:01 AM     all      3.89      0.03      0.96 0.06      0.00     95.06
03:00:01 AM     all      4.02      0.06      1.01 0.82      0.00     94.10
03:10:01 AM     all      3.50      0.08      0.94 0.13      0.00     95.34
03:20:01 AM     all      2.13      0.02      0.70 0.10      0.00     97.05
10:20:02 AM     all      0.00      0.00      0.00 0.00      0.00      0.00
10:30:01 AM     all      1.11      0.02      0.52 0.45      0.00     97.90
10:40:01 AM     all      1.02      0.03      0.51 0.06      0.00     98.38
10:50:01 AM     all      1.01      0.02      0.52 0.11      0.00     98.35
11:00:01 AM     all      0.00      0.00      0.00 0.00      0.00      0.00
11:10:01 AM     all      0.70      0.02      0.50 1.05      0.00     97.73
11:20:01 AM     all      0.49      0.02      0.43 0.19      0.00     98.87

11:20:01 AM     CPU     %user     %nice   %system %iowait    %steal    
%idle
11:30:01 AM     all      0.41      0.02      0.39 0.09      0.00     99.09
11:40:01 AM     all      0.43      0.02      0.40 0.22      0.00     98.92
11:50:01 AM     all      0.48      0.02      0.38 0.10      0.00     99.02
12:00:01 PM     all      0.54      0.04      0.40 0.14      0.00     98.88
12:10:01 PM     all      0.36      0.02      0.37 0.24      0.00     99.01
12:20:01 PM     all      1.87      0.05      0.65 0.23      0.00     97.21
12:30:01 PM     all      1.83      0.05      0.61 0.05      0.00     97.46
12:40:01 PM     all      2.84      0.06      0.74 0.05      0.00     96.31
12:50:01 PM     all      1.91      0.02      0.61 0.05      0.00     97.41
01:00:01 PM     all      0.79      0.02      0.46 0.04      0.00     98.69
01:10:01 PM     all      0.62      0.02      0.44 0.05      0.00     98.88
01:20:01 PM     all      0.31      0.02      0.35 0.06      0.00     99.25
01:30:01 PM     all      0.64      0.01      0.41 0.23      0.00     98.71
01:40:01 PM     all      1.44      0.02      0.51 0.08      0.00     97.94
01:50:01 PM     all      1.58      0.02      0.53 0.03      0.00     97.84
02:00:01 PM     all      1.91      0.02      0.64 0.03      0.00     97.40
02:10:01 PM     all      0.05      0.02      0.33 0.18      0.00     99.42
02:20:01 PM     all      0.05      1.01      2.23 0.16      0.00     96.55
02:30:01 PM     all      0.05      1.77      3.77 0.08      0.00     94.33
02:40:01 PM     all      0.05      1.77      3.77 0.18      0.00     94.23
02:50:01 PM     all      0.04      1.73      3.79 0.17      0.00     94.27
03:00:01 PM     all      0.04      1.75      3.80 0.31      0.00     94.10

03:00:01 PM     CPU     %user     %nice   %system %iowait    %steal    
%idle
03:10:01 PM     all      0.05      1.77      3.77 0.17      0.00     94.24
03:20:01 PM     all      0.05      1.77      3.78 0.18      0.00     94.22
03:30:01 PM     all      0.05      1.77      3.76 0.15      0.00     94.27
03:40:01 PM     all      0.05      1.74      3.80 0.19      0.00     94.23
03:50:01 PM     all      0.04      1.77      3.76 0.15      0.00     94.27
04:00:01 PM     all      0.05      1.76      3.78 0.19      0.00     94.22
04:10:01 PM     all      0.05      1.76      3.79 0.18      0.00     94.23
04:20:01 PM     all      0.05      1.76      3.78 0.15      0.00     94.26
04:30:01 PM     all      0.04      1.76      3.78 0.17      0.00     94.25
04:40:01 PM     all      0.04      1.76      3.78 0.16      0.00     94.26
04:50:01 PM     all      0.63      0.84      2.09 0.23      0.00     96.20
05:00:01 PM     all      0.71      0.02      0.42 0.06      0.00     98.78
05:10:01 PM     all      1.13      0.02      1.30 0.60      0.00     96.94
05:20:01 PM     all      0.91      0.02      1.15 0.14      0.00     97.79
05:30:01 PM     all      0.73      0.02      0.52 0.07      0.00     98.66
05:40:01 PM     all      0.78      0.02      1.01 0.08      0.00     98.12
05:50:01 PM     all      1.23      0.09      0.82 0.12      0.00     97.74
06:00:01 PM     all      0.52      0.02      6.62 0.22      0.00     92.62
06:10:01 PM     all      0.33      0.02      4.20 0.11      0.00     95.35
06:20:01 PM     all      0.20      0.02      3.28 0.03      0.00     96.47
06:30:01 PM     all      0.20      0.02      1.29 0.07      0.00     98.42
06:40:01 PM     all      0.18      0.02      1.35 0.03      0.00     98.43

06:40:01 PM     CPU     %user     %nice   %system %iowait    %steal    
%idle
06:50:01 PM     all      0.26      0.02      2.16 0.04      0.00     97.52
07:00:01 PM     all      0.36      0.02      3.15 0.03      0.00     96.43
07:10:01 PM     all      0.42      0.02      6.68 0.18      0.00     92.71
07:20:01 PM     all      0.50      0.02      9.66 0.15      0.00     89.68
07:30:01 PM     all      0.18      0.02      1.30 0.01      0.00     98.49
07:40:01 PM     all      0.16      0.02      1.25 0.02      0.00     98.56
07:50:01 PM     all      1.68      0.02      7.68 0.39      0.00     90.24
08:00:01 PM     all      1.00      0.02      2.29 0.16      0.00     96.53
08:10:01 PM     all      1.07      0.05      1.93 0.22      0.00     96.73
08:20:01 PM     all      0.11      0.04      0.35 0.18      0.00     99.32
08:30:01 PM     all      2.88      0.10      0.85 0.41      0.00     95.76
08:40:01 PM     all      0.58      0.03      0.45 0.01      0.00     98.93
08:50:01 PM     all      0.09      0.30      0.89 0.33      0.00     98.39
09:00:01 PM     all      0.07      1.75      3.80 0.12      0.00     94.26
09:10:01 PM     all      0.06      1.75      3.78 0.12      0.00     94.29
09:20:01 PM     all      0.05      1.78      3.76 0.12      0.00     94.29
09:30:01 PM     all      0.07      1.78      3.78 0.23      0.00     94.15
09:40:01 PM     all      0.06      1.76      3.79 0.12      0.00     94.28
09:50:01 PM     all      0.06      1.75      3.78 0.23      0.00     94.18
10:00:01 PM     all      0.07      1.77      3.77 0.13      0.00     94.26
10:10:01 PM     all      0.06      1.76      3.78 0.12      0.00     94.28
10:20:01 PM     all      0.05      1.76      3.78 0.13      0.00     94.28

10:20:01 PM     CPU     %user     %nice   %system %iowait    %steal    
%idle
10:30:01 PM     all      0.24      1.23      2.73 0.25      0.00     95.55
10:40:01 PM     all      0.62      0.02      0.44 0.52      0.00     98.40
10:50:01 PM     all      1.81      0.03      0.72 0.13      0.00     97.32
11:00:01 PM     all      2.14      0.07      0.73 0.02      0.00     97.04
11:10:01 PM     all      1.54      0.04      0.65 0.02      0.00     97.75
11:20:01 PM     all      2.77      0.04      0.84 0.04      0.00     96.32
11:30:01 PM     all      0.00      0.00      0.00 0.00      0.00      0.00
11:40:01 PM     all      1.37      0.01      0.52 0.51      0.00     97.59
11:50:01 PM     all      0.37      0.02      0.40 0.07      0.00     99.14
Average:        all      0.69      0.02      0.44 0.00      0.00     98.86
***@umajor:/var/log/sysstat$

As you can see,  my idle percent is on average above 95% so my CPU
resources are more than adequate.

Post by Mark Davidson
Where can I find information on calculating how much CPU an entire
database instance is using (on average)?  I have several databases
running and I need to understand how much each is using. For example,
if my current utilization is 40%, what % is each database.
Regards,
Mark

[Sayan Malakshinov]

Hi Mark,

I like to get such information from v$sysmetric:
select * from v$sysmetric where metric_name like '%CPU%'

Post by Mark Davidson
Where can I find information on calculating how much CPU an entire
database instance is using (on average)? I have several databases running
and I need to understand how much each is using. For example, if my current
utilization is 40%, what % is each database.
Regards,
Mark

[Tefft, Michael J]

Assuming there is nothing of consequence running on the server other than the oracle database instances

I would approach this by running an AWR report on each instance (covering the same time period in each one), and check the CPU metrics reported there, such as:
Load Profile=>DB CPU(s) per second
Top 10 Foreground Events by Total Wait time => DB CPU => Total Wait Time (sec)
Wait Classes by Total Wait time => DB CPU => Total Wait Time (sec)

And then use instance-level measures these to weigh out shares of your total CPU utilization.

Mike Tefft

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Mark Davidson
Sent: Wednesday, October 10, 2018 5:30 PM
To: oracle-***@freelists.org
Subject: Database CPU Usage

Where can I find information on calculating how much CPU an entire database instance is using (on average)? I have several databases running and I need to understand how much each is using. For example, if my current utilization is 40%, what % is each database.

Regards,
Mark