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

2 thoughts on “Discoverer SQL: Long Running Discoverer Report

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s