Re: High CPU utilization

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 16 Mar 2024 15:14:52 -0400
Message-ID: <012d5e24b12546341cbea51c2b838d9685df871b.camel_at_gmail.com>

On Sat, 2024-03-16 at 10:24 -0400, Mark W. Farnham wrote:
> V$SYS_TIME_MODEL and V$SYSMETRIC_HISTORY come to mind. The GV$ versions
> are for all the instances in a given database, V$ the individual
> instances.
>  
> Do you have multiple independent databases running or are these PDBs
> within a single container on each node?
>  
> That’s from the database viewpoint keeping track of itself with sampling.
>  
> From ps output with the right flags (assuming you have system authority
> to dump those out) and if your database backend names are reasonably
> parseable,
> you can pipe that to awk or perl (or <fill in latest version of analysis
> candy tool ?python maybe?>) and sum up the cpu numbers.
>  
> All this is much easier when decently close is the desired answer rather
> than tooling to split hairs for repeated regression runs to figure out
> what is absolutely best for something.
>  
> It seems to me you’re just looking for run-away detection for cpu storms.
> Time sharing usage control can become arbitrarily complex, but if you’re
> just looking for storms rather than precision for back billing remember
> that as you build your watcher. Also remember to subtract the sum of the
> per instance totals from the total for the box. When everything seems
> “slow” there is a non-zero possibility that the OS non-database jobs
> and/or things like backups and routine maintenance are sucking up the
> juice.
>  
> By the way, do your clients run on the database server?
>  
> When someone starts asking this question putting some sort of job
> scheduler in place is usually the result.
>  
> Probably someone has something reasonably canned up for various OS
> releases and Oracle versions and so forth. Some may be free and some may
> be for sale or come with paid consulting support.
>  
> Good luck,
>  
> mwf
>  
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org] On Behalf Of Krishnaprasad Yadav
> Sent: Saturday, March 16, 2024 5:41 AM
> To: Oracle L
> Subject: High CPU utilization
>  
> Hi Guru's,
>  
> Want to know  about which database is utilizing the most CPU.We receive 
> incidents of high CPU utilization of servers which have around 8-10
> database instances running ,it becomes very difficult to find out which
> DB instances are causing high CPU .
>  
>  
> It will be very full if we get some light on this , unfortunately we
> don't have any OS watcher details available  , is their is any way to get
> details if issue reproduce using any OS command of ps by grouping
> instance name and even in case we want to do some postmatterm stuff any
> way from DB to figure out who was contributing it more ?
>  
> Regards,
> krishna
>  

Hi Mark, it looks like V$SYSMETRIC_HISTORY is no longer used in multitenant  databases.

SQL> show parameter pack

NAME                           TYPE   VALUE             
------------------------------ ------ ----------------- 

control_management_pack_access string DIAGNOSTIC+TUNING SQL> select distinct metric_name from V$SYSMETRIC_HISTORY;

no rows selected
Elapsed: 00:00:00.017
SQL> select count(*) from V$SYSMETRIC_HISTORY;

   COUNT(*)

---

          0

Elapsed: 00:00:00.032

There is, however, V$CON_SYSMETRIC_HISTORY, which looks exactly the same as our old friend, but with the connection information:

SQL> select count(*) from v$con_sysmetric_history;

   COUNT(*)

---

       3355

Elapsed: 00:00:00.085

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Mar 16 2024 - 20:14:52 CET