We have developed a report that has 4 dataproviders, all from one universe. And, there is no linking used among the dataproviders.
Previously the report ran in QA vs UAT database in 6-8 min, but now the report takes about 100+ min.
Our observations,
The SQLs are retreiving the data very fast when run directly on database.
When we refresh the dataproviders independently, they are quick in retreiving data.
When ‘refresh all’ is selected, the report takes about 100+ min.
Is there anything that we missed out without checking.
Any help is well appreciated.
Hi.
there is merge dimension in you report? If yes,Could you try to refresh the document without dimension merged?
Some times BO takes long time to build the cube with all the data merge. maybe its you case.
Good luck!
Are you are running the reports interactively? Do you experience the same poor performance when scheduling the report? Can you check the task manager on the BOBJ server to observer the activity of the various BOBJ services?
We’re having similar issues to those described above. We’re using XI 3.1 with an Oracle database, and some WebI reports are running extremely slowly, much more slowly than they used to. Slow queries have been reported in 4 universes. Most of the slow reports have multiple data providers, but not all.
The report that I’m investigating at the moment has maybe 8 or 10 data providers. If I refresh them individually most of them finish in a second or two. The slowest one takes 15 seconds, and there’s another that takes 5 seconds. If I schedule the report, I get data in about 3 minutes. But if I run it interactively it takes a long time; it can run for 20, 30, or 60 minutes before I give up and cancel it. I’m not scheduling the report for off hours; I’m scheduling it “now” during the business day, so the difference isn’t due to system load.
I can understand that it takes longer to refresh all data providers at once than to refresh them one at a time because BO is merging dimensions, calculating variables, etc. But why does the report run so much faster when it’s scheduled? Any suggestions about how to improve the performance of interactively refreshed reports?
Try it in Structure Mode, so rendering etc is taken out of the picture. I think you need to refresh it interactively and trace it at the database side at the same time. I bet that the database refresh finishes in a few minutes, but something is going wrong with WebI picking up that the database refresh is finished. I’ve seen similar in Xir2.
I tried refreshing one of the reports in structure mode. The report didn’t finish so I clicked cancel after 13 minutes, and it seemed to be hung: I didn’t get back to Edit Report, it just stuck with the “Retrieving Data” dialog box, which was grayed out.
I found a suggestion elsewhere on BOB to use Task Manager to observe the server activity while running the problematic query. Our sysadmin and I tried that, but everything looked fine: CPU usage was no more than 10% (briefly), mostly it was 0% or 1%. Memory usage was similarly minimal. One complicating factor is that our production environment is a cluster of 5 servers and there’s no way to tell which server I was using. So we were actually looking at Task Manager on each of the servers simultaneously, and none of them were using significant resources.
I tried running one of the problematic queries in one of our non-production environments which has only one server (where we could more easily monitor resources on the server), but it completed after 7 minutes.
Based on our testing so far I’m pretty sure that it’s not:
[list]
the database (the query runs when scheduled and when run in SQL*Plus)
system load (the problem happens both under heavy load and light load)
the universe (the problem happens with reports in multiple universes)
CPU or memory on the WebI server
[/list]
It seems to be intermittent; some of our staff have gotten data from a query that never finishes for the user. I’m starting to suspect that the queries are hanging rather than just being slow. Maybe the clustering is part of the problem, since the query completed in a one-server environment?
Anyone have any suggestions or guesses about what could be going on?
I thought you’d be interested in hearing what we’ve found so far in our investigations. There seems to be more than one cause for the slowness.
We were able to tune some of the reports that users said were running slowly so that they’re much faster.
We’ve found that there are some other reports that not only run slowly but also degrade performance for other users at the same time. In other words, when we get several calls about slow reports and check the database, we sometimes find that there is a query that’s been running for an hour or more. When we stop that query, performance improves for others.
We think that the first query must be consuming some resource that the other queries need, and we’re trying to identify what that resource is. It’s not anything obvious like CPU or memory on the WebI server or the server with the data; those are all fine. We’ve found ways to speed up some of the resource-intensive queries, which should improve performance for other users.
Some reports seem to hang some of the time. In other words, we can see that the query has completed in the database, but BO never displays the data.
(BOB member since 2006-12-10)
(BOB member since 2002-08-15)
(BOB member since 2006-03-30)