Re: Database CPU Usage
Date: Wed, 10 Oct 2018 18:11:20 -0400
Message-ID: <e0c43b93-9b7a-5085-4734-95f6d2304fec_at_gmail.com>
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:
mgogala_at_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.14Average: all 0.69 0.02 0.44 0.00 0.00 98.86 mgogala_at_umajor:/var/log/sysstat$
As you can see, my idle percent is on average above 95% so my CPU resources are more than adequate.
On 10/10/2018 05:30 PM, Mark Davidson wrote:
> 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 Database Consultant Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 11 2018 - 00:11:20 CEST