I'm working on a report(Discoverer Plus) which returns some product information. Parameters are a product id or a product name.
The report has three page items.(typically these would have between 3 and 10 entries in each).
If I run the report for our busiest product id the report completes in about 5secs.
If I then chose something from the page item the report can then take up to 20mins to complete, doesn't seem to matter what I pick.
I notice that if I instead refresh the report and enter a product id with little data, change the page items there and then refresh and enter the the product id of our busiest product that the report will complete in about 5secss, returning the same data as the report that will take 20 mins.
I am new to Discoverer and have discussed this with some colleagues but they are not sure, so I'm left scratching my head.
So I'd appreciate any insight that peeps might have in to this.
I would have thought that once I enter a product ID all data is returned from the DB and indexed for page items, I just can't see what Discoverer is doing for 20 odd mins.
It is hard to know what the problem is without some information about what Discoverer is doing when you select a page item. You need to look at the SQL being run, either using the SQL inspector or better still, use a database monitoring tool to examine the SQL and the optimizer plan the database is using.
Also make sure that your product id is a mandatory parameter to ensure that Discoverer always using a product id condition.
I had the Discovery admin check oem and he can see that when the page item is selected that the cpu is busy processing the data for the 15-20 mins, no additional call is made to the database.
I installed Discovery Desktop today and the same report changes tabs almost instantaneously through it, so I'm left scratching my head.
I do see occasional warnings about low heap memory on the AS but on Discovery Desktop with even a 15k memory cache the report only took a few seconds to switch pages.
The admin has advised to raise an SR with Oracle but I'd be interested in any areas to check.
Edited by: dobrien_ie on Jun 1, 2012 3:40 PM