Monday, January 21, 2008

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

11 comments:

Anonymous said...

Hi, Aviad.

You got a performance problem with EUL5_QPP_STATS table, however you had to "rebuild the EUL5_QPP_ENABLE table".

What is a connection?

Aviad said...

Hi Anonymous...

After deleting the rows from EUL5_QPP_STATS table, I noticed the explain plan for the query prediction sql was not improved and had the same cost like before.
I'm not sure what exactly happened, maybe the segment size cause this behaviour or somthing specific for 9i.
In 10g I found it's not necessary to rebuild the table.
Anyway, if you can see improvement for the query prediction sql without rebuilding the table, perfect! Don't rebuild it.

Regards

Anonymous said...

Hi Aviad,

Is there any way to disable this feature on discoverer desktop so that the query prediction does not take place?

Aviad said...

Hi,

Yes, There is a way to disable query prediction.
Follow this:
1. Start -> run -> regedit
2. HKEY_CURRENT_USER -> Software -> ORACLE -> Discoverer 10 -> Database
3. Update key "QPPEnable" to -> 0

Aviad

Mahnaz said...

Hi Aviad,

I have the same problem with OD web, could you solve your problem?

Anonymous said...

I have this wierd message window "query running, estimate not available" while running the query. I want the the query running time to be estimated. How do I do that? Any ideas, please let me know.

Aviad said...

Hi,

I'm not sure if you are talking about Discoverer Desktop (client) or Discoverer Plus/Viewer (Web)..

For setting Discoverer Desktop to show query esitmated time you should update the registry at:

HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Database
"QPPEnable"=dword:00000001

For Discoverer viewer/plus you should update QPPEnable at:
$ORACLE_HOME/discoverer/pref.txt

oracle_solutions said...

Hi Aviad, I am currently working on an Oracle Ebusiness site helping to improve the performance of the discoverer EUL by using materialized views. Running reports on my PC via disco desktop is now taking two minutes whereas on some of my testers machines, the same report is taking 15 minutes ( which was the original time it used to take prior to my changes). I have tried two different PCs and my timing is consistent. These users are in a different physical location. Any ideas ?

Naga Shankar said...

Hi Aviad,

we have been seeing the problem like this

" when users connect to the database and tries to run a existing report or a new report(with one column). the report hangs at "Determining the query time estimate" . this is happening on both Discoverer viewer/plus and desktop version . the version of discoverer is 9.0.4.46.00 . i know it is old version and we are upgrading it to new one in a month . could you please help us out if you faced this similar issue before....

appreciate your quick response

thanks

Monkey.D. Luffy said...

Hi ,
Thanks for the blog.
Currently I am looking into an issue where actual time is way more than the Estimated time of the report. I am not sure what to look and where to look.
we are using discoverer viewer(WEB version).
can you please give me some pointers.

Monkey.D. Luffy said...

I am facing an issue where actual running timeof a report is way more than the estimated time.

is there any solution or soemthing I can look into. We are using dsicoverer viewer (WEB).