Metrics Process Limit Usage percentage (%) is critical or warning in OEM or Cloud Control 12c or Cloud Control 13c
Please run the below queries to trace details during Metric alert "Process Limit % is >= 75" in OEM or Cloud Control 12c or Cloud Control 13c
To check the Metrics Process Limit Usage (%):
*****************************************************
set lines 300
col INITIAL_ALLOCATION for a17
col LIMIT_VALUE for a12
select resource_name,current_utilization,max_utilization,
INITIAL_ALLOCATION,LIMIT_VALUE,((current_utilization*100)/(INITIAL_ALLOCATION)) as "Process limit %"
from v$resource_limit
where resource_name in ('processes');
The below query will give you all connected sessions (ACTIVE and INACTIVE) to instance:
*******************************************************************************************************
set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a20
col MODULE for a18
col OSUSER for a07
col MACHINE for a20
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from v$session s, v$process p where s.paddr=p.addr;
The below query will give you the sessions connected from particular machine :
******************************************************************************************
set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 9999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a13
col PROGRAM for a16
col MODULE for a16
col OSUSER for a08
col MACHINE for a24
col EVENT for a27
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from v$session s, v$process p where s.paddr=p.addr and s.machine like '&MACHINE'
and s.status in('&status');
The above query will ask you to enter the status (type INACTIVE and press ENTER);
*************************************************************************************************
Enter value for status: INACTIVE
To run the previous query again for Active sessions, type the below
****************************************************************************
SYS@OPRP1 > /
Then it will ask you to enter the status (type ACTIVE and press ENTER):
**********************************************************************************
Enter value for status: ACTIVE
The below query will give you count of sessions connected from each machine:
******************************************************************************************
set lines 500
set pages 1000
col MACHINE for a35
col OSUSER for a15
col USERNAME for a19
break on report
compute sum of count(*) on report
select s.machine,s.status,s.osuser,s.username,s.SQL_ID,count(*)
from v$session s, v$process p where s.paddr=p.addr
group by s.machine,s.status,s.osuser,s.username,s.SQL_ID
order by 6 desc;
To check the Metrics Process Limit Usage (%):
*****************************************************
set lines 300
col INITIAL_ALLOCATION for a17
col LIMIT_VALUE for a12
select resource_name,current_utilization,max_utilization,
INITIAL_ALLOCATION,LIMIT_VALUE,((current_utilization*100)/(INITIAL_ALLOCATION)) as "Process limit %"
from v$resource_limit
where resource_name in ('processes');
The below query will give you all connected sessions (ACTIVE and INACTIVE) to instance:
*******************************************************************************************************
set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a20
col MODULE for a18
col OSUSER for a07
col MACHINE for a20
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from v$session s, v$process p where s.paddr=p.addr;
The below query will give you the sessions connected from particular machine :
******************************************************************************************
set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 9999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a13
col PROGRAM for a16
col MODULE for a16
col OSUSER for a08
col MACHINE for a24
col EVENT for a27
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from v$session s, v$process p where s.paddr=p.addr and s.machine like '&MACHINE'
and s.status in('&status');
The above query will ask you to enter the status (type INACTIVE and press ENTER);
*************************************************************************************************
Enter value for status: INACTIVE
To run the previous query again for Active sessions, type the below
****************************************************************************
SYS@OPRP1 > /
Then it will ask you to enter the status (type ACTIVE and press ENTER):
**********************************************************************************
Enter value for status: ACTIVE
The below query will give you count of sessions connected from each machine:
******************************************************************************************
set lines 500
set pages 1000
col MACHINE for a35
col OSUSER for a15
col USERNAME for a19
break on report
compute sum of count(*) on report
select s.machine,s.status,s.osuser,s.username,s.SQL_ID,count(*)
from v$session s, v$process p where s.paddr=p.addr
group by s.machine,s.status,s.osuser,s.username,s.SQL_ID
order by 6 desc;
Comments
Post a Comment