Tuesday, February 2, 2016

2000002 - FAQ: SAP HANA SQL Optimization

Symptom
SQL statements run for a long time or consume a high amount of resources in terms of memory and CPU.


Environment
SAP HANA


Cause
1. Where do I find information about SQL statement tuning provided by SAP?
2. Which indications exist for critical SQL statements?
3. Which prerequisites are helpful for SQL statement tuning?
4. How can I identify a specific SQL statement?
5. What is an expensive SQL statement?
6. How can time information in the SQL cache (M_SQL_PLAN_CACHE) be interpreted?
7. How can I determine the most critical SQL statements?
8. How can I determine and interpret basic performance information for a particular SQL statement?
9. Which options exist to understand the execution of a SQL statement in detail?
10. What are typical approaches to tune expensive SQL statements?
11. Are secondary indexes required to provide an optimal performance?
12. Which advanced features exist for tuning SQL statements?
13. Are there standard recommendations for specific SQL statements available?
14. Is it required to create optimizer statistics in order to support optimal execution plans?
15. Are all database operations recorded in the SQL cache (M_SQL_PLAN_CACHE)?
16. Can sorting be supported by an appropriate index?
17. Is it possible to capture bind values of prepared SQL statements?
18. How can the performance of INSERTs and data loads be tuned?
19. Why are there significant differences between SQL statements on ABAP and SAP HANA side?
20. How does the EXPLAIN functionality work?


Resolution

1. Where do I find information about SQL statement tuning provided by SAP?

The SAP HANA Troubleshooting and Performance Analysis Guide contains detailed information about tuning SQL statements.
If you want to optimize ABAP coding for SAP HANA, you can refer to the blog Performance Guidelines for ABAP Development on SAP HANA.

2. Which indications exist for critical SQL statements?

The following SAP HANA alerts indicate problems in the SQL area:
Alert Name SAP Note  Description
39 Long-running statements 1977262 Identifies long-running SQL statements.
SQL: "HANA_Configuration_MiniChecks" (SAP Notes 1969700, 1999993) returns a potentially critical issue (C = 'X') for one of the following individual checks:
Check ID Details
1110 SQL using in average > 1 connection (last day)
1112 SQL using in average > 1 thread (last hour)
1113 SQL using in average > 1 thread (last day)
1115 Longest running current SQL statement (s)
1118 Longest running current job (s)
1120 Exp. stmt. trace: SQL running > 1 h (last day)
1160 Average COMMIT time (ms)

3. Which prerequisites are helpful for SQL statement tuning?

See SAP Note 2000000 for more information about an optimal SAP HANA performance configuration and useful prerequisites for performance and SQL statement optimization.

4. How can I identify a specific SQL statement?

Quite obviously a specific SQL statement can be identified by its SQL text. Due to the fact that the text can be quite long and there can be different similar SQL texts it is useful to identify a SQL statements based on a hash value that is derived from the SQL text. This hash value is called statement hash and can be found in column STATEMENT_HASH of SAP HANA performance views like M_SQL_PLAN_CACHE, M_EXPENSIVE_STATEMENTS or M_SERVICE_THREAD_SAMPLES.
The statement ID (column STATEMENT_ID) can't be used for that purpose as it is based on a connection ID and not on a SQL statement text.

5. What is an expensive SQL statement?

An expensive SQL statement is a database access that shows high values in areas like:
  • High execution time: Usually most important in the area of performance analysis are SQL statements with a particular high overall runtime. Usually the overall runtime counts, so it doesn't matter if a statement is executed 1 time with a duration of 1000 seconds or if it is executed 1 million times with an average duration of 1 ms, because in both cases the statement is responsible for a total duration of 1000 seconds.
  • High memory utilization
  • High CPU consumption
  • High lock wait time

6. How can time information in the SQL cache (M_SQL_PLAN_CACHE) be interpreted?

Tables like M_SQL_PLAN_CACHE, M_SQL_PLAN_CACHE_RESET and HOST_SQL_PLAN_CACHE contain several time related columns and it is not always clear how to interpret them:
Time column type Description
CURSOR Contains the overall cursor time including SAP HANA server time and client time
If the client performs other tasks between fetches of data, the cursor time can be much higher than the SAP HANA server time.
This can result in MVCC issues because old versions of data need to be kept until the execution is finished.
EXECUTION Contains the execution time (open + fetch + lock wait + close) on SAP HANA server side, does not include table load and preparation time
EXECUTION_OPEN Contains the open time on SAP HANA server side
Includes the actual retrieval of data in case of column store accesses with early materialization
EXECUTION_FETCH Contains the fetch time on SAP HANA server side
Includes the actual retrieval of data in case of row store accesses or late materialization
EXECUTION_CLOSE Contains the close time on SAP HANA server side
TABLE_LOAD Contains the table load time during preparation, is part of the preparation time
PREPARATION Contains the preparation time
LOCK_WAIT Contains the transaction lock wait time, internal locks are not included
Usually long EXECUTION_OPEN or EXECUTION_FETCH times are caused by retrieving the data.
From a SQL tuning perspective the most important information is the total elapsed time of the SQL statement which is the sum of preparation time and execution time.

7. How can I determine the most critical SQL statements?

For a pure technical SQL optimization it is useful to determine the most expensive SQL statements in terms of elapsed time and check the result list top down. The most expensive SQL statements in the SQL cache can be determined in the following ways:
  • M_SQL_PLAN_CACHE / HOST_SQL_PLAN_CACHE
  • SAP HANA Studio -> Administration -> Performance -> SQL Plan Cache
  • ABAP: DBACOCKPIT -> Performance -> SQL Plan Cache
  • SQL: "HANA_SQL_SQLCache" (SAP Note 1969700)
  • Solution Manager self service "SQL Statement Tuning" (SAP Note 1601951)
Additionally expensive SQL statements can be captured by trace features:
Trace Environment  SAP Note Detail
ST05 ABAP   SQL trace
ST12 ABAP   Single transaction analysis
SQLM ABAP 1885926 SQL monitor
SQL trace SAP HANA Studio, SQL, ABAP (DBACOCKPIT) 2031647 SQL trace
Expensive statement trace SAP HANA Studio, SQL, ABAP (DBACOCKPIT) 2180165 Expensive statement trace
If you are interested in the top SQL statements in terms of memory consumption, you can activate both the expensive statement trace and the statement memory tracking (SPS 08 or higher, see SAP Note 1999997 -> "Is it possible to limit the memory that can be allocated by a single SQL statement?") and later on run SQL: "HANA_SQL_ExpensiveStatements" (SAP Note 1969700) with ORDER_BY = 'MEMORY'.
The currently running SQL statements can be determined via SQL: "HANA_SQL_ActiveStatements" (SAP Note 1969700).
In all cases you get a list of SQL statements / STATEMENT_HASH values that subsequently be analyzed in more detail.

8. How can I determine and interpret basic performance information for a particular SQL statement?

The following SQL statements available via SAP Note 1969700 can be used to collect further details for a given STATEMENT_HASH (to be specified in "Modification section" of the statements):
SQL command Details
SQL: "HANA_SQL_StatementHash_BindValues" Captured bind values from SQL cache in case of long running prepared SQL statements (SPS 08 and higher)
SQL: "HANA_SQL_StatementHash_KeyFigures" Important key figures from SQL cache, for examples see below
SQL: "HANA_SQL_StatementHash_SQLCache" SQL cache information (current and / or historic) like executions, records, execution time or preparation time
SQL: "HANA_SQL_StatementHash_SQLText" SQL statement text
SQL: "HANA_SQL_ExpensiveStatements" Important key figures from expensive statement trace (SAP Note 2180165)
SQL: "HANA_SQL_ExpensiveStatements_BindValues" Captured bind values from expensive statement trace (SAP Note 2180165)
Below you can find several typical output scenarios for SQL: "HANA_SQL_StatementHash_KeyFigures".

Scenario 1:  Transactional lock waits


We can see that nearly the whole execution time is caused by lock wait time, so transactional lock (i.e. record or object locks) are responsible for the long runtime. Further transactional lock analysis can now be performed based on SAP Note 1999998.

Scenario 2: High number of executions


An elapsed time of 0.55 ms for 1 record is not particularly bad and for most of the SQL statements no further action is required. In this particular case the number of executions is very high, so that overall the execution time of this SQL statement is significant. Optimally the number of executions can be reduced from an application perspective. If this is not possible, further technical analysis should be performed. Really quick single row accesses can be below 0.10 ms, so there might be options for further performance improvements (e.g. index design or table store changes).

Scenario 3: High elapsed time


An execution time of 284 ms for retrieving one row from a single table is definitely longer than expected and it is very likely that an improvement can be achieved. Further analysis is required to understand the root cause of the increased runtime.

Scenario 4: High elapsed time for DML operations, no lock wait time


An elapsed time of 10 ms for inserting a single record is quite high. If DML operations have an increased elapsed time it can be caused by internal locks that are e.g. linked to the blocking phase of savepoints. Further internal lock analysis can now be performed based on SAP Note 1999998.

Scenario 5: Many records


Reading about 200,000 records in less than 2 seconds is not a bad value. In the first place you should check from an application perspective if it is possible to reduce the result set or the number of executions. Apart from this there are typically also technical optimizations available to further reduce the elapsed time (e.g. delta storage or table store optimizations).

Scenario 6: High Preparation Time


Preparation time is linked to the initial parsing and compilation. You can reduce this overhead by using bind variables rather than literals so that several SQL statements with the same structure need to be parsed only once. Furthermore you can perform a more detailed analysis to understand why the preparation step is taking longer than expected.

9. Which options exist to understand the execution of a SQL statement in detail?

In order to understand how a SQL statement can be tuned it is essential to know how it is processed. For this purpose you can use the following tools:
Tool Details
Explain High-level information for SQL execution (e.g. joins, used indexes)
  • DBACOCKPIT: Diagnostics -> Explain
  • SAP HANA Studio: SQL console -> Explain plan (right mouse click)
  • SQL: "EXPLAIN PLAN FOR ..." and subsequent evaluation of explain plan table via SQL: "HANA_SQL_ExplainPlan" (SAP Note 1969700)
PlanViz Detailed graphical insight in SQL execution
  • DBACOCKPIT: Diagnostics -> Explain -> Execution trace
  • SAP HANA Studio: SQL console -> Visualize plan (right mouse click) -> Execute
Thread sample analysis High-level thread state and lock type information (e.g. useful in case of waits for internal locks which are not reflected in the "lock wait time")
  • SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" (SAP Note 1969700)
Performance trace Detailed insight in SQL execution including SQL plan and function profiling.
See SAP Note 1787489 for more details.
User-specific trace Granular trace information for configurable components of the SQL statement execution
  • SAP HANA Studio: Administration -> Trace configuration -> User-Specific Trace
Trace components depend on individual scenario, usually only one component is activated, most common components are:

10. What are typical approaches to tune expensive SQL statements?

Depending on the results of the analysis of an SQL statement the following optimizations can be considered.
Possible symptoms Optimization Optimization Details
High number of executions Reduce high number of executions Check from application perspective if the number of executions can be reduced, e.g. by avoiding identical SELECTs or adjusting the application logic.
High number of selected records Reduce number of selected records Check from application perspective if you can restrict the number of selected records by adding further selection conditions or modifying the application logic.

Check if you can reduce the amount of relevant data by archiving or cleanup of basis tables (SAP Note 706478).
High lock wait time due to record locks
Reduce record lock contention Check from application perspective if you can reduce concurrent changes of same records.

Check from application perspective if you can reduce the critical time frame between change operation and next COMMIT.
See SAP Note 1999998 for more information.
High lock wait time due to object locks Reduce object lock contention Check if you can schedule critical offline DDL operations less often or at times of lower workload.

Check if you can use online instead of offline DDL operations.
See SAP Note 1999998 for more information.
High total execution time, significant amount of thread samples pointing to internal lock waits Reduce internal lock contention Check if you can reduce the internal lock wait time (SAP Note 1999998).
Execution time higher than expected, optimal index doesn't exist
Table and column scan related thread methods and details like searchDocumentsIterateDocidsParallel, IndirectScanBvOutJob<range>, IndirectScanVecOutJob<range>, sparseSearch, SparseScanRangeVecJob
Synchronize index and application design Check from an application perspective if you can adjust the database request so that existing indexes can be used efficiently.

Create a new index or adjust an existing index so that the SQL statement can be processed efficiently:
  • Make sure that selective fields are contained in the index
  • Use indexes that don't contain more fields than specified in the WHERE clause
Execution time higher than expected, negative impact by existing partitioning Synchronize partitioning and application design Consider the following possible optimization approaches:
  • Make sure that existing partitioning optimally supports the most important SQL statements (e.g. via partition pruning, load distribution and minimization of inter-host communication). See SAP Note 2044468 for more information.
  • For technical reasons partitioning can sometimes introduce performance overhead in combination with join engine accesses. In this case you can check if it is possible to eliminate the use of the join engine, e.g. by removing a DISTINCT aggregation.
  • If it is technically possible to disable partitioning, you can consider undoing the partitioning.
Long runtime with OR condition having selection conditions on both tables Avoid OR in this context If an OR concatenation is used and the terms reference columns of more than one table, a significant overhead can be caused by the fact that a cartesian product of both individual result sets is required in some cases.
If you face performance issues in this context, you can check if the problem disappears with a simplified SQL statement accessing only one table. If yes, you should check if you can avoid joins with OR concatenated selection conditions on both tables.
Execution time higher than expected, significant portion for accessing delta storage Optimize delta storage Make sure that the auto merge mechanism is properly configured. See SAP Note 2057046 for more details.
Consider smart merges controlled by the application scenario to make sure that the delta storage is minimized before critical processing starts.
Execution time slightly higher than expected Change to row store In general the number of tables in the row store should be kept on a small level, but under the following circumstances it is an option to check if the performance can be optimized by moving a table to the row store:
  • Involved table located in column store and not too large (<= 2 GB)
  • Many records with many columns selected or a very high number of quick accesses with small result sets performed
Execution time sporadically increased Avoid resource bottlenecks Check if peaks correlate to resource bottlenecks (CPU, memory, paging) and eliminate bottleneck situations.
Execution time higher than expected, significant portion for sorting (trex_qo trace: doSort) Optimize sorting Sort operations (e.g. related to ORDER BY) are particularly expensive if all of the following conditions are fulfilled:
  • Sorting of a high number of records
  • Sorting of more than one column
  • Leading sort column has rather few (but more than 1) distinct values
In order to optimize the sort performance you can check from an application side if you can reduce the number of records to be sorted (e.g. by adding further selection conditions) or if you can put a column with a high amount of distinct values at the beginning of the ORDER BY)
Long BW query runtime
Long execution time of TREXviaDBSL calls
Long execution time of TREXviaDBSLWithParameter calls
Optimize BW queries If you aren't aware about the actual query, yet, get in touch with the application team to understand which actual application operations result in long running TREXviaDBSL calls and start the analysis from that perspective. When you are able to reproduce the issue, more detailed traces can be activated as required.
BW allows executing queries in different execution modes which control the utilization of SAP HANA internal engines. Furthermore it is possible and usually recommended to convert infocubes to the HANA optimized type. See BW on HANA and the Query Execution Mode and SAP Notes 1682992 and 1734002 for further information and check if modifying the execution mode and / or the infocube type improves the performance of BW queries. See SAP Note 2016832 that describes how to adjust the query execution mode.
Long BW DTP and transformation runtime using SAP HANA execution mode Optimize BW DTP and transformation setup See SAP Notes 2033679 (BW 7.40 SP 05 - 07) and 2067912 (BW 7.40 SP 08 - 10) in order to make sure that all recommended fixes are implemented. For example, SAP Note 2133987 provides a coding correction for SAPKW74010 in order to speed up delta extractions from a DSO.
See SAP Note 2057542 and consider SAP HANA based transformations available as of BW 7.40 SP 05.
Long runtime of FOR ALL ENTRIES query Adjust FOR ALL ENTRIES transformation If a FOR ALL ENTRIES selection in SAP ABAP environments takes long and consumes a lot of resources, you can consider adjusting the way how it the database requests are generated.
  • If multiple columns in the WHERE condition reference the FOR ALL ENTRIES list and a SQL statement based on OR concatenations is generated, you can use the DBSL hint dbsl_equi_join as described in SAP Notes 1622681 and 1662726. Be aware that this option will only work if references to the FOR ALL ENTRIES list are on consecutive columns in the WHERE clause and that only "=" references are allowed. If these conditions are not fulfilled, a termination with a short dump will happen.
  • In order to take optimal advantage of the dbsl_equi_join hint in BW, you have to make sure that SAP Notes 2007363 (7.40 SPS 09) and 2020193 (7.40 SPS 08) are implemented.
See SAP Note 2142945 for more information regarding SAP HANA hints.
Long runtime of query on SAP HANA DDIC objects Assign CATALOG READ If the CATALOG READ privilege is not assigned to a user, queries on SAP HANA DDIC objects like TABLES, INDEXES or TABLE_COLUMNS can take much longer, because SAP HANA needs to filter the relevant (own) data and suppress the display of information from other schemas. Make sure that CATALOG READ is assigned (either directly or indicrectly via roles) to users having to access SAP HANA DDIC objects. You can use SQL: "HANA_Security_GrantedRolesAndPrivileges" (SAP Note 1969700) to check if this privilege is already assigned or not.
Long runtime of queries on monitoring views Use fewer and larger selections

Select information from other sources
For technical reasons accesses to monitoring views like M_TABLE_LOCATIONS or M_TABLE_PERSISTENCE_LOCATIONS often scan the complete underlying structures regardless of the WHERE clause. Thus, you should avoid frequent selections of small amounts of data (e.g. one access per table) and use fewer selections reading larger amounts of data (e.g. for all tables of a schema at once).
Alternatively check if you can select the required information from other sources, e.g. monitoring view M_CS_TABLES.
Similar overhead is also required for other monitoring views. The column FILTER_PUSHDOWN_TYPE in internal view SYS.M_MONITOR_COLUMNS_ provides information to what extent a column supports the pushdown of filters.
Wrong join order Update join statistics An obviously wrong join order can be caused by problems with join statistics. Join statistics are created on the fly when two columns are joined the first time. Up to SPS 08 the initially created join statistics are kept until SAP HANA is restarted. This can cause trouble if join statistics were created at a time when the involved tables had a different filling level, e.g. when they were empty. In this case you can restart the indexserver in order to make sure that new join statistics are created. Starting with SPS 09 SAP HANA will automatically invalidate join statistics (and SQL plans) when the size of an involved table changed significantly.
If you suspect problems with join statistics, you can create a join_eval trace (SAP Note 2119087) and check for lines like:
JoinEvaluator.cpp(01987) : getJoinStatistics: SAPSR3:CRMM_BUAG (-1)/BUAG_GUID<->SAPP25:CRMM_BUAG_H (-1)/BUAG_GUID
JoinEvaluator.cpp(01999) : jStats   clSizesL:0 clCntAL:0 cntSJL:0 TTL:0 clSizesR:0 clCntAR:0 cntSJR:0 TTR:0
Zero values in the second line can indicate that join statistics were created at a time when one table was empty.
Join statistics are a SAP HANA internal concept and so they can't be displayed or adjusted.
High runtime, not reproducible in SAP HANA Studio / DBACOCKPIT Recompile execution plan If the runtime of a query is longer than expected and you can't reproduce the long runtime with SAP HANA Studio or DBACOCKPIT (if bind variables are used: using a prepared statement with proper bind values), the issue can be caused by an inadequate execution plan (e.g. generated based on the bind values of the first execution or based on statistical information collected during the first execution). In this case you can check if an invalidation of the related SQL cache entry can resolve the issue:
ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY '<plan_id>'
You can identify the plan ID related to a statement hash by executing SQL: "HANA_SQL_SQLCache" (STATEMENT_HASH = '<statement_hash>', AGGREGATE_BY = 'NONE', DATA_SOURCE = 'CURRENT') available via SAP Note 1969700.
Depending on the factors considered during next parsing (e.g. set of bind values, dynamic statistics information) a better execution plan may be generated. It can happen that you have to repeate the RECOMPILE command until a good set of bind values is parsed.
Even if the problem is resolved after the RECOMPILE, there is no guarantee that it is permanently fixed, because after every eviction or restart a new parsing happens from scratch. If the problem is supposed to be linked to bind values, you can consider to adjust the application so that different classes of bind values are executed with slightly different SQL statements, so that SAP HANA can parse each statement individually.
As of SPS 09 more frequent reparses happen and so the situation can improve.
As of SPS 09 you can also think about the IGNORE_PLAN_CACHE hint as a last resort (see SAP Note 2142945). Be aware that this will make the performance more predictable, but due to the permanent parsing requirements the quick executions can significantly slow down. So it should only be used in exceptional cases.
Long preparation times Optimize parsing See SAP Note 2124112 and check if the amount and duration of preparations can be reduced (e.g. by increasing the SQL cache or using bind variables).
High runtime with range condition on multi column index Avoid selective ranges on multi column indexes Range conditions like BETWEEN, "<", ">", ">=", "<=" OR LIKE can't be used in order to restrict the search in a multi column index. As a consequence the performance can be significantly worse than expected. Possible solutions are:
  • Use a single column index rather than a multi column index if possible.
  • Use "=" or "IN" instead of a range condition.
High runtime with multiple OR concatenated ranges on indexed column Decompress column Due to a design limitation with SPS <= 100 SAP HANA doesn't use an index on an advanced compressed column if multiple OR concatenated range conditions exist on the same column. As a consequence statements like
SELECT ... FROM COSP 
WHERE ... AND ( OBJNR BETWEEN ? AND ? OR OBJNR BETWEEN ? AND ? )
can have a long runtime and high resource consumption. As a workaround you can only use DEFAULT compression for the table. See SAP Note 2112604 for more information.
High runtime with multiple EXISTS in combination with OR Consider UNION or upgrade to Rev. >= 111 Up to SAP HANA Rev. 110 multiple EXISTS semi-joins are not evaluated optimally if combined with OR. As a workaround you can check if OR can be transformed into UNION. As a permanent solution you can use SAP HANA Rev. >= 111.
High runtime although good single column index exists Make sure that index column isn't compressed with SPARSE or PREFIXED If a column is compressed with type SPARSE or PREFIXED, an index on this column doesn't take effect. In order to use a different compression for the column, you can run the following command:
UPDATE "<table_name>" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE', 'COLUMNS' = ('<column_name>') )
See SAP Note 2112604 for more information related to SAP HANA compression.
High runtime with join and TOP <n> Avoid TOP selections on unselective joins SAP HANA performs a significant amount of join activities on the overall data set before finally returning the TOP <n> records. Therefore you should consider the following options:
  • Provide selective conditions in the WHERE clause so that the amount of joined data is limited.
  • Avoid TOP <n> selections in unselective joins
  • Optimize the join processing, e.g. by defining optimal indexes on the join columns
High runtime of MIN and MAX searches Avoid frequent MIN and MAX searches on large data volumes Indexes in SAP HANA can't be used to identify the maximum or minimum value of a column directly. Instead the whole column has to be scanned. Therefore you avoid frequent MAX or MIN searches on large data volumes. Possible alternatives are:
  • Sequences
  • Identities (GENERATE [BY DEFAULT] AS IDENTITY, CURRENT_IDENTITY_VALUE())
  • Additional selective conditions
  • Maintaining the MIN and MAX values independently in a separate table
High runtime when LIKE condition with leading place holder is evaluated Avoid LIKE conditions with leading place holder The evaluation of a LIKE condition with a leading place holder (e.g. '%1234') can consume significant time on large tables. The runtime can be high even if the result set was already reduced to a small amount of records (> 0) before evaluating the LIKE condition.
In certain cases you can use LIKE_REGEXP (with the appropriate search pattern) instead of LIKE as a workaround. This should be tested thoroughly because LIKE_REGEXP can also impose overhead in other scenarios.
High runtime of COUNT DISTINCT Use SPS 09 or OLAP engine if SAP HANA <= SPS 08 is used and a COUNT DISTINCT is executed on a column with a high amount of distinct values, a rather larger internal data structure is created regardless of the actual number of records that have to be processed. This can significantly increase the processing time for COUNT DISTINCT. As a workaround you can check if the SQL statement is processed more efficiently using the OLAP engine by using the USE_OLAP_PLAN hint (see SAP Note 2142945). As a permanent solution you have to upgrade to SPS 09 or higher, so that the size of the internal data structure takes the amount of processed records into account.
High mass UPDATE runtime Use primary key for updating Updating a high amount of records in a single command (e.g. "UPDATE ... FROM TABLE" in ABAP systems) is more efficient than performing individual UPDATEs for each record, but it still can consume significant time. A special UPDATE performance optimization is available when the UPDATE is based on a primary key. So if you suffer from long mass UPDATE runtimes you can check if you can implement an appropriate primary key. A unique index is not sufficient for this purpose, a real primary key constraint is needed.
High runtime of TOP 1 requests Check TOP 1 optimization If on SAP HANA SPS >= 09 a query with a TOP 1 restriction (e.g. "SELECT TOP 1 ...") runs much longer than expected and call stacks (e.g. via SQL: "HANA_Threads_Callstacks", SAP Note 1969700) indicate that most of the time is spent in UnifiedTable::MVCCObject::generateOLAPBitmapMVCC, you can check if disabling the TOP 1 optimization feature can be used as a workaround (see SAP Note 2238679):
indexserver.ini -> [search] -> qo_top_1_optimization = false
High runtime of certain queries with Rev. 90 to 97.01 in UnifiedTable::MVCCObject coding Upgrade to Rev. >= 97.02 Similar to the TOP 1 issue above also other queries can suffer from long runtimes in modules like UnifiedTable::MVCCObject::isTSBlockGarbageCollected or UnifiedTable::MVCCObject::generateOLAPBitmapMVCC. One main root cause is fixed as of Rev. 97.02, so an upgrade can be considered in case the performance seriously impacts production operation.
Sporadically increased runtimes of calculation scenario accesses Check calculation engine cache size If accesses to calculation scenarios are sometimes slow, cache displacements can be responsible. You can use SQL: "HANA_Configuration_MiniChecks" (SAP Note 1999993, check ID 460) and SQL: "HANA_CalculationEngine_CalculationScenarios" (SAP Note 1969700) to find out more. If the cache is undersized, you can increase it using the following parameter (default: 1048576):
indexserver.ini -> [calcengine] -> max_cache_size_kb = <size_kb>
See also SAP Note 1988050 for specific problems with SAP HANA <= Rev. 73.
Increased runtime of joins Check table locations Make sure that tables involved in critical joins are located on the same host whenever possible, so that unnecessary communication overhead between SAP HANA nodes can be avoided.
High runtime accessing row store tables Check garbage collection A high runtime on row store tables can be caused by a high number of versions, e.g. due do a blocked garbage collection. See SAP Note 2169283 for more information about SAP HANA garbage collection.
Bad performance on specific row store table, unexpected UNION ALL in execution plan Check table containers For some reasons (e.g. when a column is added) a row store table can consist out of more than one underlying container. As a consequence, SAP HANA needs to combine the results from several containers using UNION ALL. Existing indexes may only work for a subset of the containers and so they aren't used in the most efficient way. In order to check for the number of containers and generate cleanup commands, you can use SQL: "HANA_Tables_RowStore_TablesWithMultipleContainers" (SAP Note 1969700). A table can be merged into a single container by reorganizing it with the following command:
ALTER TABLE "<table_name>" RECLAIM DATA SPACE
Be aware that this activity requires a table lock and so concurrent accesses to the same table may be blocked. Therefore it is recommended to perform it during a time of reduced workload. Furthermore you can set a low lock wait timeout on transaction level (e.g. 10000 ms) in order to reduce the risk of long-term lock escalations:
SET TRANSACTION LOCK WAIT TIMEOUT <timeout_in_ms>
High runtime of COMMIT operations Check disk I/O and system replication If COMMITs take in average more than a few ms, this is an indication for a bottleneck. See SAP Note 2000000 ("How can the performance of COMMIT operations be optimized?") for more information.
Unexplainable long runtime Optimize internal processing If you experience a runtime of a SQL statement that is much higher than expected, but none of the above scenarios apply, a few more general options are left.
See SAP Note 2142945 and test if the performance of SQL statements improves with certain hints (e.g. USE_OLAP_PLAN, NO_USE_OLAP_PLAN), because this can provide you with more ideas for workarounds and underlying root causes.
Check if SAP Notes exist that describe a performance bug for similar scenarios.
Check if the problem remains after having implemented a recent SAP HANA patch level.
Open a SAP incident on component HAN-DB in order to get assistance from SAP support.

11. Are secondary indexes required to provide an optimal performance?

SAP HANA is able to process data efficiently so that often a good performance is possible even without the use of indexes. In case of frequent, selective accesses to large tables it is nevertheless useful to create additional secondary indexes. As a rule of thumb column store indexes should be created on single columns whenever possible, because single column indexes require much less memory compared to multi column indexes.
See SAP Note 2160391 for more details.

12. Which advanced features exist for tuning SQL statements?

The following advanced features exist to optimize SQL statements:
Feature SAP Note Details
Hints 2142945 If the long runtime is caused by inadequate execution plans, you can influence them by using SAP HANA hints.
Result cache 2014148 If complex queries on rather static tables have to be executed repeatedly for the same result, you can consider using the query result cache.

13. Are there standard recommendations for specific SQL statements available?

In general it is hard to provide standard recommendations because optimizations often depend on the situation on the individual system. Nevertheless there are some exceptions where the underlying root cause is of a general nature. These SQL statements are collected in the following table:
Statement hash Type Object Optimization
a35caae16f2fa43033c8c4f278483d0c
e5332b10f3a1a4215728857efc0f8eda
f9468e4f53d23d0dd90230463b544c3c
48482a15fb005f2ef288ef198c0b1275
CALL
ALERT_BACKUP_LONG_LOG_BACKUP
These calls are issued by the SAP HANA statistics server. Usually a long runtime is caused by a large backup catalog. See SAP Note 2147247 ("How can the runtime and CPU requirements of the statistics server actions be analyzed and optimized?") and check if the backup catalog can be reduced. Alternatively you can increase the execution interval.
c0f43e5dbdfc438b86964acb0a22c05f

various
DELETE

CALL
HELPER_ALERT_MON_PYTHONTRACE_ACTIVE_AGE

ALERT_MON_PYTHONTRACE_ACTIVE
This statistics server check accesses the file-based M_INIFILE_CONTENTS view among others. As a consequence its runtime can suffer from disk I/O problems. Check if there are times of significantly increased execution times. If yes, analyze the disk I/O performance at this time (SAP Note 1999930). In case of correlated I/O problems it is likely that they are responsible for the long runtime of ALERT_MON_PYTHONTRACE_ACTIVE.
eb82038136e28e802bd6913b38a7848c  CALL
BW_CONVERT_CLASSIC_TO_IMO_CUBE
This procedure is executed when a classic infocube is converted to an in-memory optimized infocube using transaction RSMIGRHANADB. Increased load is normal when large infocubes are converted. After the conversion of the existing infocubes is finished, executing this procedure is no longer required, so it is only a temporary activity.
651d11661829c37bf3aa668f83da0950
42bf4a47fbf7eabb5e5b887dffd53c7c
CALL
INSERT
COLLECTOR_HOST_CS_UNLOADS
HOST_CS_UNLOADS_BASE
Disable the data collection for HOST_CS_UNLOADS as described in SAP Note 2084747. This action is also recommended in SAP Note 2147247.
ac52398f58a752bed7843aac8d829464
e9f2feac54a048984928f3fcbcfacedd
CALL
INSERT
COLLECTOR_HOST_RS_INDEXES
HOST_RS_INDEXES_BASE
These statements are related to the row store index history collection performed by the statistics server (see SAP Note 2147247). Consider reducing the amount of data in large row store tables (e.g. based on SAP Note 706478) or moving tables with large indexes to column store in order to improve the runtime of these queries.
0c2d40de2702bc576de87a9aec2cef30
f45e2eaba236b6655804f6585d631923
CALL
INSERT
COLLECTOR_GLOBAL_ROWSTORE_TABLES_SIZE
GLOBAL_ROWSTORE_TABLES_SIZE_BASE
These statements are related to the row store table size history collection performed by the statistics server (see SAP Note 2147247). Consider reducing the amount of data in large row store tables (e.g. based on SAP Note 706478) or moving large tables to column store in order to improve the runtime of these queries.
various SELECT BUT000, BUT020, ADRC Expensive SELECTs on tables BUT000, BUT020 and ADRC from CL_BUPA_IL_SEARCH_SERVICE->SEARCH_BY_ADDRESS2 can be optimized via SAP Note 1792008 by using switch CRM_PERF_BP_SEARCH.
68f35c58ff746e0fe131a22792ccc1b5 CALL BW_F_FACT_TABLE_COMPRESSION It is normal to see a significant cumulated runtime, because it is a central procedure call for all F fact table compressions.
This procedure performs BW compression on F fact tables (i.e. elimination of requests and transition of data into E fact table or dedicated F fact table partition). If F fact tables with a significant amount of records are processed, a significant runtime is expected. Otherwise you can use SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" (SAP Note 1969700) in order to check for the THREAD_DETAIL information related to this statement hash, which contains the involved table names. Based on this information you can check if you can optimize BW F fact table compression for the top tables. Be aware that the THREAD_DETAIL information is only available when the service_thread_sampling_monitor_thread_detail_enabled parameter is set to true (see SAP Note 2114710).
783ed7080a18c2f0684a1765c95354e6
SELECT CDHDR, CDPOS, CDPOS_UID, CDPOS_STR This statement includes three OR concatenated EXISTS subqueries that can't be handled optimally with SAP HANA Rev. <= 110. See "What are typical approaches to tune expensive SQL statements" -> "High runtime with multiple EXISTS in combination with OR" for details and consider an upgrade to SAP HANA Rev. >= 111 in order to eliminate the underlying limitation.
a7b7221b320c2cf6d25fd5842bf89ec4
e6c9e30929d7a58fd740be11b9d63204
611684d486918a356f1bbb14b790c17a
SELECT CDPOS Implement the dbsl_equi_join hint as described in SAP Note 2162575, so that the SQL statement is processed via IN list rather than OR concatenation. Alternatively avoid blanks at the end of TABKEY, so that the OR concatenation of LIKE conditions on TABKEY is no longer generated.
26e1ca7b731a467f2db818753d80118f
SELECT CRMD_ORDER_INDEX This access suffers from the COUNT DISTINCT limitation of SAP HANA SPS <= 08 and can be improved with the USE_OLAP_PLAN hint. SAP Note 2255511 provides the coding correction.
0e483b3074906106fd7c321a30fdea85
SELECT CRMORDERLOPR This SELECT is triggered by SAP ABAP table buffer reloads that can happen frequently because of invalidations. See SAP Note 1916476 for more information and disable table buffering for CRMORDERLOPR.
afb54cf91cd5d51ac7f360f82e67d5dd
INSERT DDLOG INSERTs into DDLOG can be impacted by the underlying SAP HANA sequence DDLOG_SEQ (see e.g. SAP Note 1977214). It is generally recommended to activate caching for this sequence. You can use the following command to activate a cache with 1000 elements for the sequence:
ALTER SEQUENCE <schema_owner>.DDLOG_SEQ CACHE 1000
91d7aff2b6fb6c513293d9359bf559a6
CALL DSO_ACTIVATE_PERSISTED It is normal to see a significant cumulated runtime, because it is a central procedure call for all DSO activations.
This procedure is the HANA server side implementation of DSO activation in BW environments. During this activation data is moved between three involved tables (/BIC/A*40, /BIC/A*00 and /BIC/B000*). It is much more efficient than the previous, SAP application server based approach, because moving a high amount from the database to the application and back is no longer required.
If the DSO activation in distributed environments takes longer than expected, you should check if the partitioning of the /BIC/A*40 and /BIC/A*00 tables is consistent, i.e. same partitioning and same hosts for the related partitions. You can check this DSO consistency via SQL: "HANA_BW_InconsistentDSOTables" (SAP Note 1969700).
For SAP HANA optimized DSOs you can check SAP Note 1646723 for DSO activation parameters.
A DSO activation performs many changes, so it can suffer significantly from bottlenecks in the redo log and system replication area. See SAP Notes 1999930 for redo log I/O analysis and 1999880 ("Can problems with system replication impact the performance on the primary system?") for more details.
Apart from this you can also apply optimizations on BW side, e.g. smaller requests or smaller keys.
various
SELECT EABL, EABLG If joins on tables EABL and EABLG are expensive without apparent reasons (like inadequate index design) you can check for the following optimizations:
  • If the tables were recently populated, the join statistics may not reflect the current state and SAP HANA may use an inappropriate execution plan. In this case a restart of SAP HANA would result in a collection of current join statistics. Starting with SAP HANA SPS 09 join statistics are also recreated online when the amount of changes on the underlying tables is significant.
  • If the expensive join happens in context of CRM for Utilities and the IC Webclient / CRM UI you can check SAP Note 2218437 and implement the proposed coding correction or the required IS-UT support package in order to reduce the amount of executed EABL / EABLG joins for displaying historical meter readings from an application perspective.
554c18cc7e6018abd21c69429b273f0b
SELECT FOR UPDATE FKKDIHDTMP If SELECT FOR UPDATE requests on table FKKDIHDTMP suffer from exclusive lock waits (SAP Note 1999998) you should check if a reduction of mass activity intervals can improve the situation (SAP Note 2177798).
7c541b915a48ae457956cda4e9702780
83ac4bf74da990133f1c525d05f43714

db2a5d8b668a837677bb6946de2a8d76
INSERT


CALL 
HOST_BLOCKED_TRANSACTIONS_BASE


COLLECTOR_HOST_BLOCKED_TRANSACTIONS
See SAP Note 2147247 and consider reducing the execution frequency for the statistics server actions related to blocked transactions (Collector_Host_Blocked_Transactions, Alert_Blocked_Transactions), e.g. to 300 seconds:
UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET INTERVALLENGTH = 300 WHERE ID IN (49, 5020)
b8b6f286b1ed1ef2e003a26c3e8e3c73
cf2e9b374514550f2b2e522df9f619ec
2afa9311f17e325d6d1418b3dd3eb388
2c7032c1db3d02465b5b025642d609e0
5ec9ba31bee68e09adb2eca981c03d43
5f42d3d4c911e0b34a7c60e3de8a70d2
SELECT M_BACKUP_CATALOG_FILES
M_BACKUP_CATALOG
These SELECTs are regularly issued by the backup console in SAP HANA Studio. In order to minimize the impact on the system you should open the backup console only when required and not permanently.
If you repeatedly need to open the backup console, you can increase the refresh interval (default: 3 seconds):
refreshInterval.jpg
Check if the backup catalog is unnecessarily large and delete old catalog entries if possible using:
BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID ...
bb45c65f7cc79e50dbdf6bbb3c1bf67e
b0f95b51bcece71350ba40ce18832179
SELECT M_CONNECTIONS This SELECT is executed when a SAP ABAP work process connects to SAP HANA. Implement a sufficiently new DBSL patch level according to SAP Note 2207349 in order to replace this query with a more efficient approach based on session variables.
fcdf8f8383886aaf02c3a45e27fbd5f2 SELECT M_CONNECTIONS
M_ACTIVE_STATEMENTS
This SELECT is executed if performance monitoring is scheduled via transaction /SDF/MON and the checkbox "SQL statements" is flagged. Disable this checkbox if you want to eliminate the load introduced by this statement hash.
various, e.g.:
d952da8472c0d4350559843cbfd48d0e
SELECT M_SERVICE_REPLICATION SELECTs to M_SERVICE_REPLICATION read some data from the secondary system replication site. They can suffer from network problems or unavailabilities related to the secondary system. See SAP Note 1999880 and make sure that system replication is established properly. Check according to SAP Note 2222200 if the network configuration of the primary and secondary SAP HANA system replication site works properly.
As a workaround in case of an unavailable secondary system you can disable the regulary secondary system check temporarily (SAP HANA Rev. >= 102.03 and >= 111):
global.ini -> [system_replication] -> check_secondary_active_status = 'false'
09214fa9f6a8b5322d329b156c86313b
28996bd0243d3b4649fcb713f43a45d7
7439e3c53e84b9f849c61d672da8cf79
84e1f3afbcb91b8e02b80546ee57c537
9ce26ae22be3174ceaffcfeee3fdd9b7

f32de4a673809ad96393ac60dfc41347
SELECT





UPDATE
M_STATISTICS_LASTVALUES





STATISTICS_LASTVALUES
This SELECT is executed by the Solution Manager in order to read history data from the statistics server. It is particularly critical if the standalone statistics server is used. The UPDATE is executed by the standalone statistics server in order to update statistical information.
Implement SAP Note 2147247 ("How can the memory requirements of the statistics server be minimized?"), so that the collection and processing of statistical data is optimized. A switch to the embedded statistics server is typically already sufficient to resolve this issue.
0ad1a4c1c1a5844d01595f5b3cdc2977 SELECT M_TRACEFILE_CONTENTS This SELECT is regularly issued by the backup console in SAP HANA Studio. In order to minimize the impact on the system you should open the backup console only when required and not permanently.
6b1d10732401fe82992d93fa91f4ae86 SELECT FOR UPDATE NRIV
Check if you can reduce the critical time frame between SELECT FOR UPDATE on NRIV and the next COMMIT. Identify the involved number range objects from application perspective and optimize their number range buffering.
See SAP Note 1398444 for buffering RF_BELEG and SAP Note 1524325 for buffering RV_BELEG.
Make sure that the table NRIV is located in row store, because the frequent UPDATE operations can be handled more efficiently in row store compared to column store.
Be aware that bad performance for the SELECT FOR UPDATE on NRIV can also be a consequence of some underlying general issues, so if it happens occasionally and in combination with some other issues, it can be a symptom for another problem and not an issue itself.
32139366d69f10543bc7abfc605b37a2 DELETE /PLMB/FRW_BUFFER
This DELETE can suffer from exclusive record lock waits due to an inappropriate application COMMIT strategy. See SAP Note 2236233 for a coding correction.
a77c6081dd733bd4641d4f42205f6c84 SELECT FOR UPDATE QIWKTAB This SELECT FOR UPDATE can suffer from row store garbage collection activities if the QRFC scheduler issues a very high amount of updates on a few records of this table. This typically happens if there are STOP entries in transaction SMQ2 that never can be processed, but result in a looping scheduler check. Go to transaction SMQ2 and check if there are STOP entries in any client which can be removed. After cleanup the update frequency should significantly reduce and the row store garbage collection is able to keep up without problems. See SAP Note 2169283 for more information related to garbage collection.
SAP Note 2125972 provides an application correction to reduce the amount of updates on QIWKTAB.
efd74759c6dc35f70ccd3529b62588f3 SELECT RSBERRORLOG
The runtime is high because of the ORDER BY clause that can't be supported by the primary index with SAP HANA. See SAP Note 2117500 and implement the coding correction or upgrade to a newer SAP BW support package level to eliminate this ORDER BY.
1d2893314f809f3e491e5628f6683ea5
3ff7bab8b9778b77b821816c3a147f81
6662b470dc055adfdd4c70952de9d239
93db9a151f16022e99d6c1c7694a83b0
c706c6fb087b78f6d07b5ae0848179a3
ccbffaf1607aab010cbd4512626e10fd
d740f122ae4c4ae3664a09482c249947
SELECT RSDDSTATEVDATA
Consider reducing the execution frequency of the related Solution Manager extraction “WORKLOAD ANALYSIS (BI DATA)”.
See SAP Note 706478 and delete / archive old records from RSDDSTATEVDATA. The minimum STARTTIME in table RSDDSTATINFO is from 30 days ago, so the actual retention time is 30 days. You could reduce it via parameter TCT_KEEP_OLAP_DM_DATA_N_DAYS (SAP Note 891740).
07a4169722d9848d8150f53037f3a6fe UPDATE RSDD_TMPNM_ADM
Table RSDD_TMPNM_ADM contains counters for temporary BW table names. Only a few records are updated frequently. In row store this can result in garbage collection related performance issues (see SAP Note 2169283). Therefore it is recommended (and also default with newer SAP ABAP releases) that RSDD_TMPNM_ADM is located in column store. You can move the table to column store using the following command:
ALTER TABLE RSDD_TMPNM_ADM COLUMN
0e30f2cd9cda54594a8c72afbb69d8fd
a115fd6e8a4da78f0813cfe35ffc6d42

00218f32b03b44529da647b530bf4e6d

8a8ec2baa7873e835da66583aa0caba2

b51046ad3685e1d45034c4ba78293fd8
ec91f8ecc5030996f1e15374f16749a8
f0a4785a6ada81a04e600dc2b06f0e49

52063e6acb3492518389d230490492f7
a3df1aa41b220869d74683a8fe9b1f98
c3a9a6620824cd0c931ae3a0e88942c6
8d87d6a303389d8a3f2c72639910da49

4fe60bbfbfa2979b89e8da75f5b2aac7

ca8758b296bd2321306f4fee7335cce5
2aeb4f7ffd47da8917a03f15a57f411a
SELECT


SELECT

SELECT

SELECT



SELECT




SELECT

SELECT
RSICCONT


RSSELDONE

RSMONICDP

RSSTATMANPART



RSSTATMANREQMAP




RSSTATMANSTATUS

TESTDATRNRPART0
If theses queries return a significant amount of records (> 100), it might be caused by a high number of existing requests. In this case you can check the following:
  • Run SQL: "HANA_BW_DataTargets" (MIN_REQUESTS = 10000) available via SAP Note 1969700 or check table RSMDATASTATE_EXT manually for data targets with REQUESTS_ALL > 10000.
  • See SAP Note 2037093 and reduce the request lists of data targets with more than 10,000 requests using programs like RSSM_AUTODEL_REQU_MASTER_TEXT and RSSM_REDUCE_REQUESTLIST.
8437943036a2a9fd82b290b2c6eafce7 SELECT RSR_CACHE_FFB
RSR_CACHE_VARSH
See SAP Note 706478 and check if you can clean up old entries in RSR_CACHE tables.
753769700d4435edcbc8cd8ecaa2a6fc CALL SHARED_FOLLOW_UP_ACTIONS This call is executed at the end of every statistics server alert check (independent of the fact if actually an alert was triggered) in order to schedule follow-up actions like creating a runtime dump or activating traces. It is normal to see a certain load coming from this SQL statement. Runtimes of up to 20 ms are normal. The number of executions can theoretically be influenced by adjusting alert check intervals as described in SAP Note 2147247 ("How can the statistics server check intervals be adjusted?"), but normally not required.
7488ecfdc727226c7bad7e99ebe21b25
e5332b10f3a1a4215728857efc0f8eda
SELECT SOURCE_ALERT_65_BACKUP_CATALOG This SELECT is linked to the statistics server action Alert_Backup_Long_Log_Backup. See SAP Note 2147247 ("How can the runtime and CPU requirements of the statistics server actions be analyzed and optimized?" -> "Alert_Backup_Long_Log_Backup") for details about optimizations.
5ef81b3843efbe961b19fdd79c2bd86b
a3bbe3e5447bc42eb3f0ece820585294
CALL
UPDATE
STATISTICS_PREPARE_CALL_TIMER
STATISTICS_SCHEDULE
The UPDATE on STATISTICS_SCHEDULE is executed from within the procedure STATISTICS_PREPARE_CALL_TIMER. High runtimes in combination with record locks (ConditionalVariable Wait) on table STATISTICS_SCHEDULE can be caused by inefficient COMMIT handling. This problem is resolved as of Rev. 101.
These calls are executed at the beginning of every statistics server check. It is normal to see a certain load coming from this SQL statement. Runtimes of up to 20 ms are normal. The number of executions can theoretically be influenced by adjusting alert check intervals as described in SAP Note 2147247 ("How can the statistics server check intervals be adjusted?"), but normally not required.
ac08e80aa8512f9669eaf56dedc02202
c6c59150977220ea4fdea412cac7d1ce
0800928edd9d43764935a4e02abbfa15
16c226e204b54efc280091df267152fd
2b7fee2d2b95e792d3890051cbb97ec9
55bb62c4b9ff3f0b32de3a4df248e18c
SELECT



SELECT
STATISTICS_CURRENT_ALERTS

STATISTICS_LAST_CHECKS
See SAP Notes 2147247 ("How can the runtime and CPU requirements of the statistics server actions be analyzed and optimized?" -> STATISTICS_ALERTS_BASE) and make sure that the size of the underlying table STATISTICS_ALERTS_BASE remains on a reasonable level.

d6fd6678833f9a2e25e7b53239c50e9a CALL STATISTICS_SCHEDULABLEWRAPPER This procedure is a wrapper for all embedded statistics server actions like history collections and alert checks. This statement hash typically indicates that it is called by the statistics server based on the configured check time intervals. See SAP Note 2147247 ("How can the runtime and CPU requirements of the statistics server actions be analyzed and optimized?") for more information how to analyze and optimize the actions.
dc571bf5eb7cad9c313c20de904ab709 CALL STATISTICS_SCHEDULABLEWRAPPER This procedure is a wrapper for all embedded statistics server actions like history collections and alert checks. This statement hash typically indicates that it is called by SAP Solution Manager in order to extract history and alert information from SAP HANA. See SAP Note 2147247 ("How can the runtime and CPU requirements of the statistics server actions be analyzed and optimized?") for more information how to analyze and optimize the actions.
deefdc73509bee2c6e78103e6ba5a44c SELECT STXH The performance can suffer from a wrong query optimizer decision. As a workaround you can create an additional index on columns MANDT and TDOBJECT.
97171744726a50e248699f758de23444
ba8161b6133633e2984828ce38aa669a
7f55e72c22694541b531e83d8bab8557
d24d8b8d00dd8b8df3e13dd9fb17b5f9
2b459e0d42037fe4d6880b238018a6f7
70efe3b4e470438543e6ecec418e4f02
905dbaa93a672b087c6f226bc283431d
CALL




CALL
SELECT
SELECT
ALERT_DEC_EXTRACTOR_STATUS


COLLECTOR_GLOBAL_DEC_EXTRACTOR_STATUS
GLOBAL_DEC_EXTRACTOR_STATUS_BASE
TABLES
This CALL and SELECT is executed by the statistics server under the _SYS_STATISTICS user. It is linked to DXC (see M_EXTRACTORS) and looks for tables with names like '/BIC/A%AO'. Tables following this naming convention are technical tables to control the DXC DSO activation process. If the CATALOG READ privilege is not granted to _SYS_STATISTICS, the query can take quite long and return limited results. You can massively improve the runtime by granting CATALOG READ to _SYS_STATISTICS.
371d616387247f2101fde31ed537dec0
aaff34b83d10188d08109cb52a0069ae
3f96e3762412e8d4cf4c670f2993aa8a
51bffaafaddd0ec825ecb9f5bf1b5615
5d0b4a21b0c17c08a536bec04f2825cd
a3f19325031503e9e4088a8e57515cd3
1cbbe08ef2bf075daecb9e383ae74deb
a5539c73611c1d0ba9e4a5df719329b8
a382ffaeafac914c422752ab9c2f9eab
81d83c08828b792684d4fd4d2580038e
1ce978ccb4cfed10e1ef12dc554c2273
54c8e15427f41b9074b4f324fdb07ee9
da7ef0fee69db516f1e048217bca39e7
c197df197e4530b3eb0dcc1367e5ba4b
DELETE
DELETE
DELETE
DELETE
DELETE
DELETE
DELETE
INSERT
INSERT
INSERT
TESTDATRNRPART0
TESTDATRNRPART1
TESTDATRNRPART2
TESTDATRNRPART3
TESTDATRNRPART4
TESTDATRNRPART5
TESTDATRNRPART6
TESTDATRNRPART0
TESTDATRNRPART1
TESTDATRNRPART2
  SAP Note 1964024 provides a correction that reduces the DML operations on the TESTDATRNRPART<id> tables for all types of databases. The optimization is available as of BW support packages SAPKW73012, SAPKW73112 and SAPKW74007.
Also make sure that no data targets exist with a high number of requests. See SAP Note 2037093 for more information.
e1cdd703df87fc61ce8163fa107162a9
CALL
TREXviaDBSL
TREXviaDBSL is used to execute queries that are not possible with plain SQL (e.g. BW queries with execution modes > 0). In the SAP HANA SQL cache both the content and the bind values are hidden, so a direct analysis and optimization is not possible.
SAP Note 2125337 provides a coding correction that allows to trace bind values via an expensive statements trace.
See "What are typical approaches to tune expensive SQL statements?" -> "Long runtime of TREXviaDBSL calls" for more information.
3e3b8e11cad214e9a7a5cec6193ad270
UPDATE
USR02
This UPDATE is frequently executed in SAP ABAP environments in order to update user specific information. Long runtimes are typically not caused by the statement itself, but by general infrastructure or SAP HANA issues. See SAP Note 2000000 and check for general performance issues (e.g. high CPU consumption, disk I/O issues or network problems).

14. Is it required to create optimizer statistics in order to support optimal execution plans?

it is not necessary to create optimizer statistics for normal SAP HANA tables. SAP HANA determines optimal execution plans by certain heuristics (e.g. based on unique indexes and constraints), by ad-hoc sampling of data or by internally collecting and re-using statistical information.
Remote tables accessed via Smart Data Access (SDA, SAP Note 2180119) can take advantage of optimizer statistics. They can be created with the CREATE STATISTICS command and displayed via SQL: "HANA_SQL_Statistics" (SAP Note 1969700).

15. Are all database operations recorded in the SQL cache (M_SQL_PLAN_CACHE)?

Standard operations like SELECT, INSERT, UPDATE or DELETE are recorded, but some more specific DML (e.g. TRUNCATE) and DDL operations (e.g. ALTER) are not contained.
Starting with Revision 74 also accesses from stored procedures and SQLScript to temporary tables are no longer stored in the SQL cache (see SAP Note 2003736, "Changed the implementation of SQL queries...").

16. Can sorting be supported by an appropriate index?

No, due to the technical structure it is not possible that a sort operation is supported by an index.

17. Is it possible to capture bind values of prepared SQL statements?

Often SQL statements are prepared with bind variables in order to avoid frequent reparses. Rather than literals you will then see question marks in the WHERE clause, e.g.:
SELECT 
  * 
FROM 
  "A004" 
WHERE 
  "MANDT" = ? AND 
  "KNUMH" = ? AND 
  "KAPPL" = ?
In order to understand selectivities and correlation and to be able to reproduce a problem with a SQL statement it is important to know which actual bind values are used. This information can be determined based on specific traces (e.g. the ST05 SQL trace in ABAP environments).
On SAP HANA side the expensive statement trace captures bind values which can e.g. be evaluated via SQL: "HANA_SQL_ExpensiveStatements_BindValues" (SAP Note 1969700).
Additionally SAP HANA is able to capture the bind values of critical SQL statements in the SQL plan cache per default as of SPS 08. This capturing is controlled by the following parameters:
Parameter Details
indexserver.ini -> [sql] -> plan_cache_parameter_enabled true: Activate capturing of bind values (for non-LOB columns) for long running SQL statements (default)
false: Deactivate capturing of bind values
indexserver.ini -> [sql] -> plan_cache_parameter_sum_threshold Minimum threshold for the total execution time of a SQL statement before first set of bind values is captured (in ms, default: 100000)
indexserver.ini -> [sql] -> plan_cache_parameter_threshold After having captured the first set of bind values for a certain SQL statement, it will capture further sets of bind values if the single execution time exceeds the parameter value (in ms, default: 100) and is higher than the single execution time of the previously captured bind values.
indexserver.ini -> [sql] -> plan_cache_parameter_for_lob_enabled true: Activate capturing of bind values for LOB columns for long running SQL statements, can result in signifcant data volume
false: Deactivate capturing of bind values for LOB columns (default)
The captured values are stored in view M_SQL_PLAN_CACHE_PARAMETERS and can be evaluated via SQL: "HANA_SQL_StatementHash_BindValues" (SAP Note 1969700).

18. How can the performance of INSERTs and data loads be tuned?

INSERTs and data loads write new data, while other operations like SELECT, UPDATE or DELETE have to work on existing data. Therefore typical performance factors are partially different. If you want to improve the performance of INSERTs and data loads, you can consider the following areas:
Area Details
Lock waits See SAP Note 1999998 and optimize lock wait situations if required. Typical situation when an INSERT has to wait for a lock are:
  • Critical savepoint phase
  • Concurrent INSERT of same primary key
  • SAP HANA internal locks
  • DDL operation on same table active
Columns During an INSERT every column has to be maintained individually, so the INSERT time significantly depends on the number of table columns.
Indexes Every existing index slows down an INSERT operation. Check if you can reduce the number of indexes during mass INSERTs and data loads. SAP BW provides possibilities to automatically drop and recreate indexes during data loads. Primary index normally mustn't be dropped.
Bulk load If a high number of records is loaded, you shouldn't perform an INSERT for every individual record. Instead you should take advantage of bulk loading options (i.e. inserting multiple records with a single INSERT operation) whenever possible.
Parallelism If a high number of records is loaded, you should consider parallelism on client side, so that multiple connections to SAP HANA are used to load the data.
Commits Make sure that a COMMIT is executed on a regular basis when mass INSERTs are done (e.g after each bulk of a bulk load).
Delta merge Usually it is of advantage to disable auto merges when a mass load is performed. See SAP Note 2057046 for more details.
An extremely large delta storage can reduce the load performance and increase the memory footprint, so it is usually good to trigger individual delta merges when really large loads are done, e.g. after every 100 million records.
Avoid repeated merges of small delta storages or with a high amount of uncommitted data in order to avoid unnecessary overhead.
Typical INSERT throughputs are:
Constellation Typical throughput
Problem situations like long critical savepoint phases or other locks < 500 records / second
Normal, sequential single-row INSERTs 1,000 - 10,000 records / second
Highly parallelized bulk loads 1,000,000 records / second

19. Why are there significant differences between SQL statements on ABAP and SAP HANA side?

The ABAP Open SQL statements are transferred to the database via the database interface (DBI). In many cases, the statement is modified in the DBI before being sent to the database. Typical adjustments are:
  • If a column with an empty variable is compared in the WHERE section, the DBI omits this condition.
  • When you use FOR ALL ENTRIES, the program splits a value list depending on the DBI parameters described in SAP Note 1987132 (particularly rsdb/max_blocking_factor, rsdb/max_in_blocking_factor) into statements with short IN lists or OR linkages.
  • If the FOR ALL ENTRIES list is empty, all data is generally selected from the current client ("SELECT ... FROM <table> WHERE MANDT = ?"). All conditions in the WHERE part are ignored.
  • Starting with kernel 7.42 a DISTINCT is generated in any of the following situations:
    • Fast data access is used (rsdb/prefer_join_with_fda = 1, see SAP Note 1987132) and no LOB columns are selected
    • The whole FOR ALL ENTRIES list can be satisfied with a single database selection and no LOB columns are selected
  • Accesses to SAP pool and cluster tables are converted into accesses on the related table pools or table clusters. In many cases these pool and cluster tables are already transparent tables with SAP HANA.
  • If tables are completely or generically buffered on SAP side, the buffers are reloaded, if necessary, with special DBI statements (for example, "SELECT * FROM <table> WHERE MANDT = ? ORDER BY <primary_key_fields>" for completely buffered tables) that may be completely different to the statement from the ABAP source code.
  • Some operations (for example, kernel procedures, bulk operations, generations) can generate SQL statements although there is no regular SQL statement in the ABAP source code.
  • Also when you use conversion exits, there can be significant differences between ABAP source code and database-side statements (such as additional conditions that are not explicitly present in the ABAP source code).
  • IN conditions from the ABAP source code may be converted into any number of possible conditions on the database level, depending on your selection criteria: "=", "IN", "LIKE", "BETWEEN", ">", "<", ">=", "<="
  • Columns that appear in both the selection list and the WHERE condition are removed from the selection list if it is clear from the WHERE condition what the column's value must be.
  • If an expression ends with a space followed by a placeholder, the system generates an OR concatenation as follows:
           SQL statement: ... WHERE <column> LIKE '<string> %'
           Statement after DBI transformation: ... WHERE (<column> LIKE '<string> %' OR <column> LIKE '<string>')

20. How does the EXPLAIN functionality work?

The following EXPLAIN functionalities exist:
Command Rev. Details
EXPLAIN PLAN FOR <sql_statement>

Generation of explain plan for explicitly specified SQL statement <sql_statement>
EXPLAIN PLAN FOR SQL PLAN CACHE ENTRY <plan_id>
>= 90
Generation of explain plan for SQL statement with PLAN_ID <plan_id> in M_SQL_PLAN_CACHE
This option is helpful to understand why a previously recorded SQL statement shows a different performance than current executions.
Results of these commands are written to the EXPLAIN_PLAN_TABLE. Among others, you can use SQL: "HANA_SQL_ExplainPlan" (SAP Note 1969700) to evaluate the results.
In order to identify the proper entries in EXPLAIN_PLAN_TABLE, you can use "EXPLAIN PLAN SET STATEMENT_NAME = '<statement_name>' ..." when generating the explain plan. Then you can check EXPLAIN_PLAN_TABLE for entries related to <statement_name>.


Keywords
HANA SQL statement tuning optimization request



Header Data

Released On 28.01.2016 13:46:33
Release Status Released to Customer
Component HAN-DB SAP HANA Database
Priority Normal
Category How To

No comments:

Post a Comment