HANA details part 3 tech. changes in SPS12

1) HANA Consistency Checks and Corruptions

CALL CHECK_TABLE_CONSISTENCY('SET_ALERT_SCHEDULE', 'EXECUTION_TIME_OF_DAY', '')

Specify a specific time of day (local server time) for the alert check (SAP HANA >= SPS 12)

CALL CHECK_TABLE_CONSISTENCY('SET_COLLECTOR_SCHEDULE', 'EXECUTION_TIME_OF_DAY', '')

Specify a specific time of day (local server time) for the collector (SAP HANA >= SPS 12). This setting is applied only if the INTERVAL 1,2,3,4,6,8, 12 or 24 hours is used.

This means:

CALL CHECK_TABLE_CONSISTENCY('SET_COLLECTOR_SCHEDULE', 'INTERVAL', '')

Definition of check interval in seconds (e.g. 86400 for daily execution)

2) Query result cache

Allocator: “Pool/ResultCache”
The result cache was introdruced with SPS06 and also caches view results.
Up to SPS 12 the allocator “Pool/malloc/libhdbcsapi.so” is also used when the query result cache is configured (indexserver.ini -> [cache] -> query_result_cache = enabled; SAP Note 2014148). With newer patch levels dedicated result cache allocators (Pool/ResultCache) are used instead.

3) Metadata cache

Allocator: “Pool/Metadata/MetadataCache/MetadataGlobalCacheSlot”
The metadata cache allocator was introduced with SPS 12 and is used to store metadata locally that otherwise has to be retrieved from a remote SAP HANA node. It can grow significantly if many DDL operations are executed, because DDL operations invalidate existing cache entries.
Before SPS 12 the metadata information was stored in temporary row tables and so the allocator Pool/RowEngine/RSTempPage was used.

Known issues in 120 – 122.03 => fixed with 122.04+

4) View cache

Allocator: “Pool/ResultCache”
This allocator stores view cache information with SPS 12 and higher. The view cache is available as of SPS 11. It is also known as result cache extension and allows to cache results on SQL views, calculation views and CDS views. This allows to get the result without repeated overhead.

The view cache can provide the following advantages:

• Reduction of CPU consumption
• Reduction of SAP HANA thread utilization
• Performance improvements

5) Monitoring view for LOBs

New monitoring view: M_TABLE_LOB_STATISTICS
LOBs (large objects) are columns that are used to store a large amount of unstructured data such as text documents and images.

Starting with SPS 12 you can consider using M_TABLE_LOB_STATISTICS as a light-weight variant for M_TABLE_LOB_FILES that doesn’t show the high memory requirements.

6) MDC parameter

Parameter: global.ini -> [multidb] -> systemdb_reserved_memory
This parameter allows you to configure a minimal amount of memory (in MB) to be exclusively used by the multitenant database container system database.

7) JVM6 Kerberos

The default JVM 6 encryption configuration for Kerberos is deprecated and cannot be used with the new KRB5 version in SPS 12. For JVM 6 therefore, you need to configure a krb5.ini file (default file for Windows) or krb5.conf file (default file for Linux).

For more details for each topic just follow the corresponging note.

8) replicate parameters

Starting with SPS 12 it is possible to automatically replicate parameters from the primary to the secondary site by activating the following parameter:

global.ini -> [inifile_checker] -> replicate = ‘true’

Before SPS12 you have to configure every side for itself. If you have done a mistake you will get an alert for a parameter mismatch. With this feature this is not necessary any more.

9) alert 94

Starting with SAP HANA SPS 12 alert 94 (“Log replay backlog for system replication secondary”) reports increased log replay backlogs.
SQL: “HANA_Replication_SystemReplication_KeyFigures” displays among others the log replay backlog (REPLAY_BACKLOG_MB).

As a fallback option you can use the contents of M_SERVICE_REPLICATION to determine the log replay delay on the secondary site:

SELECT SHIPPED_LOG_POSITION, REPLAYED_LOG_POSITION FROM M_SERVICE_REPLICATION

Now you can calculate the difference and multiply it with the log position size of 64 byte:

(SHIPPED_LOG_POSITION – REPLAYED_LOG_POSITION) * 64 =

10) Garbage Collection

Up to SPS 11 the table GARBAGE_LOB_FILES_ is defined as meta data, so that table-wise garbage collection isn’t possible.
Starting with SPS 12 adjustments were done and the problem no longer exists.
Now SYS tables with the naming convention RS_LOB_GARBAGE__ are used for that purpose.

11) Merge I

If a column is loaded and unloaded purely for merge reasons, you will find the unload reason in column REASON of monitoring view M_CS_UNLOADS MERGE as of SPS 12.
See SAP Note 2057046 for more information related to SAP HANA delta merges. In former times you couldn’t find out the root cause for the unload/load of the table.

12) Merge II

Up to SAP HANA Rev. 121 critical merges are registered with MOTIVATION = ‘AUTO’ in views like M_DELTA_MERGE_STATISTICS and HOST_DELTA_MERGE_STATISTICS. Therefore you can see a merge categorized as ‘AUTO’ when a critical merge was executed (e.g. because no successful merge happened within the last 7 days).

Starting with SAP HANA Rev. 122 critical merges are recorded with MOTIVATION = ‘CRITICAL’ in M_DELTA_MERGE_STATISTICS.

13) auto merge decision

A bug with SAP HANA Rev. 120 to 122.04 can result in permanent merges of specific tables because UDIV gaps (that can only be eliminated with optimize compression) are erroneously considered as deleted main rows and so the decision function may indicate the need for a merge on a permanent basis.
As a workaround the merge and optimize compression decision functions are suggested for Rev. 120 to 122.04 => you will find it in the corresponding note.

14) Optimistic synchronous table replication

The synchronous table replication allows to replicate a table to one or several replicas synchronously. This means that the replicas are always in sync with the master table. Up to SPS 11 only the asynchronous table replication was able to use. This means the usual replication delay is significantly smaller than 1 s, but in case of overload or resource bottlenecks it can be much higher.

There have also been earlier implementations of synchronous table replication. They are deprecated as of SAP HANA SPS 12.
See SAP Note 2385974 for more details.

There is also a new monitoring view: M_TABLE_REPLICAS

15) Commit in autonomous transaction

Previously the content of temporary tables related to the main transaction was accessible by autonomous transactions. This has been corrected and so you have to materialize the content of a temporary table in a variable if you want to access it from an autonomous transaction.

16) High sampling overhead

SAP HANA calculates certain statistics ad-hoc during parsing. By optimizing any of the known issues prior SPS12 (see note 2124112), also this overhead should reduce, because less parses are required. In special situations, you can consider adjusting the statistics sampling behavior.

Starting with SPS 12 you can adjust the amount of samples for a particular SQL statement using the ESTIMATION_SAMPLES hint (SAP Note 2142945).

For example, the following hint will completely disable sampling for the query:

ESTIMATION_SAMPLES(0)

17) More efficient ‘GLOBAL’ sharing type

SQL plans with sharing type ‘SESSION LOCAL’ can exclusively be used by the connection that has created the SQL plan. As a consequence various plans for the same SQL statement may exist in the SQL cache at the same time and additionally parsing of the same plan is done various times for different connections. In case of complex SQL statements parsing can take significant time and so the performance can be impacted.

As of SAP HANA SPS 12 statements using global temporary tables with a defined location will use the more efficient ‘GLOBAL’ sharing type.

18) partitioning information in statement routing decisions

Partitioning information can be included in the statement routing decisions. With SAP HANA SPS 12 the following options exist:

19) initialize a replicated system based on backup / restore

Up to SPS 11 it is only possible to initialize the replication side based on an snapshot that is created via full data shipping from primary side. An initialization via backup and restore is not possible.

Starting with SPS 12 you can initialize the secondary with a binary copy (via storage snapshot or while primary site is stopped) from the primary site.

…) to be continued

Appendix

2222219 – SAP HANA Errors and different Results due to Design Changes

####################
Main blog
Shared memory
Heap memory
####################

####################
Update 1.1
####################
14) – 19)
Appendix