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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.