wrong execution plan used

[SAPSupport]

current PERSON_NAME revision is 2.00.072.

In production system we see queries usng the EXECUTION_ENGINE "COLUMN, ROW" instead of prefered "HEX" engine

With using the hint " ...with hint (USE_HEX_PLAN); " the query execution is much faster and using less memory

adding both planviz data, statement execution history and FSID to this ticket

I know , with PERSON_NAME SPS08 there are some bugs fixed in regards to HEX-usage, please check if this will be covered already, or an additional optimizer correction is required

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

For your question, use or do not use a DB engin like HEX that is not recognised as a real DB issue, that basically is HANA optimization operation. 

Optimization is based on estimation both size and cost, when creating the plan, we do sampling some part of table. 

Based on sampling records, we do calculate # input & output records on join etc, 
and try to calculate the cost when using Join engine / Olap engine / HEX engine,
if the cost is low, optimizer will choose it as final plan.

So I assume that the original plan was generated was chosen because the cost was lower than using current one.

As mentioned, some of estimation might be not accurate as our calculation is based on estimation and some sampled data doesn't represent the whole data distribution.

As a result, it might lead to the wrong choice of execution engine.
 
HANA team are trying to mitigate the gap between estimation and the real data distribution with the newer HANA DB version as you may know.

However, there are too many variances which affect these gaps in practical way.