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;