SAP Basis Tech Supports

Symptom
This note provides recommendations for configuring the database parameters
for SAP MaxDB Version 7.8 for OLTP, CRM, BW, and OneDB systems.
When you configure OneDB systems, also refer to
Note 1147936
Other terms
MaxDB parameter, trigger, parameter
Reason and Prerequisites
You are using MaxDB Version 7.8.
Solution
To ensure that the parameter is set correctly, use the current version
of the DB Analyzer config file, which you can obtain from Note 1111426.

When you use the Database Studio, transaction DB50, or the DBA Cockpit to perform parameter changes, we recommend that you enter the reason for the parameter change (for example, enter the note number, customer message number, or the PTS number) in the comment field.
You MUST enter new parameters in alphabetical order.
1. EnableJoinHashTableOptimization
You use MaxDB Version 7.8.00 lower than 12, and the error described in Note 1305500 occurs (PTS:1171091). In this case, you can prevent the error by setting the parameter EnableJoinHashTableOptimization to the value NO.2. HashJoinTotalMemorySize and HashJoinSingleTableMemorySize
The memory available to hash-joins is limited by parameters HashJoinTotalMemorySize and HashJoinSingleTableMemorySize.
a) BW systems: The experience in the BW environment shows that the default settings of both parameters in BW systems not sufficient. Therefore, set both parameters in the BW environment as follows:
HashJoinTotalMemorySize = 24000 KB HashJoinSingleTableMemorySize = 4000 KB3. JoinSearchTableThreshold9
See Note 1112049 before you change the parameter JoinSearchTableThreshold9.4. LoadBalancingCheckLevel
You can use this parameter to activate load balancing.
In MaxDB/liveCache versions lower than 7.8, you are permitted to activate load balancing only under very specific circumstances.
(For more information about load balancing, see Note 695721).
As of Version 7.8, you can activate load balancing without restrictions (default setting for new installations). After you upgrade from a version lower than 7.8 to Version 7.8 or higher, the system transfers the pervious setting and does not adjust it. If required, you must activate this manually.5. LogQueues
The parameter LogQueues is used to configure the LOG_IO_QUEUEs. If you set the parameter LogQueues to 0, the number MaxCPUs always determines the number of LOG_IO_QUEUES. If MaxCPUs is greater than 1, the system also implicitly configures more LOG_IO_QUEUEs accordingly.6. MaxExclusiveLockCollisionLoops
The default value (-1) for this parameter ensures that the optimum setting is automatically made by the MaxDB depending on the parameter MaxCPUs. For large systems with MaxCPUs=8 or higher, tests have shown that 10,000 is a good value.7. MaxSQLLocks
See Note 65946 for the correct settings for the parameter MAXLOCKS.8. UpdateStatParallelServerTask
You can use this parameter to raise the number of server tasks that are to be used for parallel update statistics. The default value 0 means that the number of configured data volumes determines the number of server tasks that are used for the parallel update statistics. This default value should not be changed without the recommendation of MaxDB support.9. UpdateStatSampleAlgorithm
This parameter controls the algorithm used to calculate the optimizer statistics. Make sure that the parameter value is set to 1.
For more information about update statistics, see Note 808060: Converting the update statistics estimated values and 927882: FAQ: MaxDB Update Statistics10. UseLobClustering
You can use the parameter UseLobClustering to control whether large LOBs (smaller than 8 KB) are to be saved in blocks (UseLobClustering=YES) to ensure that they can be read with a small amount of I/O.
MaxDB Version 7.8.00 or higher:
As of MaxDB Version 7.8.00., the parameter UseLobClustering should be set to the value YES. (PTS: 1157372)11. UseSelectFetchOptimization:
You can use this parameter to activate the select-fetch optimization (UseSelectFetchOptimization=YES).
The select-fetch optimization should always be activated.
If the select-fetch optimization is deactivated (UseSelectFetchOptimization=NO), the error AK Duplicate Catalog Information (PTS: 1173305) may occur when you use the SQLDBC runtime of versions lower than Version 7.8.00.14.12. UseStrategyCache
MaxDB Version 7.8.00
In all MaxDB builds of all Support Packages, set the parameter
UseStrategyCache to the value NO until further notice. (PTS: 1160910)
13. UseSystemTrigger
UseSystemTrigger should always be set to YES (default). You can deactivate the system trigger only if errors occur when you restart the instance while executing the system trigger, and these errors prevent the restart from being successful.
In this case, contact MaxDB/liveCache Development Support (component: BC-DB-SDB).
UNIX (all):
14. UseFilesystemCacheForVolume
Ensure that the parameter UseFilesystemCacheForVolume, if available, is set to NO. If the value of the parameter on Linux and Unix is set to YES, and if file systems are used for volumes, the file systems must be mounted with direct I/O. The mount options vary depending on the type of file system.15. UseVectorIOSimulation
The parameter should be set to NEVER.

You can change the parameter value for the current database session for database parameters with the CHANGE=RUNNING attribute. Changes to the values for parameters with the attribute CHANGE=OFFLINE only become valid when the database is restarted.
For example:
dbmcli> param_getfull SHAREDSQL
…
CHANGE RUNNING
…
dbmcli> param_put -running -permanent SHAREDSQL <VALUE>
OK

You can also use the DBMCLI command param_getfull to check the current value of the parameter. This is displayed in the row ‘ACTIVEVALUE’.