VERY slow reporting performance

[Former Member]

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.

[Former Member]

Do you have trace on the Webi Report Server?

Also have you used the -querytiming switch with the CMS to determine (obviously) query times?

[Former Member]

I haven't enabled trace on the job server yet. Is there any particular part of the command line the "-trace" should go? Also the same question for the querytiming switch.

I'm not familiar with the querytiming option, where does this log it's information to? (is there a document somewhere that explains these special options?)

[Former Member]

Correction: I incorrectly said jobserver in the earlier post (I read your post wrong).

You need to start a trace on the CMS (for querytiming), the Webi Report Server and the Connection Server (which accesses the db)

For the CMS trace, stop the CMS, in ccm.config, add a '-trace -querytiming' to the end of the cmsLaunch line. Similarly, add a -trace to the command line for the Webi Report Server (I don't remember the exact line; I don't have a XI R2 deployment on Linux available right now)

To enable tracing on the connection server, do the following:

(SAP 1197745 - How to enable connection server tracing on a UNIX platform)

Open cs.cfg in a text editor. These are located by default in

Linux: bobje/enterprise115/linux_x86/dataAccess/RDBMS/connectionServer/cs.cfg

Locate the following entry:

Traces ClassID="csTRACELOG" Active="No"

Change the "No" to "Yes".

Traces ClassID="csTRACELOG" Active="Yes"

Save the file.

Restart the Web Intelligence Report Server.

====================
NOTE:
Be aware that connection server traces slow down the server performance. Detailed traces get generated when this level of tracing is enabled. Disable tracing when not needed.
====================

PS: The Webi Report Server contains the Connection Server libraries. The Connection Server runs in-proc within the Webi Report Server. Hence the need to restart the Webi Report Server.

Also, I encourage you to open a ticket with BO on this. Performance issues are never easy to troubleshoot, even less so on a forum such as this.

Finally, I don't believe there is any document that lists all command line options. Or rather, if such a document exists, I don't believe it's available for public consumption.