Issue: Concurrent request running from a very long time

.
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 ;)