6 HANA Options: Performance Analysis
6.1 SAP HANA Dynamic Tiering
Identify and resolve specific performance issues and enhance the performance of SAP HANA dynamic tiering.
Note
Troubleshooting information for SAP HANA dynamic tiering is in the SAP HANA Dynamic Tiering:
Administration Guide.
6.1.1 Tools and Tracing
This section gives you an overview of the tools and tracing options available for SAP HANA dynamic tiering.
6.1.1.1 Federation Trace
Federation trace can be turned ON to diagnose most issues with SAP HANA dynamic tiering.
Federation trace generates tracing information in the indexserver.ini trace file.
To enable federation trace:
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('trace', 'fedtrace') = 'debug' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('trace', 'federationexecution') = 'debug' WITH RECONFIGURE;
6.1.2 Query Plan Analysis
The query plan shows the various operators involved in the query execution.
Queries referencing both SAP HANA tables and SAP HANA dynamic tiering tables are either:
● Executed in SAP HANA by pulling data from SAP HANA dynamic tiering
● Relocated to SAP HANA dynamic tiering, where the data is pulled from SAP HANA
Generally, since SAP HANA dynamic tiering involves tables with large amounts of data, it may be preferable to use the latter strategy. Another reason to use the latter strategy is when the SAP HANA dynamic tiering table is too large to fit in SAP HANA.
If your query involves both SAP HANA tables and SAP HANA dynamic tiering tables and you are experiencing poor performance, you should review the query plan. Review the visual query plan that shows the timings for various sub-trees. Alternatively, you can query M_REMOTE_STATEMENTS to show timing results for query fragments executed on SAP HANA dynamic tiering. If the timing shown is small, and you think the optimizer is shipping the upper limit of query fragments, then SAP HANA dynamic tiering is probably not the cause of the performance problem.
The Remote Row Scan operator deals with a query fragment executed by an SAP HANA dynamic tiering node. If this operator is directly under the top-level Project node, then the entire query has been either pushed down or relocated to an SAP HANA dynamic tiering node. Generally, relocating the query yields better performance.
Changing the Execution Strategy to Remote
If the execution strategy 'auto' mode is not yielding the best plan, try the following procedure to change the execution strategy from 'auto' to 'remote':
1. Clear the query plan cache.
ALTER SYSTEM CLEAR SQL PLAN CACHE;
2. Change the execution strategy from 'auto' to 'remote':
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'execution_strategies') = 'remote' WITH RECONFIGURE;
Changing the Execution Strategy to Auto
If the execution strategy is set to 'auto', then the optimizer chooses the best strategy for executing the query:
either relocating the query to SAP HANA dynamic tiering, or executing the query in SAP HANA. In most cases, 'auto' provides best performance.
1. Change the execution strategy from 'remote' to 'auto':
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'execution_strategies') = 'auto' WITH RECONFIGURE;
If neither execution strategy improves performance, there may be a capability issue. The query optimizer decides what to push down to SAP HANA dynamic tiering based on the capability supported by the option. If the query deals with some operator, builtin, or other item that SAP HANA dynamic tiering does not
understand, then it lets the SAP HANA execution engine compensate for it. Review your query to see if there are any unsupported operators, or builtins and see if you can rewrite the query without them. Finally, you may be missing statistics on the tables that may prevent the optimizer from choosing an optimal query plan.
Related Information
Statistics [page 193]
6.1.2.1 Statistics
Statistics help the query optimizer in choosing the right query plan. Missing statistics may prevent the query optimizer from selecting the optimal query plan.
When no statistics for an extended table are present, the query optimizer assumes the table size to be 1 million rows. If the actual table has significantly different number of rows, then the query plan chosen may not be optimal. To ensure that optimizer has the correct information, we recommend that you create statistics on the extended tables.
SAP HANA currently supports two types of statistics:
Histogram Creates a multi-bucket histogram. Histogram statistics provides much better estimates for range queries.
Simple Creates simple statistics for each column in the table. Statistics are: minimum value, maximum value, total count of values, distinct count of values, and null counts.
We recommend that simple statistics, at the very least, are present on key columns of extended tables.
To create simple statistics on column c1:
CREATE STATISTICS ON TAB1 (c1) TYPE SIMPLE To create simple statistics on all columns:
CREATE STATISTICS ON TAB1 TYPE SIMPLE
To create histogram-type statistics on column c1:
CREATE STATISTICS ON TAB1 (c1) TYPE HISTOGRAM
6.1.3 Data Loading Performance
SAP HANA dynamic tiering supports all data loading methods for extended tables. This section explores the various mechanisms for inserting data into extended tables, and recommends the optimal loading
mechanisms.
In general, SAP HANA dynamic tiering is optimized for batch writes, like SAP HANA. Singleton writes are not the best use case for SAP HANA dynamic tiering, although singleton writes are supported.
6.1.3.1 IMPORT FROM Statement
If you have a CSV file for the data to be loaded, the IMPORT FROM statement is by far the best method for loading data into an SAP HANA dynamic tiering table.
The IMPORT FROM statement is sent directly to the SAP HANA dynamic tiering node for the data load.
Currently, IMPORT FROM does not support the THREADS parameter on the import command and hence it is better to break the files into multiple files if you are planning on loading hundreds of millions of rows.
When dealing with a delta-enabled extended table, you can run these IMPORT FROM statements in parallel if required. However, note that this will put heavy demand on the delta memory. Make sure delta memory is appropriately configured. Alternatively, you can do this load serially.
When importing very large amounts of data into an extended table, use multiple files in the same IMPORT FROM statement. Breaking into multiple files for IMPORT FROM yields better performance than a single file import.
6.1.3.2 INSERT Statement with SELECT Statement
If the data is present in another SAP HANA table, then INSERT-SELECT is a better loading method than the IMPORT FROM statement.
The query optimizer tries to relocate the INSERT-SELECT on an extended table to the SAP HANA dynamic tiering node. The SAP HANA dynamic tiering node does a parallel fetch from SAP HANA, thereby speeding up the INSERT-SELECT.
6.1.3.3 Parameterized Array Inserts
Array insert is by far the most optimal mechanism to load data into an extended table.
SAP HANA dynamic tiering converts an array-insert into a LOAD statement on the SAP HANA dynamic tiering node.
Bulk load is controlled by the bulk_inserts_as_load and bulk_load_as_binary parameters. Both parameters are 'true' by default.
If you need to re-enable the defaults:
1. Re-enable the bulk load mechanism for optimizing array inserts:
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'bulk_inserts_as_load') = 'true' WITH RECONFIGURE;
2. Re-enable binary load (instead of the ASCII load):
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'bulk_load_as_binary') = 'true' WITH RECONFIGURE;
Important Disclaimer for Features in SAP
HANA Platform, Options and Capabilities
SAP HANA server software and tools can be used for several SAP HANA platform and options scenarios as well as the respective capabilities used in these scenarios. The availability of these is based on the available SAP HANA licenses and the SAP HANA landscape, including the type and version of the back-end systems the SAP HANA administration and development tools are connected to. There are several types of licenses available for SAP HANA. Depending on your SAP HANA installation license type, some of the features and tools described in the SAP HANA platform documentation may only be available in the SAP HANA options and capabilities, which may be released independently of an SAP HANA Platform Support Package Stack (SPS).
Although various features included in SAP HANA options and capabilities are cited in the SAP HANA platform documentation, each SAP HANA edition governs the options and capabilities available. Based on this, customers do not necessarily have the right to use features included in SAP HANA options and capabilities.
For customers to whom these license restrictions apply, the use of features included in SAP HANA options and capabilities in a production system requires purchasing the corresponding software license(s) from SAP. The documentation for the SAP HANA optional components is available in SAP Help Portal at http://
help.sap.com/hana_options. If you have additional questions about what your particular license provides, or wish to discuss licensing features available in SAP HANA options, please contact your SAP account team representative.
Important Disclaimers and Legal Information
Coding Samples
Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, unless damages were caused by SAP intentionally or by SAP's gross negligence.
Accessibility
The information contained in the SAP documentation represents SAP's current view of accessibility criteria as of the date of publication; it is in no way intended to be a binding guideline on how to ensure accessibility of software products. SAP in particular disclaims any liability in relation to this document. This disclaimer, however, does not apply in cases of wilful misconduct or gross negligence of SAP. Furthermore, this document does not result in any direct or indirect contractual obligations of SAP.
Gender-Neutral Language
As far as possible, SAP documentation is gender neutral. Depending on the context, the reader is addressed directly with "you", or a gender-neutral noun (such as
"sales person" or "working days") is used. If when referring to members of both sexes, however, the third-person singular cannot be avoided or a gender-neutral noun does not exist, SAP reserves the right to use the masculine form of the noun and pronoun. This is to ensure that the documentation remains comprehensible.
Internet Hyperlinks
The SAP documentation may contain hyperlinks to the Internet. These hyperlinks are intended to serve as a hint about where to find related information. SAP does not warrant the availability and correctness of this related information or the ability of this information to serve a particular purpose. SAP shall not be liable for any damages caused by the use of related information unless damages have been caused by SAP's gross negligence or willful misconduct. All links are categorized for transparency (see: http://help.sap.com/disclaimer).
www.sap.com/contactsap
© 2015 SAP SE or an SAP affiliate company. All rights reserved.
No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. The information contained herein may be changed without prior notice.
Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary.
These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other