How to troubleshoot long running concurrent request in R12.2
Normally we are getting concurrent program running longer and concurrent
request not picking jobs, etc. those are very frequent issue we expected
from development team.
Collect below basis level of information to development team
1 Oracle seeded program/Custom program?
2 How much time it used earlier?
3 Is there any recent code change done in concurrent program?
4 Is this program fetching higher data compare to last run?
5 Does this job running any specific time/ It can be run any time?
6 Does this job fetching data using DB link?
7 Does the problem happen on both the test and production instance?
Once asked above questions to development team in meanwhile we need to
start basic sanity check in our environment.
1 Verify the status of the concurrent program
select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from
apps.fnd_concurrent_requests where request_id=’1234567’;
2 Find which concurrent manger ran a specific concurrent request
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes
a,fnd_concurrent_queues_vl b, fnd_concurrent_requests c where
a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and a.CONCURRENT_PROCESS_ID =
c.controlling_manager and c.request_id ='856272604';
3 Verify the actual and target of the CM
4 Verify the concurrent manager status
5 Find SID for the concurrent request use below query
SELECT A.REQUEST_ID, D.SID, D.sql_id, D.SERIAL# , C.SPID
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
gV$PROCESS C,
gV$SESSION D
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND A.REQUEST_ID = &REQUEST_ID
AND A.PHASE_CODE = 'R';
6 Find any blocking session for concurrent request use below query and
verify concurrent program SID
select (select username from v$session where sid=a.sid) blocker, a.sid, '
is blocking ' "IS BLOCKING", (select username from v$session where
sid=b.sid) blockee,b.sid from v$lock a, v$lock b where a.block = 1 and
b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
If any blocking session there use below query to find the concurrent
request id
SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid FROM
apps.fnd_concurrent_requests a, gv$process c, gv$session s WHERE s.sid in
('&sid') AND s.paddr = c.addr
AND a.oracle_process_id = c.spid AND a.phase_code = UPPER ('R');
7 Find client identifier and module, action for concurrent request
8 Then check with module owner with the concurrent request
sid,serial#,USERNAME,CLIENT_IDENTIFIER,status,LAST_CALL_ET/60,SQL_HASH_VALUE,module,action,program
from v$session where sid='&n';
9 Find which sql query is running
select sql_text from v$sqltext where sql_id = 'a1x81t4jwn3jh' order by
piece;
10 Find stale value of the tables which is used by the concurrent program
select table_name, stale_stats, last_analyzed from dba_tab_statistics
where stale_stats='YES';
11 You may have to run the gather stats against those tables which are
having stale value.
12 Use OEM and monitor session ID
13 If development team ask tkprof,AWR,ASH report please generate and analysis.
Reference:
R12.2 forms performance Tunings
Concurrent Manager Performance tuning R12.2
Great information. Thanks for sharing.
ReplyDeleteOracle DBA Online Training