2142945 – FAQ: SAP HANA Hints

1. What is the purpose of hints?

Hints in the context of this SAP Note are instructions for the SAP HANA database server or other components involved in the processing of database requests (e.g. SAP HANA client, SAP ABAP database interface or SAP ABAP DBSL). Hints can influence the way how a database request is generated or processed. They don’t change the result set of the database requests.
Hints are typically used in order to optimize SAP HANA performance or memory consumption. They may be used temporarily for testing purposes or permanently.

2. Which types of hints exist?

The following main hint types exist:

Hint type Details
SAP HANA hints These hints can be used to adjust the request processing on SAP HANA server side, e.g.:

  • IGNORE_PLAN_CACHE: Don’t store the execution plan in the SQL cache
  • USE_OLAP_PLAN:  Prefer OLAP engine over join engine if possible
  • OPTIMIZE_METAMODEL: Suppress the generation of CONCAT attributes in case of multi column joins

See the SAP HANA SQL and System Views Reference (-> “HINT clause”) for more information.
Supported hints depend on the SAP HANA patch level and can be identified by SQL: “HANA_SQL_Hints” (SAP Note 1969700) or by directly accessing the HINTS table.

SAP ABAP hints These hints control the way how SAP ABAP generates database requests, e.g.:

  • &SUBSTITUTE VALUES&: Use literals instead of bind variables
  • &max_in_blocking_factor <n>&: Limit the length of IN lists in the context of FOR ALL ENTRIES to <n>
  • &dbsl_equi_join&: Use an IN list of IN lists instead of OR in the context of FOR ALL ENTRIES

See SAP Notes 129385 and 1987132 for more information.

3. How can hints be specified?

Depending on the context hints can be specified in the following way:

Context Validity Details
Native SQL SAP HANA hints Hints are added at the end of the SQL statement using WITH HINT:

<sql_statement> WITH HINT ( <hint> )

Example:

SELECT * FROM DBSTATC WITH HINT ( IGNORE_PLAN_CACHE )

See the SAP HANA SQL and System Views Reference (-> “HINT clause”) for more information.

ABAP Open SQL  SAP HANA hints
SAP ABAP hints
Hints are added at the end of the SQL statement using %_HINTS <db_selector>. The <db_selector> depends on the SAP kernel. With kernels before 7.40 it is ADABAS, starting with 7.40 it is HDB. Thus, the actual hint syntax depends on the SAP kernel.
SAP kernel < 7.40:

<sql_statement> %_HINTS ADABAS '<hint>'.

SAP kernel >= 7.40:

<sql_statement> %_HINTS HDB '<hint>'.

Example:

SELECT ... %_HINTS HDB '&SUBSTITUTE VALUES&'.

See SAP Note 129385 for more information.

4. What are important hints related to SAP HANA?

Below you can find a subset of available hints which can be particularly useful:

Hint Type Details Scenarios
&SUBSTITUTE VALUES& ABAP Use of literals instead of bind variables Sending literals instead of bind values to the database is in general not recommended, because of parsing and SQL cache overhead (see SAP Note 2124112). In specific situations with a limited amount of different sets of literals and the need of different execution plans depending on the literals it is possible to disable the use of bind variables by setting the &SUBSTITUTE VALUES& hint.
&SUBSTITUTE LITERALS& ABAP Use of literals instead of bind variables for constants The &SUBSTITUTE LITERALS& only sends ABAP constants to the database as literals. For variable values bind variables are used. As a consequence the amount of different WHERE clauses typically remain on a lower level than with &SUBSTITUTE VALUES& and so  the negative effect on parsing and the SQL cache is minimized. Due to the fact that SAP HANA evaluates the literals behind the bind variables during first execution, the effect of this hint is smaller than on other databases where the bind variable content is not known during parsing.
&max_in_blocking_factor <n>& ABAP Limitation of IN list generated by FOR ALL ENTRIES to a maximum length on <n> The SAP profile parameter rsdb/max_in_blocking_factor defines the maximum length of the IN list generated in the context of FOR ALL ENTRIES statements. This scenario is typically used when exactly one field of the WHERE clause refers to the FOR ALL ENTRIES list. For some reasons it can be useful to deviate from the standard, e.g.:

  • Higher values can result in less network roundtrips and reduce the communication overhead.
  • Lower values can result in less SQL statements with only a different IN list length, so that parsing and the SQL cache utilization is optimized.
  • A variation in either direction can help to convince the query optimizer to use a better execution plan.

The default value of rsdb/max_in_blocking_factor is 1024 (SAP Note 1987132). Often it is useful to set the parameter centrally to a lower value in order to minimize parsing and SQL cache overhead (see SAP Note 2124112).
For more information related to FOR ALL ENTRIES parameters see SAP Note 48230.

&max_blocking_factor <n>& ABAP Limitation of OR concatenations generated by FOR ALL ENTRIES to a maximum length on <n> The SAP profile parameter rsdb/max_blocking_factor defines the maximum length of the OR concatenation generated in the context of FOR ALL ENTRIES statements. This scenario is typically used when more than one field of the WHERE clause refers to the FOR ALL ENTRIES list. For some reasons it can be useful to deviate from the standard, e.g.:

  • Higher values can result in less network roundtrips and reduce the communication overhead.
  • Lower values can result in less SQL statements with only a different number of OR concatenations, so that parsing and the SQL cache utilization is optimized.
  • A variation in either direction can help to convince the query optimizer to use a better execution plan.

The default value of rsdb/max_blocking_factor in SAP HANA environments is 50 (SAP Note 1987132).
For more information related to FOR ALL ENTRIES parameters see SAP Note 48230.

&prefer_join_with_fda 1& ABAP
(kernel >= 7.42)
Pushdown of FOR ALL ENTRIES list to SAP HANA via fast data access This FOR ALL ENTRIES implementation is available as of SAP kernel 7.42. A join is created between the FOR ALL ENTRIES list and the database table. The FOR ALL ENTRIES list is sent to the database using the fast data access (FDA) protocol.
This processing can be more efficient than the classic approaches.
&prefer_join 1& dbsl_equi_join ABAP Generation of an IN list of IN lists instead of OR concatenations in case of FOR ALL ENTRIES If multiple WHERE conditions refer to the FOR ALL ENTRIES list, an OR concatenation is used per default. Sometimes OR concatenations are not processed optimally by SAP HANA (see SAP Note2000002). In these cases the dbsl_equi_join hint may be used to switch to an IN list consisting of smaller IN lists. See SAP Notes1662726 and 1622681 for more information and restrictions. The length of generated IN lists is limited by parameter rsdb/max_blocking_factor in this case (not by rsdb/max_in_blocking_factor).
In BW environments the function module RSDU_CREATE_HINT_FAE_HDB is used to generate proper dbsl_equi_join hints for specific BW functionalities (see SAP Note1718930). SAP Notes 1919804, 2020193 and2127008 provide optimizations for this function in SAP BW 7.30 to 7.40 environments in order to avoid terminations and reduce the statement size.
In order to take optimal advantage of the dbsl_equi_join hint in BW, you have to make sure that SAP Notes 2012779 (7.40 SPS 08), 2007363 (7.40 SPS 09), 2020193 (7.40 SPS 08), 2092759 (7.40 SPS 10) and2143880 (7.40 SPS 12) are implemented.
CALC_VIEW_UNFOLDING
NO_CALC_VIEW_UNFOLDING
HANA Activate / deactivate unfolding of calculation views in SQL statements These hints influence the processing of calculation views within SQL statements (see SAP Note 2177965 and SAP internal Note2170436):

  • CALC_VIEW_UNFOLDING: Calculation views are unfolded and processed by SQL engine
  • NO_CALC_VIEW_UNFOLDING: Calculation view are independently processed by attribute engine
CS_EXPR_JOIN
NO_CS_EXPR_JOIN
HANA Prefer / avoid column engine expression joins These hints can be used to influence the handling of expressions. SAP Note 2212330 describes a bug with Rev. 100 and 101 that can produce wrong results and can be bypassed by using the hint NO_CS_EXPR_JOIN.
IGNORE_PLAN_CACHE HANA Bypassing of SQL cache and reparsing for every execution Normally a SQL statement is parsed when it is executed the first time, and the resulting execution plan is stored in the SQL cache. Subsequent executions can then rely on the buffered plan and don’t need a complete reparsing. If the following conditions are met at the same time, IGNORE_PLAN_CACHE can be useful:

  • Depending on the actual bind values different execution plans are optimal.
  • The amount of different bind value combinations in the WHERE clause is not extraordinary high.
  • A parsing overhead (typically not more than a few ms) is acceptable for each execution.

See SAP Note 2000002 for more information.

OPTIMIZE_METAMODEL
NO_OPTIMIZE_METAMODEL
HANA Multi column join processing with CONCAT attribute vs. hash approach Multi column joins can be processed via an internal CONCAT attribute or a hash approach (see SAP Note 1986747). The actual behavior can be controlled with these hints:

  • OPTIMIZE_METAMODEL: Creation / utilization of CONCAT attribute
  • NO_OPTIMIZE_METAMODEL: Hash-based algorithm
ROUTE_BY
ROUTE_BY_CARDINALITY
ROUTE_TO
NO_ROUTE_TO
HANA Statement routing control As of Rev. 73 these hints can be used to control statement routing in scale-out scenarios. See SAP Note 2200772 for more information.
USE_OLAP_PLAN
NO_USE_OLAP_PLAN
HANA Activate / deactivate use of OLAP engine for column searches Certain database requests can be executed by either the OLAP engine (USE_OLAP_PLAN) or the join engine (NO_USE_OLAP_PLAN). Depending on the chosen exection plan there are situations where one engine has significant disadvantages compared to the other. Using the hints USE_OLAP_PLAN and NO_USE_OLAP_PLAN it is possible to influence the choice of the engine used for column searches.
The hint OLAP_PARALLEL_AGGREGATION can also be used to prefer the OLAP engine, but with new codings you should use USE_OLAP_PLAN whenever possible.
SAP Note 1734002 describes how BW can be configured (e.g. RSADMIN parameter HDB_JOIN_ENGINE_QUERY or BW execution mode) to implicitely make use of these parameters.

5. Which problems and pitfalls exist in the area of hints?

When using hints you should consider the following problems and pitfalls:

Problem Details
Different ABAP database selector name depending on SAP kernel version When specifying hints in ABAP it depends on the SAP kernel version which database selector has to be used for SAP HANA:

  • SAP kernel < 7.40: ADABAS
  • SAP kernel >= 7.40: HDB

As a consequence hints with ADABAS stop working when upgrading to SAP kernel >= 7.40 and the performance may degrade. In order to fix this issue you have to specify HDB as database selector:

  • <sql_statement> %_HINTS HDB ‘<hint>’.

The following SAP Notes introduce the HDB hint in the SAP standard coding via corrections:

  • SAP Note 2012779 (“Incorrect SAP HANA DB hint during NLS lookup”), fixed with SAPKW74008
  • SAP Note 2007363 (“740SP9: Master data update – Performance optimization for HANA”), fixed with SAPKW74009
  • SAP Note 2092759 (“Performance in class CL_RRHI_INCL_CREATOR_TID during data load”), fixed with SAPKW74010
  • SAP Note 2143880 (“Method CL_RSDM_READ_MASTER _DATA->_SID_DIRECT_READ and less than optimum database accesses”), fixed with SAPKW74012
Problems with RSDU_CREATE_HINT_FAE_HDB The BW wrapper function RSDU_CREATE_HINT_FAE_HDB for hint generation tends to create very long SQL statements.
In order to come around problems, you should consider the following SAP Notes:

  • SAP Note 2020193: Elimination of terminations due to exceeded statement limit. Typical errors are:
DBSQL_STMNT_TOO_LARGE / DBSQL_SQL_ERROR

SQL error 589: too many parameters are set: # of params: 32768

too many input values (32768) - maximum is 32767
  • SAP Note 2127008: Reduction of SQL cache space requirements
Problems with hint OLAP_PARALLEL_AGGREGATION The SAP HANA hint OLAP_PARALLEL_AGGREGATION can cause problems:

  • SAP Note 2086261: Risk of wrong results with negated predicate and ESCAPE (Revisions < 82)

When feasible it is recommended to disable the OLAP_PARALLEL_AGGREGATION hint or use the hint USE_OLAP_PLAN instead.

Behavior in case of invalid hints While other databases typically ignore invalid hints, SAP HANA fails with the following error:

SQL error 468: hint error: invalid hint

Therefore you have to pay attention that correct hints are specified.
If you specify database specific SAP ABAP hints, no problem will happen, because the specified hints will only be sent to the database(s) indicated with the database selector.

dbsl_equi_join restrictions The dbsl_equi_join hint can only be used in restricted contexts (see SAP Notes 1662726 and 1622681). The following main limitations exist:

  • Only works in combination with &prefer_join 1& hint
  • The WHERE conditions referring to the FOR ALL ENTRIES list need to be next to each other
  • Only works in combination with “=” operators
  • SAP kernel < 7.40: Not possible in combination with joins, only single tables and views allowed
Patch level dependent SAP HANA hint support Not every available SAP HANA hint is supported by every SAP HANA patch level. If you use an unsupported hint, different problems are possible:

  • Terminations if hint is considered invalid
  • Unexpected effects and instabilities

Therefore make sure that you only use supported hints. See the SAP HANA SQL and System Views Reference (-> “HINT clause”) for more information.
Supported hints can also be identified by SQL: “HANA_SQL_Hints” (SAP Note 1969700) or by directly accessing the HINTS table.

 

Leave a Reply