Re: High CPU utilization

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

On Sun, 17 Mar 2024 at 00:44, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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
> multi-tenant 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