Showing posts with label report usage stats. Show all posts
Showing posts with label report usage stats. 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;
Subscribe to:
Posts (Atom)