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.
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:
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
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:
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.
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 */
- 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.
The shortcut join appears joining the two tables. A shortcut join is shown as dotted line in the Structure pane.
You should set the cardinality of a shortcut join to the same cardinality as the join path it replaces.
2009/03/10 18:34:01.748|>>|E| | 5119|1128074160|
|||||||||||||||**ERROR:CCDZMgr:Terminator: terminated by timerEvent thread
locking > WebIEventMaxTimeinSeconds
- 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
- 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
[Software\Business Objects\Suite 12.0\default\WebIntelligence\Calculator]
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.
- SAP BO KB 1549622 - WIReportServer reports run out of memory WIS 30280 and WIS 30285
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.
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
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,