Monday, January 14, 2019

Check maximum number of processes & processes used by schema

To check maximum number of processes & processes used  by schema :

select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes','sessions');

-- processes/sessions are getting is by running max utilization is the high level ::

SELECT inst_id,resource_name, current_utilization, max_utilization, limit_value FROM gv$resource_limit
WHERE resource_name in ('processes','sessions');



--See which machines/schemas are causing any process exhaustion ::

--number of processes using by schema or machine


select distinct
        s.inst_id,
        s.username,
        s.machine,
        count(*)
from    gv$session s,
        gv$process p
where   s.paddr       =  p.addr
and     s.inst_id     =  p.inst_id
GROUP BY         s.inst_id,
        s.username,
        s.machine
ORDER BY 4 desc;