SAP Basis Tech Supports

Symptom
This note describes the special settings for the database parameters for MaxDB Version 7.5.00 and 7.6 which affect the performance of BW systems.
This note is an addition to the general parameter Note 767635 (Version 7.5) or Note 814704 (Version 7.6). This note can override parameter recommendations from Note 767635 or 814704 for BW systems.
Other terms
Parameter, optimizer, join optimizer, load balancing, co-routines, JOIN_OPERATOR_IMPLEMENTATION, LRU_FOR_SCAN
Reason and Prerequisites
This note applies to BW systems.
Do NOT apply this Note to liveCache or OLTP systems.
Use Note 767635 for MaxDB OLTP systems.
Solution
******************************************************************
* MaxDB Version 7.6.01 for BW systems is now available.
* It improves performance in the BW environment
* and contains a BW feature pack.
* For more information, see Note 983845.
******************************************************************
BW functions are database-intensive, the database portion of the overall response time is higher than in the OLTP system (R3 system). Therefore, the settings for the CACHE_SIZE and MAXCPU database parameters should be higher than in an OLTP system.
In small BW systems, you should also choose the number of data volumes and the hard disk configuration in such a way that parallel I/O can be supported without problems. For information about the optimal configuration for the MaxDB data volumes, see Note 820824.
1. MAXCPU
If an SAP instance is active on the BW database server, the distribution of the available CPUs between SAP and the database should be 50:50 and not 3:1 as in OLTP systems.2. CACHE_SIZE
We cannot give a general recommendation for the size of the caches because the setting for the available memory depends on the system. But the same recommendation applies as before, that is, if an SAP instance is active on the database server, this should be configured as small as possible to provide as much memory to the database as possible.
You should not swap the systems of a database server.3. LRU_FOR_SCAN (Extended Parameter)
The data cache is organized as an LRU (Least Recently Used) list. The LRU list is a chain of data pages. The pages that were used last are at the front.
You can use the LRU_FOR_SCAN parameter to control whether you want the whole data cache to be used for table scans or whether you only want 10% of the data cache to be used (LRU_FOR_SCAN = NO).
In BW, using the whole data cache for table scans has proved advantageous.
Therefore, you can set the LRU_FOR_SCAN parameter to YES.
If you use MaxDB Version 7.6.03 and 7.6.04 with a build less than 6, refer to Note 1127082.4. MAXLOCKS
If the system issues error message: -1000 too many lock requests, the limit of the MAXLOCKS parameter has been reached. In BW systems, the parameter setting is between the following limits:
500,000 <= MAXLOCKS <= 1,500,0005. Join parameter
a) JOIN_OPERATOR_IMPLEMENTATION or OPTIMIZE_OPERATOR_JOIN
In Business Warehouse installations (MaxDB Version 7.5), always set the JOIN_OPERATOR_IMPLEMENTATION parameter to ‘IMPROVED’, otherwise (=NO) endless runtimes may occur in particular during the aggregate setup.
Note: As of MaxDB version 7.6, parameter JOIN_OPERATOR_IMPLEMENTATION was renamed. The new name is:
OPTIMIZE_OPERATOR_JOIN. In MaxDB version 7.6, ensure that parameter value ‘YES’ is set for OPTIMIZE_OPERATOR_JOIN.
b) JOIN_MAXTAB_LEVEL9 and JOIN_MAXTAB_LEVEL4
These two parameters define which algorithm is to be used to determine the optimal Join sequence depending on the number of join tables.
If the number of join tables <= JOIN_MAXTAB_LEVEL9, a permutation is carried out. If the number of the join tables >= JOIN_MAXTAB_LEVEL4, the greedy algorithm is used. If the number of tables is between both parameter values (JOIN_MAXTAB_LEVEL9 < tables < JOIN_MAXTAB_LEVEL4), the internal algorithm is used.
You should change the JOIN_MAXTAB_LEVEL9 parameter as described in Note 322715 only if the behavior described in the note occurs in the BW system.
If you intend to set the parameter in online mode of the database, you must first read Note 1112049 because the database may crash.

You should not change the JOIN_MAXTABLEVEL4 parameter in production systems with large data volumes. If performance problems occur in these system, they must be examined by SAP Support.
In small BW systems, it may be useful (if the EXPLAIN takes a very long time) to reduce the size of the JOIN_MAXTAB_LEVEL4 parameter. However, you should only do this after a through analysis by MaxDB Development Support.
c) OPTIMIZE_STAR_JOIN (Support parameter)
This parameter is available as of MaxDB version 7.5.00 edition 38 and is activated by default (OPTIMIZE_STAR_JOIN=YES).
This special new join processing activated by the parameter has proved advantageous in BW. Implement at least MaxDB version 7.5.00 edition 38 and ensure that the parameter is set to YES.
The parameter only has an effect if all fact tables in the BWTABLES system table in the FACT column have the value YES.6. Parallel access with the join:
Do not set parameter OPTIMIZE_JOIN_PARALLEL_SERVERS in current MaxDB Versions 7.5.00, 7.6.01, 7.6.02 and 7.6.03 (OPTIMIZE_JOIN_PARALLEL_SERVERS=0), because synchronization problems occur in these versions.
7. Distributing the server tasks to several UKTs
As of database Version 7.5.00 Build 09, you can use the ALLOW_MULTIPLE_SERVERTASK_UKTS parameter to distribute server tasks to different UKTs and to use several CPUs for server tasks. You should activate the ALLOW_MULTIPLE_SERVERTASK_UKTS parameter in BW systems (=YES) if:
MAXCPU > 1
numerous server tasks are configured
the parallel access is activated for the join
You must restart the database to activate the parameter changes.