VERY slow reporting performance
We've created reports which, when run directly on Oracle through sqlplus (copying the exact SQL from the report) take around 3 minutes, when run from BO (using Web Intelligence) take hours.
The SQL outputs around 300,000 rows, which equates to a few hundred pages in the report. Is there a reason why the report would take so long (the session to Oracle is open the whole time)? I can understand it not taking just the time for Oracle to execute the query, as WebI then needs to take the results and produce the report, but I wouldn't expect it to take hours.
So far on our servers we've concentrated on making Oracle perform faster, and now we've got that working better our attention has been drawn to the performance issues of the reporting, and we don't have any experience with tuning it, so any advice would be very much appreciated.
We're running BO Enterprise XI R2 SP3 on Linux with Web Intelligence as the only reporting interface. The database is Oracle RAC (3 node cluster) 10.2.0.4.