Is the default auto merge decision function already optimal?
The default auto merge decision function has changed a few times. Regardless of these changes it tends to merge small tables with large delta storages too rarely with SPS 09 and below. For example, an auto merge is typically not triggered for a table with 20 MB main storage and 90 MB delta storage, because the threshold for delta storage sizes is 100 MB or higher. As a consequence the memory allocation is higher than necessary and the performance can suffer.
In order to eliminate this issue, you can adjust the auto_merge_decision_func parameter by adding the following condition after the first bracket in its value:
DRC * TMD > 3600 * ( MRC + 0.0001 ) or
This formula compares the delta row count with the main row count and additionally takes the time since the last merge into account, so that e.g. a small table with the same number of records in main and delta storage will typically be merged after 1 hour.
Example (Rev. 82):
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('mergedog','auto_merge_decision_func')= '(DRC*TMD>3600*(MRC+0.0001) or (THM>=256000 and (((DMS>100 or DCC>100 or DLS>1000) and DRC>MRC/100) or (DMR>0.2*MRC and DMR>0.001))) or (THM<256000 and (DMS>50 or DCC>8 or DLS>100))) and (DUC<0.1 or 0.05*DRC>=DUC)' WITH RECONFIGURE
Be aware that you should always take the default value for the auto merge decision function in the system as a basis and only add the additional term at the beginning. If you upgrade to a later SAP HANA revision, you should revisit this parameter setting and adjust it if the default has changed. With SPS 10 and higher the default parameter value has been adjusted so that also small tables are properly considered. Thus, you have to remove this special configuration when you uses SPS 10 or higher.
Sign up for STechies
Ask Question From Our Experts
All the site contents are Copyright © www.stechies.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies. The site www.stechies.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.stechies.com or the content authors.