How to Monitor Long Running Concurrent Requests in Oracle 11i

Use following Script to monitor long running concurrent requests in Oracle 11i



------------Start of Script-------------------------------------
#!/bin/sh
# Long Running Concurrent Requests
#


SPOOL_FILE=long_runn_conc.html
FINAL_FILE=Long_Running_Concurrent.html


pchk1=`ps -ef | grep ora_smon_$ORACLE_SID | grep -v grep | wc -l`


if [ "$pchk1" -eq 0 ]; then
     echo "WARNING: Possible database shutdown problem"
     exit 0
fi


echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s apps/apps EOF
set echo off
set pagesize 120
set markup html on spool on
spool ${SPOOL_FILE}


TTITLE CENTER 'Concurrent Requests running for more than 20 minutes'


SELECT gv.inst_id "Instance Number",
gv.sid "Sid",
gv.serial# "Serial#",
fcr.request_id "Request ID",
substr(fcr.program,1,40) "Program" ,
fcr.phase "Phase",
fcr.status "Status",
to_char(fcr.actual_start_date,'DD-MON-RR HH24:MI:SS') "Start",
to_char(fcr.actual_completion_date,'DD-MON-RR HH24:MI:SS') "End",
ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) "Time(Min)",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcr.user_name "User Name",
substr(gvw.event,1,30) "Event"
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE Phase_code ='R'
and fcr.oracle_session_id=gv.audsid(+)
and gv.sid=gvw.sid(+)
and gv.inst_id=gvw.inst_id(+)
and fcr.controlling_manager = fcproc.concurrent_process_id
and fcproc.queue_application_id = fcqtl.application_id
and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
and fcqtl.language='US'
and ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) > 20
order by 9 desc;


spool off
set markup html off spool off
EOF


if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
 then
   rm ${FINAL_FILE}
   rm ${SPOOL_FILE}
 exit 0
fi


cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}


(echo "Importance: High"; echo "Subject: VGOP: Long Running Concurrent Requests"; cat ${FINAL_FILE})  | /usr/sbin/sendmail -F VGOP dbadmin@activision.com


rm ${FINAL_FILE}
rm ${SPOOL_FILE}

-------------End of Script-------------------------------------


Use following SQL to get more information.


SELECT   gv.inst_id "Instance Number", gv.SID "Sid", gv.serial# "Serial#", fcr.request_id "Request ID", SUBSTR (fcr.program, 1, 40) "Program", fcr.phase "Phase", fcr.status "Status",
         TO_CHAR (NEW_TIME (fcr.actual_start_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "Start",
         TO_CHAR (NEW_TIME (fcr.actual_completion_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "End",
         ROUND (  (  NVL (fcr.actual_completion_date, SYSDATE)
                   - fcr.actual_start_date )* 60* 24,2) "Time(Min)",
         fcqtl.user_concurrent_queue_name "Concurrent Manager",
         fcr.user_name "User Name",
         (CASE
             WHEN gvw.event = 'latch free'
                THEN (SELECT vl.NAME || ': Latch Name'
                        FROM v$latch vl
                       WHERE vl.latch# = gvw.p2)
             ELSE SUBSTR (gvw.event, 1, 30)
          END
         ) "Event",
         (CASE
             WHEN (gvw.event = 'db file sequential read' or gvw.event ='gc buffer busy')
             AND dbo.object_name IS NULL
                THEN 'Rollback Segment'
             ELSE dbo.object_name
          END
         ) "Database Object",
         fcr.argument_text, gvw.p1, gvw.p2, gv.sql_id,gp.spid
    FROM apps.fnd_amp_requests_v fcr,
         gv$session gv,
         gv$process gp,
         gv$session_wait gvw,
         dba_objects dbo,
         fnd_concurrent_queues_tl fcqtl,
         fnd_concurrent_processes fcproc
   WHERE phase_code = 'R'
     AND gv.paddr    = gp.addr(+)
     and gv.inst_id=gp.inst_id(+)
     AND fcr.oracle_session_id = gv.audsid(+)
     AND gv.SID = gvw.SID(+)
     AND gv.inst_id = gvw.inst_id(+)
     AND gv.row_wait_obj# = dbo.object_id(+)
     AND fcr.controlling_manager = fcproc.concurrent_process_id
     AND fcproc.queue_application_id = fcqtl.application_id
     AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
     AND fcqtl.LANGUAGE = 'US'
 ORDER BY 10 DESC;