HANA Migrated calculation views produces different execution plan

[david_repton]

Afternoon,

looking for some assistance in trouble shooting an situation that we currently have.

I have two identical, as far as I can tell, HANA system - DEV and TST.

Running 2.00.020.00.1500920972 (fa/hana2sp02) on SUSE Linux Enterprise Server 12.1, with Kernel Version SUSE Linux Enterprise Server 12.1.

Hana Studio is Version: 2.3.31

The issue we are experiencing is that content, calculation views, migrated via delivery unit produce difference execution plans - I have compared the result of the EXPLAIN STATEMENT against a SELECT from the final view.

The base tables used are identical and I have validated the imported content and am unable to see any differences.

Has anyone experienced anything similar before and if so, what was causing the difference. If not experienced before, any suggestions on how to analyse and determine the difference??

Any help, suggestions or solutions much appreciated as I have exhausted everything I can think of....

Dave

[lbreddemann]

What do you mean by "migrated"? From your description, it sounds like you mean "transported".

If you mean that you transported the calculation view from DEV to PRD and now the execution plan looks different, that might not be a problem. Often, the data in DEV systems is different from PRD (older, less data, ...). Since the optimizer optimizes queries when they get executed (not when they have been written), the difference in data is taken into account.

Note that I'm talking about the actual records in the table, not just the table structure here.

[david_repton]

Thanks Lars,

sorry - transported would be correct. The calculation views were exported and then imported as a delivery unit.

The problem that I have is that the query in the target system runs out of memory and does not complete, where as in DEV works fine. The volumes are very similar, not exactly the same but of the same order of magnitude. eg 5.63m in DEV 5.64m in TEST.

From what I can read of the execution plan, it is referencing the main transaction table more than I would have expected or it is indeed doing in DEV 😞 Can also see more ROW search operations in TST over DEV

Any thoughts?

Dave

[david_repton]

Quick update - have has a bit of time to look further today, not as much as I would like.

Not sure if this has an impact at all but may be useful information...

Looking at the calculation view in _SYS_BIC and selecting Visualise View from the context menu produces exactly the same representation in both DEV and TEST and shows the fact table being pulled in then the dimensions being join in turn.

Have compared data in the base tables and, although there are slight difference in volumes, all is consistent between the two environments - structures identical, no differences in key columns. I have also compared the transported content and can find no difference between the two.

In TEST the query will not execute, causes HANA to hang and returns an out of memory error after several minutes. In DEV the query returns data within about 20-30 seconds - this is via data preview of the execution or the query from the GENERATE SELECT from the context menu.

Again - any thoughts / suggestions on where to look next on this one or things to try greatly appreciated. If any additional information would help happy to add this in 🙂

[lbreddemann]

What you describe really sounds like that the different data sets are at the root of the issue here.

The Visualize View feature is not that useful at that point of analysis as it shows the static representation of the view data flow structure, but not the execution plan.

My gut feel would be to check whether the base tables in both systems show the same kind of storage (row or column) and whether the data is similarly compressed. I've seen quite a couple of performance issues vanish by simply making sure the tables are column store tables with active delta merge.

Beyond these general hints, it's not possible to say much about the issue without actually seeing the explain plan, PlanViz etc.

[GiorgosVas]

Hi guys,

sorry for re-commenting on this old topic.

I was just wondering if there are any chances that this problem could occur without this difference in the underlying data.

I saw the same behavior in the same TEST environment on a dataflow that has been unchanged for more than a year.

Suddenly, execution plan looks a certain way, and performance is terrible. Runs out of memory after a few minutes of running.

The next day, execution plan suddenly looks different and performance ok, everything is working fine.

I tried to track down if there are any changes to the underlying SAP HANA tables used in this dataflow but everything looks the same between these runs.

Any ideas about other possible causes to this issue, except from data differences?