Step1: Find the process that are consuming high CPU on DB Node.
oracledb=>prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
26477 oracledb 2407M 2369M cpu2 0 0 2:59:40 13% oracle/39
1958 oracledb 2393M 2355M cpu6 0 0 1:54:27 12% oracle/1
2124 oracledb 2393M 2355M cpu2 0 0 1:53:35 12% oracle/1
2202 oracledb 2393M 2355M cpu5 0 0 1:52:07 12% oracle/1
Step2: Identify the PROCESS(Apps Node) from DB.
SQL>select s.sid, s.serial#, s.process, s.program, s.module
from v$session s, v$process p
where s.paddr =p.addr and p.spid in ('26477','2124','1958','2202');
SID SERIAL# PROCESS PROGRAM MODULE
---------- ---------- ------------ ------------------------------------------------ --------------------
93 35457 17991
Step3:On the APPLICATION Server, grep for the PROCESS:
apps=>ps -ef|grep 17991
apps 27982 25127 0 14:14:24 pts/2 0:00 grep 17991
apps 17991 9806 0 10:40:02 ? 0:02 ar60run P_CONC_REQUEST_ID=13318110 P_CORP='055' P_COMPANY='004' P_AS_OF_DATE='1
Step4:Get the Concurrent Request details
select /*+ CHOOSE*/
'Node Name..............................: ' || q.node_name || chr(10) ||
'Req id.................................: ' || Request_Id || chr(10) ||
'Requestor..............................: ' || User_Name || chr(10) ||
'Manager................................: ' || Q.User_Concurrent_Queue_Name || chr(10) ||
'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
'W', 'Paused', 'X','Terminated', status_code) || chr(10) ||
'Phase code.............................: ' || decode(phase_code, 'C', 'Completed',
'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code) || chr(10) ||
'Priority...............................: ' || Fcr.priority || chr(10) ||
'Program................................: ' || Fcp.User_Concurrent_Program_Name || chr(10) ||
'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
'Avg execution time in 30 days.........: ' || trunc(AVG_TIME,2) ||' min' || chr(10) ||
'Max execution time in 30 days..........: ' || trunc(MAX_TIME,2) ||' min' || chr(10) ||
'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min' || chr(10) ||
'Number of executions in last 30 days...: ' ||occurance || chr(10) ||
'ClientPID..............................: ' || Fcr.OS_PROCESS_ID || chr(10) ||
'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID || chr(10) ||
'Arguments passed to the program .......: ' ||Fcr.argument_text
from apps.Fnd_Concurrent_Requests Fcr,
apps.Fnd_Concurrent_Programs_vl Fcp,
apps.Fnd_Oracle_Userid O,
apps.Fnd_Concurrent_Processes P,
apps.Fnd_Concurrent_Queues_vl Q,
apps.Fnd_User,(select
concurrent_program_id
,count(concurrent_program_id) occurance
,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
, avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
from apps.fnd_concurrent_requests
where status_code='C' and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id
having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
where Controlling_Manager = Concurrent_Process_ID
and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
and( Fcr.Program_Application_Id=Fcp.Application_Id
and Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
and Requested_By = User_Id
and Phase_Code = 'R' and status_code in ('R','T')
--adding joins with new
and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
Order By Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;
Enter value for reqid: 13318110
old 40: and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
new 40: and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('13318110')
Node Name..............................: server02
Req id.................................: 13318110
Requestor..............................: USER10
Manager................................: Standard Manager
Status code............................: Normal
Phase code.............................: Running
Priority...............................: 50
Program................................: Some Long Running Program
Time so far ...........................: 218.63 min
Avg execution time in 30 days.........: min
Max execution time in 30 days..........: min
Fastest execution Time in 30 days......: min
Number of executions in last 30 days...:
ClientPID..............................:
ServerPID..............................:
Arguments passed to the program .......: , , , , 055, 004, , , , , 10-JUL-2014, , Merchant Number, , 71, Y, N
oracledb=>prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
26477 oracledb 2407M 2369M cpu2 0 0 2:59:40 13% oracle/39
1958 oracledb 2393M 2355M cpu6 0 0 1:54:27 12% oracle/1
2124 oracledb 2393M 2355M cpu2 0 0 1:53:35 12% oracle/1
2202 oracledb 2393M 2355M cpu5 0 0 1:52:07 12% oracle/1
Step2: Identify the PROCESS(Apps Node) from DB.
SQL>select s.sid, s.serial#, s.process, s.program, s.module
from v$session s, v$process p
where s.paddr =p.addr and p.spid in ('26477','2124','1958','2202');
SID SERIAL# PROCESS PROGRAM MODULE
---------- ---------- ------------ ------------------------------------------------ --------------------
93 35457 17991
Step3:On the APPLICATION Server, grep for the PROCESS:
apps=>ps -ef|grep 17991
apps 27982 25127 0 14:14:24 pts/2 0:00 grep 17991
apps 17991 9806 0 10:40:02 ? 0:02 ar60run P_CONC_REQUEST_ID=13318110 P_CORP='055' P_COMPANY='004' P_AS_OF_DATE='1
Step4:Get the Concurrent Request details
select /*+ CHOOSE*/
'Node Name..............................: ' || q.node_name || chr(10) ||
'Req id.................................: ' || Request_Id || chr(10) ||
'Requestor..............................: ' || User_Name || chr(10) ||
'Manager................................: ' || Q.User_Concurrent_Queue_Name || chr(10) ||
'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
'W', 'Paused', 'X','Terminated', status_code) || chr(10) ||
'Phase code.............................: ' || decode(phase_code, 'C', 'Completed',
'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code) || chr(10) ||
'Priority...............................: ' || Fcr.priority || chr(10) ||
'Program................................: ' || Fcp.User_Concurrent_Program_Name || chr(10) ||
'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
'Avg execution time in 30 days.........: ' || trunc(AVG_TIME,2) ||' min' || chr(10) ||
'Max execution time in 30 days..........: ' || trunc(MAX_TIME,2) ||' min' || chr(10) ||
'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min' || chr(10) ||
'Number of executions in last 30 days...: ' ||occurance || chr(10) ||
'ClientPID..............................: ' || Fcr.OS_PROCESS_ID || chr(10) ||
'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID || chr(10) ||
'Arguments passed to the program .......: ' ||Fcr.argument_text
from apps.Fnd_Concurrent_Requests Fcr,
apps.Fnd_Concurrent_Programs_vl Fcp,
apps.Fnd_Oracle_Userid O,
apps.Fnd_Concurrent_Processes P,
apps.Fnd_Concurrent_Queues_vl Q,
apps.Fnd_User,(select
concurrent_program_id
,count(concurrent_program_id) occurance
,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
, avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
from apps.fnd_concurrent_requests
where status_code='C' and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id
having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
where Controlling_Manager = Concurrent_Process_ID
and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
and( Fcr.Program_Application_Id=Fcp.Application_Id
and Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
and Requested_By = User_Id
and Phase_Code = 'R' and status_code in ('R','T')
--adding joins with new
and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
Order By Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;
Enter value for reqid: 13318110
old 40: and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
new 40: and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('13318110')
Node Name..............................: server02
Req id.................................: 13318110
Requestor..............................: USER10
Manager................................: Standard Manager
Status code............................: Normal
Phase code.............................: Running
Priority...............................: 50
Program................................: Some Long Running Program
Time so far ...........................: 218.63 min
Avg execution time in 30 days.........: min
Max execution time in 30 days..........: min
Fastest execution Time in 30 days......: min
Number of executions in last 30 days...:
ClientPID..............................:
ServerPID..............................:
Arguments passed to the program .......: , , , , 055, 004, , , , , 10-JUL-2014, , Merchant Number, , 71, Y, N
