Showing posts with label discoverer. Show all posts
Showing posts with label discoverer. Show all posts

Friday, August 24, 2012

Oracle Discoverer report usage stats


How to find the Oracle Discoverer reports usage stats:



/* Query a specific user's report run history  */
  SELECT A.QS_CREATED_DATE,

         B.user_name,
         QS_DOC_NAME,
         QS_DOC_DETAILS
    FROM appseul_tr.EUL4_qpp_stats a, fnd_user b
   WHERE     QS_CREATED_DATE > TO_DATE ('07 10 28 05 2012', 'hh mi DD MM YYYY')
         AND SUBSTR (a.QS_CREATED_BY, 2, LENGTH (a.QS_CREATED_BY)) = B.USER_ID
         AND user_name = '&USER_NAME'
ORDER BY a.QS_CREATED_DATE DESC



/*  How many times a discovere report is run and how much does it take   */
  SELECT a.qs_doc_owner Owner,
         a.qs_doc_name Workbook,
         a.qs_doc_details Worksheet,
       trunc(AVG (a.QS_ACT_ELAP_TIME) ) duration,
         COUNT (*) run_count, trunc(avg(QS_ACT_CPU_TIME)) CPUTime
    FROM APPSEUL.eul4_qpp_stats a, APPSEUL.eul4_documents b
   WHERE a.qs_created_date between '01-JUN-2012' and '30-JUN-2012'            
                                          AND a.qs_doc_name = b.doc_name
GROUP BY a.qs_doc_owner, a.qs_doc_name, a.qs_doc_details
ORDER BY duration DESC;