Monitor Report Server job queue performance
                                                 Last update (2008-05-08 10:27:30)
                                                                                                                   Date added (2008-05-08 10:11:42)

Summary
To monitor how reports are processed in the JOB QUEUE you must install the RW_SERVER_JOB_QUEUE table in the infrastructure database.

For detail steps search Metalink for: Using the Reports Server Queue PL/SQL Table and API (rw_server_queue)
Note:72531.1
SELECT JOB_ID, JOB_NAME, SERVER, QUEUED, STARTED, FINISHED, RUN_ELAPSE/1000 DB_SEC, TOTAL_ELAPSE/1000 TOTAL_SEC, 
(TOTAL_ELAPSE - RUN_ELAPSE)/1000 WAITING_SEC 
FROM SYSTEM.RW_SERVER_JOB_QUEUE 
WHERE TRUNC(QUEUED) = TRUNC(SYSDATE) 
AND STATUS_MESSAGE = 'Finished successfully'
--AND (TOTAL_ELAPSE - RUN_ELAPSE)/1000 > 2
ORDER BY QUEUED DESC;
To find the reports that are running now (active sessions at database)
SELECT STATUS_MESSAGE, JOB_ID, JOB_NAME, SERVER 
FROM SYSTEM.RW_SERVER_JOB_QUEUE
WHERE STATUS_CODE =1
AND JOB_TYPE != 'Scheduled';
To create a calendar of all the reports run successfully for every our hour and day.
SELECT * FROM (
SELECT A.* FROM (SELECT * FROM (SELECT TO_DATE(B.DATE_TIME, 'DD/MM/YYYY') DT, 
TO_CHAR(TO_DATE(B.DATE_TIME, 'DD/MM/YYYY'), 'DAY') DAY, 
"00", "01",  "02", "03", "04", "05", "06", "07", "08", "09", 
"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", TOTAL 
FROM (SELECT  DATE_TIME, 
SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02", 
SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05", 
SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08", 
SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",
SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",
SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",
SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",
SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", 
COUNT(*) TOTAL  
FROM (SELECT TO_CHAR(QUEUED , 'DD/MM/YYYY') DATE_TIME,  
SUBSTR(TO_CHAR(QUEUED , 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM SYSTEM.RW_SERVER_JOB_QUEUE 
WHERE STATUS_MESSAGE = 'Finished successfully')
GROUP BY DATE_TIME) B)) A) ORDER BY DT DESC;
Reviews
Filters
Search