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;
Subscribe to:
Posts (Atom)