HANA details part 3 tech. changes in SPS12
Here are the technical changes with SPS12 in an overview:
|1)||2116157||HANA Consistency Checks and Corruptions||new checks SET_ALERT_SCHEDULE SET_COLLECTOR_SCHEDULE|
|2)||2014148||query result||allocator “Pool/malloc/libhdbcsapi.so”|
|3)||1999997||metadata cache||own allocator|
|4)||2336344||view cache||own allocator Pool/ResultCache|
|5)||2220627||monitoring view||new view M_TABLE_LOB_STATISTICS|
|6)||2101244||MDC parameter||new parameter systemdb_reserved_memory|
|7)||2298750||JVM6 Kerberos||krb5 configuration|
|8)||1999880||replicate parameters||automatically replicate parameters in SR scenarios
|9)||1999880||alert 94||reports increased log replay backlogs
|10)||2169283||Garbage Collection||GARBAGE_LOB_FILES_ table-wise garbage collection
|13)||2057046||auto merge decision
||merge and optimize compression decision functions
|14)||2340450||sync table replication||Optimistic synchronous table replication|
|15)||2339401||Commit in autonomous transaction||Commit in autonomous transaction no longer impacts temporary tables in main transaction|
|16)||2124112||sampling overhead||High sampling overhead|
|17)||2124112||plan sharing type ‘SESSION LOCAL’||more efficient ‘GLOBAL’ sharing type|
|18)||2200772||statement routing decisions||partitioning information in statement routing decisions|
|19)||1999880||initialize SR with binary copy
||initialize a replicated system based on backup / restore|
1) HANA Consistency Checks and Corruptions
CALL CHECK_TABLE_CONSISTENCY('SET_ALERT_SCHEDULE', 'EXECUTION_TIME_OF_DAY', '<hh:mm:ss>')
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', '<hh:mm:ss>')
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.
CALL CHECK_TABLE_CONSISTENCY('SET_COLLECTOR_SCHEDULE', 'INTERVAL', '<seconds>')
Definition of check interval in seconds (e.g. 86400 for daily execution)
2) Query result cache
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
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
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 = <replay_backlog_byte>
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_<volume_id>_ 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:
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:
|Hash partitioning during split batch insert||Split batch not supported||Yes|
|Range partitioning during split batch insert||Split batch not supported||Yes|
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
2222219 – SAP HANA Errors and different Results due to Design Changes
14) – 19)