Oracle Discoverer Query Prediction functionality and Performance

Lately we noticed that our Discoverer reports runs very slow. Actually, the problem wasn’t the Discoverer reports query but the query prediction that for some reason took so long. What is Query Prediction in Discoverer? “Discoverer includes functionality to predict the time required to retrieve the information in a Discoverer query. The query prediction appears before the query begins, enabling Discoverer users to decide whether or not to run the query. This is a powerful facility that enables Discoverer users to control how long they wait for large reports.” (from Oracle doc’) The query prediction is the elapsed time while the following message appeared on the bottom left of Discoverer Desktop window: "Determining query time estimate". For each report we tested, we found query prediction runs 30%-50% (!!!) from the report’s total run time. Next phase was to start a SQL trace on Discoverer session to see what actually happens when running a Discoverer report. This is the relevant section from the SQL Trace: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC As you can see, the query prediction functionality tries to retrieve statistics information from EUL5_QPP_STATS and it takes 35 seconds. (total time for this report is 55 seconds). The query prediction based, among other things, on the query prediction statistics table – EUL5_QPP_STATS. This table records query prediction statistics while running Discoverer reports. There is no logic by estimating query run time longer than the report’s query itself… Since the query prediction functionality is important to our users we avoid from disable this functionality (by setting the QPPEnable to 0). Furthermore, I found that we have statistics data in this table from 7 years ago…  There is no meaning to hold these statistics… I tried to find information about purging the EUL5_QPP_STATS and I found this: “How to delete old query prediction statistics” in Oracle® Business Intelligence Discoverer Administration Guide 10g Release 2 (10.1.2.1) There is a SQL script at [ORACLE_ HOME]\discoverer\util\eulstdel.sql – that deletes all query prediction statistics that were created before a specified date. Great! I executed this sql on my database, gave 90 days as a parameter and it deleted 460,000 (from 468,000) rows. I ran a Discoverer report again, but still query prediction takes too long, same like before. I checked the explain plan and the cost of the above SQL and it remains the same. I tried to gather statistics on EUL5_QPP_ENABLE table and rebuild its indexes but cost become higher… (More than 103, something like 800…). I had no choice but rebuild the EUL5_QPP_ENABLE table (by export, drop table and import). After recreation of EUL5_QPP_STATS table I ran a Discoverer report again and query prediction takes insignificant time, almost nothing…  :-) This is from the trace I took after: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC The elapsed time for this sql reduced to 0.05 seconds!! (was 35 sec’ before) Sql cost reduced from 103 to 31! I checked this issue on Discoverer Desktop 10g (10.1.2.2) but it is relevant to the web tools (Discoverer viewer and Discoverer Plus) as well, since the query prediction functionality exist in these tools like in the client version. You are welcome to leave a comment. Aviad