Delta merge statistics -A deeper insights

DELTA MERGE BASICS and STATISTICS:

SECTION 1:Delta merge complex facts:

Delta merge is the merging of write optimized delta area of any column table with read optimized highly compressed main memory. Delta memory is always table specific and not common for all column store table.

In order to understand the working of HANA and its delta merge concepts, it is important to know the below facts without any doubts . Hence below are some of the question which will help on this regard which I have framed. Hope it helps .

1.Is delta merge valid for both row store and column store ?

Delta merge is only valid for column store and is not valid for row store . To make inserts easier into a highly compressed main memory, during data changes, the column store table gets decompressed and are copied to write optimized delta storage . Till the delta memory gets merged with main memory, all read operation has to happen in both delta and main storage by using MVCC concept .

2.Does delta merge is specific to table or entire DB?

Delta merge is a table specific operation that happens inside memory where contents of delta memory gets merged back with its main memory.

3.A failed delta merge does not mean a rolled back transaction . why?

If we have performed an insert to a column table and the transaction has been committed ,we will be able to see updated values during the next read operation even before delta merge for the respective table has happened with the help of MVCC concept in HANA. Just that the changes is still sitting in write optimized delta storage and has not been merged with read optimized main storage inside memory . This also means that an entry in log segments has already been written and just that the data has not also reached data volume .

Ie, The deltamerge takes place in memory. Updates on column store tables are stored in the in-memory delta storage. During the delta merge these changes are compressed and applied to the in-memory main storage optimized for reads. Right after the delta merge, the new main storage is persisted to the data volume. The delta merge does not block parallel read and update transactions

4.Where does a committed change gets recorded in HANA? Data volume or log volume ?

Each committed changes gets recorded in log volumes until the next successful savepoint. Hence a failed savepoint does not mean a failed transaction . When a transaction is committed in memory a corresponding log entry gets generated synchronously making the transaction successful. Once a save point is reached , these changes from memory gets flushed to data volume , asynchronously .

5.Why do we need extra space at OS level during a delta merge operation ?

During a Delta Merge the affected tables are temporarily duplicated on disk for a short period of time.

6.When does data gets persisted from memory to data volumes?

Data gets persisted from memory to data volume when ever a save point is triggered or snapshot is created or a delta merge is performed

7.What are the contents of log volumes ?

The log volume contains all changes on the data volume since the last completed savepoint. Each log volume contains the file logsegment_<partition_ID>_directory.dat and one or more log segment files (logsegment_<partition_ID>_<segment_number>.dat).

8.Does data transfer happens between log volume and data volume ?

NO.After a transaction commit log segments gets saved in log volumes synchronously from memory and after a savepoint data from memory gets flushed out to data volume . Any consistent changes that has not reached data volume will be in log segments till a savepoint.

For more basic information check OSS note 2057046 – FAQ: SAP HANA Delta Merge

SECTION 2:DELTA MERGE STATISTICS:

If you wanted to know when a delta merge happened for a  table(Column store ) in HANA DB last time , you can do the same via below SQL.

select * from M_DELTA_MERGE_STATISTICS where TABLE_NAME='<TABLE_NAME>’ and TYPE=’MERGE’

You can also use table HOST_DELTA_MERGE_STATISTICS for finding out historic delta merge statistics.

If the column MOTIVATION is AUTO and SUCCESS=’TRUE’ it means that the delta merge was auto triggered for this table and it was successful. In the column MERGED_DELTA_RECORDS gives the number of rows that got merged during this delta merge operation for this table. MOTIVATION column can hold values like AUTO (auto triggered by HABA), SMART(Triggered by SAP application, mostly valid for BW),CRITICAL(Regularly in order to make sure that modified tables are merged from time to time, even if no auto or smart merge is configured), HARD(MANUAL via SQL=> MERGE DELTA OF <SCHEMA_NAME>.”<TABLE_NAME>” ;)

Delta merge in my own simple terms is a way of merging back data that were modified in write optimized delta memory area to read optimized main memory .(This operation happens fully inside memory)

The delta storage exists only in memory. Only delta log entries are written to the persistence layer when delta entries are inserted.

Sample Screen:

Delta merge job for any table can be monitored via below

Thanks for reading!
Like and leave a comment if it was useful!
Follow for more such posts! 🙂