Question Re: Performance Issues with Webi Reports in Technology Q&A

Performance Issues with Webi Reports

Former Member — Fri, 08 Mar 2013 14:15:46 GMT

Hello,

Recently we upgraded from BOXI r 3.1 FP 1.3 to BOXI r3.1 SP4 and we ve been hit with major performance problems with respect to our webi reports. Reports which used to refresh within 10 minutes are now taking 40 minutes and eventually time out.We have maintained the same configuration parameters at the Universe level and CMS servers level. We have had the DBA review and perform sql tuning and indexing as well. But still the same. Also we had migrated around 200+ reports from the old environment to new environment. Could it be that there might be compatibility issues with objects and other custom variables created from one version to another version?

Any tips on how to improve performance would be very helpful.

Regards,

Kasturi rangan

Re: Performance Issues with Webi Reports

DayaJha — Fri, 08 Mar 2013 14:35:11 GMT

Hi Kasturi,

Please provide the following details regarding your environment:

BOBJ Environment (Distributed or Not)
Web Application Details
Hardware setup is as per SAP recommended (Regarding number of users & reports)

Thanks,

Daya

Re: Performance Issues with Webi Reports

former_member191664 — Fri, 08 Mar 2013 19:24:47 GMT

Hi, Kasturi,

It'd be better to understand where the WebI performance bottleneck is located if you can follow Daya's comment to provide the detail on your BOXI3.1 SP4 environment. There could be various reasons that your webi report turnaround time is slower than before the upgrade. For example, it could be the CMS CPU usage is spiking; the network bandwidth is busy; the backend database is slow to execute the WebI query; the number of the WIreportServers is undersized; etc.

With this said, several things you can do to improve the WebI performance in general.

- To share a common cache pool between separate physical server boxes in a cluster for the WIReportServers:

-storageRoot <CommonDirectory>

Where Output Cache Directory is a UNC formatted (Do not use drive letters) share or mount location.

If pre-caching is enabled, to utilize this cache amongst different users the advanced right of "Merge dimensions for synchronization" must be applied to the folder or the report itself for a given user or group.

To enable pre-cache sharing from within the CMC > Home > BusinessObjects Enterprise Applications > Web Intelligence > Advanced Rights : "Merge dimensions for synchronization". Grant the users or groups so they can use the pre-cached files.

- SAP BO KBase Article ID:6853398

EXECUTE IMMEDIATE 'alter session set optimizer_index_caching=0'; (100)

EXECUTE IMMEDIATE 'alter session set optimizer_index_cost_adj=100'; (100)

EXECUTE IMMEDIATE 'alter session set query_rewrite_enabled=TRUE'; TRUE

EXECUTE IMMEDIATE 'alter session set "_always_semi_join"=CHOOSE'; CHOOSE

EXECUTE IMMEDIATE 'alter session set "_b_tree_bitmap_plans"=TRUE'; TRUE

EXECUTE IMMEDIATE 'alter session set "_hash_join_enabled"=TRUE'; TRUE

- Aggregate awareness is the ability of a universe to make use of aggregate tables in a database. These are tables that contain pre-calculated data. You can use the @Aggregate_Aware function in the Select statement for an object that directs a query to be run against aggregate tables rather than a table containing non aggregated data.

Using aggregate tables speeds up the execution of queries, improving the performance of SQL transactions.

You set up aggregate awareness as follows:

Define the Select statement for an object using the @AggregateAware function.

Specify which objects are incompatible with the aggregate table and cannot be used in a query that is run against that table.

- many-to-many (N,N)

For each one or multiple rows in table 1, expect one or multiple rows in table 2.

Many-to-many cardinalities are rare in relational databases and will return duplicate rows, causing slower performance and potentially inaccurate results. If you have (N,N) cardinalities, you should re-check the concerned joins, and ensure that you understand the relationship between the tables.

- Using Hints (Oracle only)

A Hint is commented information that is embedded in a query, and used by the Oracle optimizer to choose an execution plan. You should consult your Oracle documentation for full information on the Hints that can be used, and how they can be used to optimize queries.

You enter the value for a Hint as a comment. A plus sign is used at the beginning, immediately after the comment delimiter, with no space between the comment delimiter and the plus sign:

/*+ <hint> */

Some useful Hints that you can use are as follows. Consult your Oracle documentation for a full list:

FIRST_ROWS: Optimizes response time.

RULE: Use rule-based optimization and not cost.

FULL: Does a full table scan on the table.

ROWID: Scans table by rowid.

INDEX_FFS Perform a fast full scan on the index rather than on the table.

FIRST_ROWS

On the Custom page, click Hints and type /* FIRST_ROWS */ in the values box under the parameter list, and click on Set. The SQL is:

SELECT /* FIRST_ROWS */

RESERVATIONS.CUST_ID

FROM RESERVATIONS

- Shortcut Joins

A shortcut join is a join that provides an alternative path between two tables. Shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path.

What do you want to do?

Read about how shortcut joins are used in Designer

Create a shortcut join

Shortcut joins in Designer

A common use of shortcut joins is to link a shared lookup table to another table further along a join path. The join path comprises several different tables in the same context.

In such a case, the shortcut join is only effective when the value being looked up has been denormalized to lower levels in a hierarchy of tables, so the same value exists at all the levels being joined.

Designer does not consider shortcut joins during automatic loop and context detection. However, if you set the cardinality for a shortcut join you avoid receiving the message 'Not all cardinalities are set' when detecting contexts.

To create a shortcut join

Identify the two tables in a join path that can be linked directly.

Create a join between the two tables.

Double click the new join.

The Edit Join dialog box appears.

Select the Shortcut join check box.

Select or type other join properties as required.

Click OK.

The shortcut join appears joining the two tables. A shortcut join is shown as dotted line in the Structure pane.

Note

You should set the cardinality of a shortcut join to the same cardinality as the join path it replaces.

- WebIEventMaxTimeinSeconds

2009/03/10 18:34:01.748|>>|E| | 5119|1128074160|

|||||||||||||||**ERROR:CCDZMgr:Terminator: terminated by timerEvent thread

locking > WebIEventMaxTimeinSeconds

vi $BOBJEDIR/../setup/boconfig.cfg

"WebIEventMaxTimeinSeconds"=ulong:57600

- vi $BOBJEDIR/enterprise120/linux_x86/fonts/fontalias.xml or edit <install dir>:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\fonts\fontalias.xml

Locate Tahoma
Replace Tahoma.ttf with tahoma.ttf
Replace Tahomabd.ttf with tahomabd.ttf

- Increase the WRC java heap size.

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\default\WebIntelligence\RichClient\JVMOptions and change the value -Xmx512m to -Xmx848m (windows 2003) or -Xmx664m (Windows XP).

- To improve the Webi save to PDF/Excel turnaround time

vi $BOBJEDIR/../setup/boconfig.cfg (for UNIX) or regedit (for Windows)
Add the MaxNodes to

[Software\Business Objects\Suite 12.0\default\WebIntelligence\Calculator]

"MaxNodes"=string:"124"

Note that you can go above 124 but not advisible as WIRS will drastically increase in mem consumption. 0 disables the optimization and meaning that this will yield best PDF generation time at the expense of higher Webi Server memory usage.

Restart WIReportServer(s).

- SAP BO KB 1549622 - WIReportServer reports run out of memory WIS 30280 and WIS 30285

edit <installdir>\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\ncs.conf

Locate instrument_level_threshold=10 and change it to 0

instrument_level_threshold=0

Note that the ncs.conf file controls the ncs.dll. The ncs.dll is used when Willy Introscope is used, almost exclusively with Solution Manager. This was added in SP3. If Solution Manager is not being used, making this configuration change will have no adverse affects.

- The WIReportServer is using dynamic memory allocation and the hoard library is not able to recover/free the allocated memory space.

Add -nocrheapalloc switch to the command line to the WIReportServer property in CMC
Use default upper threshold to 1500 MB and max threshold to 1800 MB, and
Restart the WIReportServer

Note that the purpose of using -nocrheapalloc switch is to disables the hoard hook. i.e. The server won't replace new/delete/malloc/free with crheapalloc.dll and will use standard C/C++ library to allocate/free memory.

- Webi LOV sorting

Import the universe.
Double click on the objects.
Edit the object and apply sorting on the object.
Click on Save and Exit.
Click on the display button.
Uncheck Export with universe.
Click Ok and Save and export the universe.
i. Import the universe again double click on the same object,Click on the display button and check the Export with universe option, Save and export the universe.

At last, you can extract the Webi SQL query from one of your existing Webi report and run the SQL query directly in a database client from your BOXI3.1 SP4 server cmd or xterm and measure the turnaround time and compare that SQL query execution time to the Webi Report turnaround time in InfoView and WRC to get you an understanding how much difference is spent on the SQL query execution and report rendering.

Hope this helps,

Jin-Chong

Re: Performance Issues with Webi Reports

Former Member — Tue, 12 Mar 2013 10:06:15 GMT

Hello Daya,

BOBJ env is not distributed

Web Application used: Tomcat 5.5.20

Not sure if hardware is SAP recommended since the setup was done by third party(BMC Software).

Let me know in case you need further information. Thanks in advance

Regards,

Kasturi Rangan

Re: Performance Issues with Webi Reports

DayaJha — Tue, 12 Mar 2013 10:59:24 GMT

Hi Kasturi,

Regarding Performance Tuning of Web Application(Tomcat Apache) you can follow the below Steps for Other details refer JinChong Comments:

Step I: Go to the following Path of Tomcat installed with BOE folder is:

<BO install folder>\Tomcat55\conf

Step II: Take a backup of server.xml file before doing any changes.

Step III: Open the server.xml file for edit.

Find the non-SSL HTTP/1.1 Connector and add following lines into it:

compression="on"

compressionMinSize="2048"

nocompressionUserAgents="gozilla, traviata"

compressableMimeType="text/html,text/xml,text/plain,text/css,

text/javascript,text/json,

application/x-javascript,

application/javascript,application/json"

Save the file and close it.

Step IV: Restart Tomcat so the changes take effect.

Hope this will help you as well. After Completion of Activity please close the thread

Thanks,

Daya

Re: Performance Issues with Webi Reports

former_member188911 — Wed, 13 Mar 2013 16:06:17 GMT

Only trace log analysis can determine where time is spent, you should ask who provided the installation to do the analysis.

thanks

Simone

Re: Performance Issues with Webi Reports

Former Member — Mon, 17 Mar 2014 14:54:16 GMT

Hi Daya,

What does the parameter you mentioned above do?

If we have SSL configured, then should the above text still go under the non-SSL HTTP connector or should it go under the SSL connector?

Thanks for the help