Concurrent request running longer than expected, Concurrent request is not picking jobs

Concurrent request running longer than expected, Concurrent request is not picking jobs , Concurrent request is not fetching/executing data etc these are very frequent issue we get from development team.



Following are the points which we have to ask the development team:

1. Is there any code changes done in the concurrent program

2. Was this running long in last few run as well, or this time only

3. How much time does it normally takes to complete

4. Is this jobs fetching higher data compare to last run

5. Does this job runs any specific time or it can be run anytime

6. Does this job fetching data using DB Link

Now when you asked these question to development team in meanwhile you can start your basic checks in your environment.

1. Check the load of the server using top command

2. Verify the mount space using df -hP

3. Check the tablespace size

4. Verify the status of the concurrent request using query:

select request_id ,phase_code, status_code from fnd_concurrent_requests where request_id ='333333';

5. Check which CM is running this request.

SELECT request_id, user_concurrent_program_name, status_code, phase_code, completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id =&req_id ;

6. Confirm the Actual and target of the CM from above output

7. Confirm the Concurrent Manager Status and queue

8. Check the SID of the concurrent request using query:

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id 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 = .oracle_process_id

AND b.session_id=d.audsid AND a.request_id =&req_id AND a.phase_code = 'R';

9. Query to get the row fetched using the SID from step 8.

column name format a30 word_wrapped

column vlu format 999,999,999,999

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%' /


col sid format 999999

col username format a20

col osuser format a15

select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b where .paddr= b.addr and a.sid='&sid' order by a.sid;

10. Check Database session status and what it is running

set echo off

set linesize 132

set verify off

set feedback off

set serveroutput on;

declare

   SID number        := 0 ;

   SERIAL number       := 0 ;

   username varchar(20)   := '';

   Status varchar(8)     := '';

   machine varchar(10)   := '';

   terminal  varchar(25)  := '';

   program  varchar(30)   := '';

   Module varchar(30)    := '';

   Action varchar(20)    := '';

   sql_hash_value number  := 0 ;

   logontime varchar(30)   := '';

   last_call_et number    := 0 ;

   proc number        := 0 ;

   spid number        := 0 ;

   event varchar(30)     := '';

   state varchar(30)     := '';

   sql_text varchar(2000)  := '';

cursor cur1 is

select a.sid sid,

   a.serial# serial,

   a.username username,

   a.status status ,

   a.machine machine,

   a.terminal terminal,

   a.program program,

   a.module module,

   a.action action,

   a.sql_hash_value sql_hash_value,

   to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,

   a.last_call_et last_call_et,

   a.process proc,

   b.spid spid,

   sw.event event,

   sw.state state

from  gv$session a, gv$process b, gv$session_wait sw

where a.paddr=b.addr and a.inst_id=b.inst_id

   and a.sid='&1'

   and a.inst_id=sw.inst_id

   and a.sid=sw.sid;

begin

 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');

 DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');

 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');

for m in cur1

loop

DBMS_OUTPUT.ENABLE(50000);

  DBMS_OUTPUT.PUT_LINE(' ');

  DBMS_OUTPUT.PUT_LINE( 'SID............ : ' || m.sid );

  DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial  );

  DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username  );

  DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status   );

  DBMS_OUTPUT.PUT_LINE( 'Machine........ : ' || m.machine );

  DBMS_OUTPUT.PUT_LINE( 'Terminal....... : ' || m.terminal);

  DBMS_OUTPUT.PUT_LINE( 'Program........ : ' || m.program );

  DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module );

  DBMS_OUTPUT.PUT_LINE( 'Action......... : ' || m.action );

  DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value );

  DBMS_OUTPUT.PUT_LINE( 'Logon Time..... : ' || m.logontime );

  DBMS_OUTPUT.PUT_LINE( 'Last Call Et... : ' || m.last_call_et );

  DBMS_OUTPUT.PUT_LINE( 'Process ID..... : ' || m.proc );

  DBMS_OUTPUT.PUT_LINE( 'SPID........... : ' || m.spid );

  DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event );

  DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);

dbms_output.put_line('SQL_TEXT is..........:');

for rec in ( select sql_text from v$session s,v$sqltext v where

  s.sql_hash_value=v.hash_value and

  s.sql_address=v.address and s.sid=m.sid order by piece)

loop

dbms_output.put_line(rec.sql_text);

end loop;

  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');

  DBMS_OUTPUT.PUT_LINE(' ');

end loop;

end;

/

11. Check which sql id it is running, you can get these details using above steps.

12. You should check the sql_id history and can run sql advisory against the

   sql_id. this will give you incase of the sql query is not correct or may have

   to see the recommendation based on the sql advisory

13. Check the 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';

14. You may have to run the gather stats against those tables which are having    stale value.

15. You can also use OEM and monitor the Session id.

After checking these above points you can definitely narrow down the issue and based on your finding you can suggest the user corrective action item.