AND, not but
There are name tags on the process identifiers that tell you which database the
database running processes are related to. OFTEN, just summing up the resource
consumption columns from ps grouped by those values can quickly identify spikes.
AND, subtracting the sum of those totals from the totals for all processes will
tell you if it even is one or more of the databases as opposed to other system
activity.
Quite often (and one of the many reasons Oracle moved to a container to fan in
support of multiple database from a single set of background services), the
sheer number of processes created by having many independent database on a
single server makes the overall server SLUGGISH compared to its apparent
capacity without additional actual useful user work being done.
Even something simple such as serving new logins as time zones roll around to
local office start times can force the system into wasteful process paging and
allocation if you have too many processes.
Oracle Real World had a brilliant set of videos about this (regardless of the
container issue). Simply limiting the overall total to something reasonable CAN
make the completion time of something that has to wait to start sooner, because
most things can then each run in a lot less elapsed time.
Determining whether you have too many concurrent processes running in aggregate
on the box is probably job one.
Oracle has had instance/database name tags on its processes since at least V6,
November 1988, and there is some utility on every OS I can think of that can be
used to feed something to do the first order math on this for you. Probably you
can find something free that’s good enough to answer the “which database(s) are
currently heavy contributors to resource consumption.”
Good luck.
PS: Thanks Mladen for updating my antique V$ reference to something more
recent. As with all V$ references, reviewing them for the actual current
release being used is clearly required.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Sunday, March 17, 2024 10:35 AM
To: Krishnaprasad Yadav
Cc: mwf@xxxxxxxx; Oracle L
Subject: Re: High CPU utilization
On Sun, 2024-03-17 at 14:55 +0530, Krishnaprasad Yadav wrote:
Dear All ,
Thanks for your response ,
Currently we have non-cdb architecture and there are independent DB instances
running on a single server , hence it becomes difficult to identify which one
is causing the high CPU Usage .
Please suggest the same .
Regards,
Krishna
Krishna, this is an OS problem, not an Oracle one. You have several groups of
processes/threads (depending on the OS) and you need to compare them with
respect to resource usage. Generally speaking, that is done through OS
auditing. Depending on your OS, you may need to use either open source auditing
package or a commercial one. You may wait for Oracle 27c, which will
incorporate artificial intelligence and the mind reading abilities, and be able
to write your queries for you. Oracle is developing a version for HAL 9000.
https://www.youtube.com/watch?v=UwCFY6pmaYY
That was an example of ORA-600.
--
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com
