Troubleshooting oracle long running request - Performance tuning

 The most common issue faced by all the oracle apps dba is a long-running concurrent program. Let us discuss the different approaches to troubleshoot the long-running oracle concurrent program.

Step 0: Check the request

Check how long the program has been running and also check the previous run of the same program to get an idea about how long it should be running.  Use the Query from the Post (Current running Concurrent Program).

The query shows the result of current running concurrent programs with the previous runtime.

Step 1: Request id to SID

The first step of troubleshooting a long-running concurrent program is to find the sid of the concurrent program. Please use the below query to find the sid of the concurrent program.

Request id to SID query :

SELECT a.request_id,
       s.sid,
       s.serial#,
       s.osuser,
       s.process,
       c.spid
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       v$process c,
       v$session s
WHERE  a.controlling_manager = b.concurrent_process_id
       AND c.pid = b.oracle_process_id
       AND b.session_id = s.audsid
       AND a.request_id = &request_id
       AND a.phase_code = 'R'; 

Step 2:Locks and blocks

Check are there any locks or blocks blocking the concurrent program operation using the below queries.

Lock Query

CLEAR COLUMNS
col ssid    form A7
col lmode   form 999 heading "loc"
col request form 999 heading "req"
col name    form A30
break on id1 on sid
select lpad(' ',decode(a.request,0,0,3))||a.sid ssid,
       a.id1,
       a.lmode,
       a.request,
       c.name
  from sys.obj$  c,
       gv$lock    b,
       gv$lock    a
 where a.id1 in ( select id1 from gv$lock where lmode = 0 )
   and a.sid  = b.sid
   and c.obj# = b.id1
   and b.type = 'TM'
 order by a.id1, a.request, b.sid, c.name
/ 

Block Query

SELECT blocking_session,
       sid
FROM   v$session
WHERE  blocking_session != 0; 

If you find any locks or blocks, check whether the lock/blocking sessions are inactive and can be killed. If the sessions can be killed then kill the session and monitor the concurrent program.

Step 4: Check load on the server

Check the load on the server and if the load is more in the server, find the top CPU consuming SQLs and check whether any of the top CPU consuming SQL can be terminated to reduce the load.

Step 3: Enable trace from backend

Before proceeding to the next steps, please enable trace for the concurrent program to analyze further in case if needed. Pass the spid value from step 1 output.

oradebug setospid &ospidvaluefromstep1
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12
oradebug tracefile_name

To disable the trace:
oradebug event 10046 trace name context off
 

Step 4: Find the current SQL of the concurrent program

To troubleshoot the long-running concurrent program, we need to find the current SQL, which might cause the long-running concurrent program.

Step 5: Run Auto tuning advisor

Using the sql_id found in the previous step to run the auto tunning from the backend as mentioned in the post(SQL Tuning advisor).

Analyze the suggestions by SQL tuning advisor and implement the best solution in your environment.

These are some of the basic ways of troubleshooting the oracle long-running concurrent program. 

Will discuss more about the   Long-running request in future posts. Also, check out the post about tuning a long-running query in the post(Tuning a long-running query)