Concurrent request running from a very long time

Issue: Concurrent request running from a very long time 

 

Before confirming a concurrent Request to long running we need to conclude/observe some of the things

•Check the Statistics,History Runs of that program & Based on this timelines we need to decide the long running Request.
•Check The load on DB node to ensure that High Resource Usage is not the Main cause.i troubleshoot
•Check DB locks in The Database To Ensure that This Session is not blocked by any other session.’
•Check wether Same Iteration Of concurrent program is running on Instance. like if concurrent request is running twise/trice
If we are Happy/resolved the above things and find that we dont have any problem Then we need to Digg

long running concurrent Request

Step 1 : Get Concurrent Request ID of long running concurrent request from Application(fronted).

Navigation : Application >> System administrator>> concurrent>request>

Step 2 : Find session associated with concurrent request .i.e SID

By using the above Query we can get sid,serial#,spid of the concurrent Request..

SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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’;

In My case The Concurrent Request id is 678901

Output of above query returned

REQUEST_ID SID SERIAL# SPID
———————————————————–
678901 1973 89 1100
Here Conurrent Request id is 678901 & Asoociated Session is 1973.

SPID is the process that Running on DB node Because of This Request..

oracle@acer $ ps -ef|grep -i 1100

oracle1100 1 0 3:30:43 0:03 oracle (LOCAL=NO)

Note : before Using Oradebug make sure that spid shoud exist on DB node..

Step 3 : Enable event 10046 trace with level 12 using oradebug .

Syntax : oradebug setospid SPID

Here SPID is the process id that we are getting from step 2

Coming to My Case
SQL> oradebug setospid 1100
—
Oracle pid: 79, Unix process pid: 1100, image: oracle@Acer
—

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc

Wait for 30 Min to get the Trace file to find the Root Cause

Step 4 : Disable trace if u r thinking that trace is enough to find the root cause..

SQL> oradebug event 10046 trace name context off

Step 5 : Convert raw trace To Understandable tracefile By using tkprof/Traceanalyzer

tkprof ‘ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc’ ’ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.txt ’ explain=apps/[apps_passwd] prsela.

Step 6 : Check TKPROF out file to find root cause of slow concurrent request.

 

********************************************************************************
.
Application version: 11.5.10.2 multi-node (4 nodes)
Database: 11g RAC


Scenario:

Business critical concurrent request is running from more than 2 hours which was supposed to complete within 1 hour.
The standard manager and the manager responsible to execute the critical concurrent request (custom manager) were overloaded with many requests. The server on which these managers run had very high server load and all CPU’s were utilized.

After diagnosis, we had to kill few requests which were running from more than 2 hours and were creating load on the server. These requests were in fact not performing anything and had gone zombie.

The server load came down and CPU’s available were set free to execute new requests/processes.

However the business critical request was still running and wasn’t moving ahead at all. The database session associated was also not executing anything.

Challenge:

Since this being a critical business request, we couldn’t terminate the request coz we had already lost few hours running it. At the same time the database session also wasn’t performing any execution and the whole request was not going anywhere even though it showed running normal status.

Trick:

Not recommended, however can be a life saver at times.

---- Forcibly change the status of the request to pending normal.
---- Kill the database session of the request.

Use the below scripts:

update applsys.fnd_concurrent_requests set phase_code = 'P',
                                           status_code = 'I',
                                           actual_start_date = null,
                                           crm_release_date = null,
                                           controlling_manager=null,
                                           logfile_name = null,
                                           logfile_node_name = null,
                                           outfile_name = null,
                                           outfile_node_name = null,
                                           crm_tstmp = null
where request_id in ('request-id');


             alter system kill session 'sid, serial#, @instance-id';


Hence a new database session will get created as soon as the old one is killed and the request starts running. Since the server on which the request is supposed to get processed also had no issues now, the request went through very fast without any issues ;)

********************************************************************************