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;

Hourly Concurrent Managers/Requests Performance Report in Oracle Applications 11i

In order to fine tune your Concurrent Managers performance you may need to collect hourly data for Concurrent Managers and Concurrent Requests. Please use following SQL report to generate this data.

COLUMN "Queue Time" format a15
COLUMN "Concurrent Manager" format a20
COLUMN "Program" format a60 heading "Concurrent Program"
set linesize 2000
set echo off
BREAK on "Queue Time" SKIP 1 ON "Concurrent Manager"
SELECT
TO_CHAR (actual_start_date, 'DD-MON-YY : HH24') "Queue Time",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcptl.user_concurrent_program_name "Program",
ROUND
( SUM (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Total Duration (min)",
ROUND
( AVG (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Avg Duration (min)",
ROUND
( MIN (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Min Duration (min)",
ROUND
( MAX (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Max Duration (min)",
COUNT (*) "Times Run", fcq.target_processes "Total Processes"
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq,
fnd_concurrent_requests fcr
WHERE fcr.phase_code = 'C'
AND fcr.actual_completion_date IS NOT NULL
AND actual_start_date IS NOT NULL
AND fcq.concurrent_queue_id = fcproc.concurrent_queue_id
AND fcq.application_id = fcproc.queue_application_id
AND fcq.manager_type = 1
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_name NOT IN
('ACTIVATE', 'ABORT', 'DEACTIVATE', 'VERIFY')
AND fcr.concurrent_program_id = fcptl.concurrent_program_id
AND fcr.program_application_id = fcptl.application_id
AND fcptl.LANGUAGE = 'US'
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YY : HH24'),
fcqtl.user_concurrent_queue_name,
fcptl.user_concurrent_program_name,
fcq.target_processes
ORDER BY "Queue Time" ASC,
"Concurrent Manager" ASC,
"Times Run" DESC,
"Max Duration (min)" DESC,
"Total Duration (min)" DESC

Generate Graph for you presentation using MS Excel