Re: High CPU utilization

On Sat, 2024-03-16 at 15:10 +0530, Krishnaprasad Yadav wrote:
> 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
>

Yadav, you will have to use DBMS_RESOURCE_MANAGER and manually limit each PDB to certain amount of CPU. One of the issues with the pluggable databases is the infamous "noisy neighbor" problem, which is notoriously hard to diagnose. That is precisely what you describe: someone is using all the resources and you don't know who that may be. One of the ways to quickly find out is to generate a AWR report on the PDB level and go straight to the SQL by CPU section. Of course, CPU is not the only resource that can be hogged by a PDB. Memory and IO can also be problematic. BTW, I find shared server configurations better for managing resources in a multitenant  databases precisely because it makes it much easier to maintain resource use. If you run out of a resource, you will most likely have to restart your instance, but that is what is expected during a major reconfiguration. I've had that issue before. People were adding applications that would require hundreds of new connections and had a gall to say that "nothing has changed". Also, every PDB should have its application administrator, responsible for the space, new users and PDB administration, so that the DBA knows who is to blame when something happens. 

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


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