Discoverer SQL: Long Running Discoverer Report
Discoverer SQL: Long Running Discoverer Report
Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.
SELECT q.qs_doc_name Worksheet,
q.qs_doc_details Sheet ,
q.qs_num_rows Lines,
QS_ACT_CPU_TIME CPU_Time,
q.qs_act_elap_time Seconds,
FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes,
q.qs_created_date Executed,
q.QS_DOC_OWNER Owner,
u.user_name APPS_User,
CASE
WHEN q.qs_state = 0 THEN ‘ERROR :CANCELLED’
WHEN q.qs_state = 1 THEN ‘RUNNING’
WHEN q.qs_state = 2 THEN ‘COMPLETED’
END “CURRENT STATUS”
FROM discovr_apps.eul5_qpp_stats q,
fnd_user u
WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1
AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id
–AND u.user_name LIKE ‘D%’
–AND q.qs_state =1
–AND FLOOR(q.qs_act_elap_time/60) >= 30
ORDER BY q.qs_act_elap_time DESC;
Thanks – Shivmohan Purohit
