SAP HANA Execution Engine (HEX) Interview Questions and Answers

SAP HANA Execution Engine (HEX) FAQs

How to control usage of HEX

User can use the following parameter on global level in order to activate or deactivate HEX:

indexserver.ini -> [sql] -> hex_enabled

Whereas on statement level user can use USE_HEX_PLAN and NO_USE_HEX_PLAN hints to activate or deactivate HEX.

HEX available from which Service Package?

The first simple database queries supported by HEX starting with SAP HANA 2.0 SPS 02. Now over the time, many more functionality have been taken from the other engines.

What are the benefits of HEX as compared to previous engines?

Benefits of HEX are:

Different functionalities can be combined into a single engine.
Less resource consumption
HEX has streaming support, therefore, the result in the first row can be produced without processing everything
Hex support code generation for flexible processing (L, LLVM)
Hex has pipelining functionality which reduces the memory consumption and improves cache behaviour

What are the various indications used by HEX in order to process database request?

The modules related to HEX in the call stack of threads which indicates that HEX is used, for e.g:

AttributeEngine::hexLookupInvertedIndex
hex::operators::ConjunctionInitOp::run
hex::operators::FragmentColumnLookupInitOp
hex::operators::FragmentScanInitOp::run
hex::operators::ValidPullInitOp::run
hex::planex::ExecutablePlan::executePipelinesUpTo
hex::planex::ExecutablePlan::open
hex::planex::impl::runNextImpl
hex::planex::NoDataOperator::xf_run
ptime::Hex_search::do_open

What are known issues related to HEX?

The issues with HEX are:

Issue	Details
Bad performance with IN list and additional filter on the same column	If in case both the IN list and another filter (typically NOT) exists for the same column (For e.g. "B IN ( ? , ? ) AND (B <> ?"), it may not be processed systematically, which can cause long runtimes.
Increased heap allocators Pool/L/jit/MetaData and Pool/L/llang/Debuggee	These heap allocators comprise of the L related information and L is utilized by HEX. With SAP HANA <= 2.0 SP 02 the Pool/L/llang/Debuggee allocator is not part of the SAP HANA resource container and so in case of low memory, it cannot be shrunk. Starting with SAP HANA 2.0 SPS 03 this problem is resolved and the allocator will then be a part of the resource container so that it can be reduced when memory is required. As a workaround for SAP HANA 2.0 SPS 02 user can set the following SAP HANA parameters: indexserver.ini -> [execution] -> compilation_strategy = always indexserver.ini -> [execution] -> asynchronous_compilation = false In order to reduce the allocator size with SAP HANA 2.0 SPS 02, user can try clearing the SQL cache. ALTER SYSTEM CLEAR SQL PLAN CACHE User must know that clearing the SQL cache will result in increased parsing requirements and therefore it should only be performed in exceptional situations.
Cancellation of the query in index lookup not possible	An index lookup can at times take very long and cancellation may not be possible

Issue

Details

Bad performance with IN list and additional filter on the same column

If in case both the IN list and another filter (typically NOT) exists for the same column (For e.g. "B IN ( ? , ? ) AND (B <> ?"), it may not be processed systematically, which can cause long runtimes.

Increased heap allocators Pool/L/jit/MetaData
and Pool/L/llang/Debuggee

These heap allocators comprise of the L related information and L is utilized by HEX. With SAP HANA <= 2.0 SP 02 the Pool/L/llang/Debuggee allocator is not part of the SAP HANA resource container and so in case of low memory, it cannot be shrunk. Starting with SAP HANA 2.0 SPS 03 this problem is resolved and the allocator will then be a part of the resource container so that it can be reduced when memory is required.

As a workaround for SAP HANA 2.0 SPS 02 user can set the following SAP HANA parameters:

indexserver.ini -> [execution] -> compilation_strategy = always
indexserver.ini -> [execution] -> asynchronous_compilation = false

In order to reduce the allocator size with SAP HANA 2.0 SPS 02, user can try clearing the SQL cache.

ALTER SYSTEM CLEAR SQL PLAN CACHE

User must know that clearing the SQL cache will result in increased parsing requirements and therefore it should only be performed in exceptional situations.

Cancellation of the query in index lookup not possible

An index lookup can at times take very long and cancellation may not be possible

How to configure HEX?

The following parameters of SAP HANA as shown in the table below can be used to adjust the HEX behaviour:

Parameter	Default	Details
indexserver.ini -> [execution] -> asynchronous_compilation	true	This HANA parameter defines if compilation, once triggered by compilation_strategy whether synchronously or asynchronously, true: happens in the background, execution continues with interpreter until it is done false: current execution waits for compilation
indexserver.ini -> [execution] -> compilation_strategy	on_3rd_execution	This parameter controls while compiling the generated L code. There can be three Possible values are: always: Compile immediately on_3rd_execution: Compile after 3rd execution never: Never compile Until the code has been compiled, for execution, the Llang interpreter will be utilized. Please keep in mind that for on_3rd_execution, the count is applicable only to single operators and not the whole query. Multiple queries using the same compiled plan to contribute to the same count.
indexserver.ini -> [execution] -> recompile_table_size_increase	4	This parameter setting defines the table size factor which triggers an invalidation of the SQL plan and a re-parse.
indexserver.ini -> [sql] -> hex_enabled	true	Utilization of HEX is controlled by this parameter: true: HEX is used for appropriate database requests that already support HEX false: HEX is globally deactivated Note: The HEX should be deactivated temporarily only to resolve the current issues. Once the issues are resolved, it must be enabled again as HEX.

Note: Generally, it is not required to adjust the default settings.

How does plan generation work in HEX?

The images below explain how HEX generates a SQL plan from a database request and also how it executes it:

Prepare: SELECT * FROM T1 JOIN T2 ON T1.X = T2.Y

Executing Prepared Query