RE: High CPU utilization

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Sunday, March 17, 2024 10:35 AM
To: Krishnaprasad Yadav
Cc: mwf_at_rsiz.com; 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  

--

http://www.freelists.org/webpage/oracle-l Received on Sun Mar 17 2024 - 17:17:06 CET