Discoverer Performance Tuning: Get running time of a workbook

When using tools like Oracle Discoverer, in where users get the ability to do ad-hoc reporting or to open predefined Discoverer workbooks, key thing is always performance. A report should generate it’s data quickly so workbooks are presented to the user as soon as possible.
If you’re extending the End User Layer (EUL) with your homemade views always make sure the view is defined as efficient as possible.

Oracle Discoverer maintains statistics of running queries – hence also about the workbooks which are defined. Discoverer will use this information also to create summary views to get calculations generated more quickly.

To monitor Discoverer performance you are able to fire a SQL statement which extracts running times of Discoverer workbooks from the database. Running times of worksheets within workbooks are stored in table EUL5_QPP_STATS. It will depend on your Discoverer version what the exact name is of the QPP_STATS table.

See below to get a list from the database with workbooks and sheets and the elapsed time for generating the data set. Long running worksheets shoud be reviewed for performance tuning.

SELECT
    QP_STATS.QS_CREATED_DATE STAT_CREATED
    , QP_STATS.QS_DOC_OWNER STAT_OWNER
    , QP_STATS.QS_DOC_NAME STAT_WORKBOOK
    , QP_STATS.QS_DOC_DETAILS STAT_WORKSHEET
    , QP_STATS.QS_ACT_ELAP_TIME STAT_ELAPSED_TIME
FROM
    EUL_US.EUL5_QPP_STATS QP_STATS
ORDER BY
    QP_STATS.QS_CREATED_DATE DESC