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@xxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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
