orkungedik/SAP-basis-dataset huggingface dataset:
## 3.14.1.1  SQL Statements Responsible for Past Problems
Sometimes it is not possible to identify a critical SQL statement during runtime. In this case you can use the following approaches to identify one or several SQL statements that can have contributed to the problem.
You can identify a SQL statement either by its SQL text ('statement string') or by the related statement hash that is uniquely linked to an individual SQL text. The mapping of the statement hash to the actual SQL text is described later.
To determine SQL statements with a particularly high runtime you can check for the top SQL statements in terms of TOTAL_EXECUTION_TIME on the SQL Plan Cache page in the SAP HANA cockpit.
To determine the top SQL statements that were executed during a dedicated time frame in the past, you can check the SQL plan cache history (HOST_SQL_PLAN_CACHE). You can use the SQL statement: 'HANA_SQL_SQLCache' available from SAP Note 1969700 - SQL Statement Collection for SAP HANA in order to check for top SQL statements during a specific time frame:
You have to specify a proper BEGIN_TIME / END_TIME interval and typically use ORDER_BY = 'ELAPSED' , so that the SQL statements with the highest elapsed time from SAP HANA are returned.
SQL Statements With Highest Elapsed Time
The thread sample history (tables m_SERVICE_THREAD_SAMPLES, HOST_SERVICE_THREAD_SAMPLES), if available, can also be used to determine the top SQL statements. You can use the SQL statement: 'HANA_Threads_ThreadSamples_FilterAndAggregation' available from SAP Note 1969700 SQL Statement Collection for SAP HANA in order to check.
You have to specify a proper BEGIN_TIME / END_TIME interval and use AGGREGATE_BY = 'STATEMENT_HASH' to identify the top SQL statements during the time frame.
SQL Example Output
In this case the SQL statement with hash 51f62795010e922370bf897325148783 is executed most often and so the analysis should be started with it. Often you need to have a look at some more SQL statements, for example the statements related to the next statement hashes fc7de6d7b8942251ee52a5d4e0af728f and 1f8299f6cb5099095ea71882f84e2cd4.
In cases where the m_SERVICE_THREAD_SAMPLES / HOST_SERVICE_THREAD_SAMPLES information is not usable you can use the thrloop.sh script to regularly collect thread samples as described in SAP Note 1989031 - Scheduling of Shell script 'thrloop.sh' .
In case of an out-of-memory (OOM) situation you can determine potentially responsible SQL statements by analyzing the OOM dump file(s) as described in SAP Note 1984422 - Analysis of HANA Out-of-memory (OOM) Dumps .
SAP HANA Alert 39 ('Long-running statements') reports long-running SQL statements and records them in the table _SYS_STATISTICS.HOST_LONG_RUNNING_STATEMENTS. Check the contents of this table to determine details of the SQL statements that caused the alert. See also KBA 1849392 - HANA alerts related to locks, hanging sessions and long runners .
## Related Information
```
SAP Note 1969700 SAP Note 1989031 SAP Note 1984422 SAP Note 1849392
```
## 3.14.1.2  SQL Statements Responsible for Current Problems
If problems like high memory consumption, high CPU consumption or a high duration of individual database requests are currently happening, you can determine the active SQL statements with the help of SAP HANA cockpit.
Check for the currently running SQL statements in SAP HANA cockpit by clicking on the Threads tile.
## Tracing
You can also activate the following traces to capture more detailed information to help identify critical SQL statements:
- SQL trace can be used to capture performance data for all SQL statements, you can use filter conditions to limit the trace activities.
- Expensive statements trace captures all SQL statements with a runtime beyond a defined threshold.
Further details can be found in Tools and Tracing .
## Related Information
Tools and Tracing [page 237]
## 3.14.2  Analysis of Critical SQL Statements
When you have identified the SQL text and the related statement hash based on the tools and traces, you can collect more information about the SQL statement in order to identify the root cause of the performance problem and optimize the statement. The available analysis approaches are described here.
From a technical perspective, analyzing query plans allows you to identify long running steps, understand how much data is processed by the operators, and see whether data is processed in parallel.
However, if you understand the idea and purpose behind the query, you can also analyze query plans from a logical perspective and consider the following questions to gain the insight you need:
- Does SAP HANA read data from multiple tables when only one is required?
- Does SAP HANA read all records when only one is required?
- Does SAP HANA start with one table even though another table has a much smaller result set?
The following tools can be used for a more detailed analysis:
- Plan Explanation - used to evaluate the execution plan;
- SQL analyzer - provides a detailed graphical execution plan with a timeline;
- Query optimizer (QO) Trace - an advanced tool that can be useful to understand the decisions of the query optimizer and column searches;
- Join evaluation (JE) Trace - an advanced tool to analyze table join operations;
- Performance trace - this advanced tool should only be used in collaboration with SAP support, it gives low level recording of key performance indicators for individual SQL statement processing steps;
- Kernel profiler - this advanced tool should only be used in collaboration with SAP support, it performs sample based profiling of SAP HANA process activities.
## Related Information
Analyzing SQL Execution with the Plan Explanation [page 249]
Analyzing SQL Execution with the Plan Visualizer [page 253]
Analyzing Column Searches (qo trace) [page 282]
Analyzing Table Joins [page 283]
Performance Trace [page 289]
Kernel Profiler [page 291]
## 3.14.3  Optimization of Critical SQL Statements
You can improve the general performance of the SAP HANA database by implementing various best practices, design principles, available features, and add-ons.
To enhance the performance of the SAP HANA database, we recommend you do the following:
- Optimize outlier queries
Queries that sometimes take much longer than expected can be caused by query-external factors (for example, resource bottlenecks) that have to be determined and eliminated.
- Check data manipulation commands (DML) DML operations like INSERT, UPDATE and DELETE can be impacted by lock waits.
- Create indexes for any non-primary key columns that are often queried. SAP HANA automatically creates indexes for primary key columns; however, if you need indexes for non-primary key columns, you must create them manually.
- Use native HANA models
Develop native HANA models and graphical calculation views in the SAP Web IDE to take advantage of the latest performance optimization capabilities.
- Develop procedures to embed data-intensive application logic into the database. With procedures, no large data transfers to the application are required and you can use performanceenhancing features such as parallel execution.
- If you use SQLScript to create procedures, follow the best practices for using SQLScript.
- For statistical computing, create procedures using the open source language R.
- Download and install the available application function libraries, such as Predictive Analysis Library (PAL) and Business Function Library (BFL). Application functions are like database procedures written in C++ and called from outside to perform data
- intensive and complex operations.
- Scale SAP HANA to improve performance. SAP HANA's performance is derived from its efficient, parallelized approach. The more computation cores
your SAP HANA server has, the better overall system performance is.
##  Note
With SAP HANA, you do not need to perform any tuning to achieve high performance. In general, the SAP HANA default settings should be sufficient in almost any application scenario. Any modifications to the predefined system parameters should only be done after receiving explicit instruction from SAP Support.
## 3.14.3.1  Outlier Queries
Outlier queries are database statements that take much more time than usual and expected. This usually happens because extra work has to be performed during execution.
Besides heavy load on the machine by non-SAP HANA processes (which should not be the case on production systems) SAP HANA itself can be under heavy load. Reasons include:
- Many users are connected and issue a large amount of statements
- Extraordinary expensive statements are executed
- Background tasks are running
Use the Performance Monitor to determine the number of statements issued and the indexserver CPU usage while the slow statement execution was perceived (see the figure CPU Consumption and SQL Throughput , the lower line (red) is the CPU consumption in percent (%), the upper line (orange) is the SQL throughput / s):
CPU Consumption and SQL Throughput
You can see that during the period in the red rectangle both CPU consumption and SQL throughput decreased. During that time frame you would look for something that consumed a lot of resources or blocked the statements (locking); just after 15:35 you see that the CPU consumption increases while the SQL throughput decreases. Here, a possible case would be a change in usage: instead of many small, fast SQL statements the workload changed to a few "heavy" (complicated calculation requiring many CPU cycles) SQL statements.
If there was a high statement load in the same period when you experienced the slow execution, the root cause is likely a lack of resources. T o resolve the situation consider restricting the number of users on SAP HANA or upgrading the hardware. See Getting Support for further help, for example if you need to improve scalability.
If you did not experience a high statement load during the time frame of the problem, check for background activities:
- Delta Merges : Use Performance Monitor Merge Requests and the monitoring view m_DELTA_MERGE_STATISTICS to check if delta merges happened. In that case try to improve the delta merge strategy to prevent merges happening in phases where users are disturbed (see the SAP HANA Administration Guide for details).
- Column Unloads : See Load Monitor Column Unloads and the monitoring view m_CS_UNLOADS to look for signs of column unloads. If a column used in the problematic statement had to be loaded before execution, the execution itself will take significantly longer.
- Savepoints : Savepoints consume resources and write-lock the database during their critical phase. Check m_SAVEPOINTS and look for savepoints during the time frame of the problem. If a savepoint slowed down your execution, the chance of having the same problem again is very low. If it happens often, see Getting Support for further help.
## Related Information
m_DELTA_MERGE_STATISTICS
m_CS_UNLOADS m_SAVEPOINTS
## 3.14.3.2  Data Manipulation Language (DML) Statements
Data Manipulation Language (DML) statements are often slowed down by lock-wait situations.
Check under Monitoring SQL Plan Cache and the view m_SQL_PLAN_CACHE to determine such issues:
##  Note
Only check entries that have TOTAL_LOCK_WAIT_COUNT greater than 0. For those entries, compare the column MAX_CURSOR_DURATION against AVG_CURSOR_DURATION. If there is a significant difference, there was at least one situation where the transactions took much longer than average.
See Transactional Problems for information on how to deal with such issues.
## Related Information
m_SQL_PLAN_CACHE Transactional Problems [page 167]
## 3.14.3.3  Native HANA Models
Creating native HANA models can be one way to improve performance compared to development options outside of the database, or in some cases also compared to pure SQL development.
Native HANA models can be developed in the new XS Advanced (XSA) development environment using SAP Web IDE for SAP HANA. These models supersede older artifacts like Analytic and Attribute Views; these views should now be replaced by graphical Calculation Views which can be used to model complex OLAP business logic. Native HANA modeling provides various options to tune performance by, for example, helping to achieve complete unfolding of the query by the calculation engine or modeling join cardinalities between two tables (that is, the number of matching entries (1...n) between the tables) and optimizing join columns.
For more information about modeling graphical calculation views refer to the SAP HANA Modeling Guide for SAP Web IDE for SAP HANA .
A number of blogs are available about the details of modeling:
- https://blogs.sap.com/2017/09/01/overview-of-migration-of-sap-hana-graphical-view-models-into-thenew-xsa-development-environment/ Overview: Migration of Models into the XSA Development Environment
- https://blogs.sap.com/2017/10/27/join-cardinality-setting-in-calculation-views/ Join cardinality setting in Calculation Views
- https://blogs.sap.com/2018/08/10/optimize-join-columns-flag/ Optimize Join Columns Flag
The following SAP Notes provide further background information:
- https://launchpad.support.sap.com/#/notes/2441054 2441054 - High query compilation times and absence of plan cache entries for queries against calculation views.
- https://launchpad.support.sap.com/#/notes/2465027 2465027 - Deprecation of SAP HANA extended application services, classic model and SAP HANA Repository.
## Related Information
SAP HANA Modeling Guide for SAP Web IDE for SAP HANA
## 3.14.3.4  Developing Procedures
SQL in SAP HANA includes extensions for creating procedures, which enables you to embed data-intensive application logic into the database, where it can be optimized for performance (since there are no large data transfers to the application and features such as parallel execution is possible). Procedures are used when other modeling objects, such as views, are not sufficient; procedures are also often used to support the database services of applications that need to write data into the database.
Reasons to use procedures instead of standard SQL, include:
- SQL is not designed for complex calculations, such as for financials.
- SQL does not provide for imperative logic.
- Complex SQL statements can be hard to understand and maintain.
- SQL queries return one result set. Procedures can return multiple result sets.
- Procedures can have local variables, eliminating the need to explicitly create temporary tables for intermediate results.
Procedures can be written in the following languages:
- SQLScript: The language that SAP HANA provides for writing procedures.
- R: An open-source programming language for statistical computing and graphics, which can be installed and integrated with SAP HANA.
There are additional libraries of procedures, called Business Function Library and Predictive Analysis Library, that can be called via SQL or from within another procedure.
## HANA Database Explorer
HANA Database Explorer provides a comprehensive set of development tools that allow you to evaluate, revise, and optimize stored procedures. You can browse through the objects in the schema to locate the procedures, from there, a number of options are available from the context menu. Features include a code editor for running and testing procedures as well as debugging and SQLScript analysis tools. Refer to the documentation sections on Database Explorer in the SAP HANA Administration Guide for more details.
## SQL Extensions for Procedures
SQL includes the following statements for enabling procedures:
- CREATE TYPE : Creates a table types, which are used to define parameters for a procedure that represent tabular results. For example:
- CREATE PROCEDURE : Creates a procedure. The LANGUAGE clause specifies the language you are using to code the procedure. For example:
- CALL : Calls a procedure. For example:
```
CREATE TYPE tt_publishers AS TABLE (
```
```
publisher INTEGER, name VARCHAR(50), price DECIMAL, cnt INTEGER);
```
```
CREATE PROCEDURE ProcWithResultView(IN id INT, OUT o1 CUSTOMER) LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ProcView AS BEGIN o1 = SELECT * FROM CUSTOMER WHERE CUST_ID = :id; END;
```
```
CALL getOutput (1000, 'EUR', NULL, NULL);
```
## Related Information
Create and Edit Procedures
Open the SAP HANA Database Explorer (SAP HANA Cockpit)
## 3.14.3.5  Application Function Library (AFL)
You can dramatically increase performance by executing complex computations in the database instead of at the application server level.
SAP HANA provides several techniques to move application logic into the database, and one of the most important is the use of application functions. Application functions are like database procedures written in C++ and called from outside to perform data intensive and complex operations. Functions for a particular topic are grouped into an application function library (AFL), such as the Predictive Analytical Library (PAL) or the Business Function Library (BFL).
Currently, all AFLs are delivered in one archive (that is, one SAR file with the name AFL&lt;version_string>=.SAR).
##  Note
The AFL archive is not part of the SAP HANA appliance, and must be installed separately by an administrator. For more information about installing the AFL archive, see the SAP HANA Server Installation and Update Guide .
## Security Considerations
- User and Schema
During startup, the system creates the user _SYS_AFL, whose default schema is _SYS_AFL.
##  Note
The user and its schema _SYS_AFL are created during a new installation or update process if they do not already exist.
All AFL objects, such as areas, packages, functions, and procedures, are created under this user and schema. Therefore, all these objects have fully specified names in the form of _SYS_AFL.&lt;object name>=.
- Roles
For each AFL library, there is a role. Y ou must be assigned to this role to execute the functions in the library. The role for each library is named: AFL__SYS_AFL_&lt;AREA NAME>=_EXECUTE. For example, the role for executing PAL functions is AFL__SYS_AFL_AFLPAL_EXECUTE.
##  Note
There are 2 underscores between AFL and SYS.
##  Note
Once a role is created, it cannot be dropped. In other words, even when an area with all its objects is dropped and recreated during system start-up, the user still keeps the role that was previously granted.
## 3.14.3.7  Further Recommendations
In addition to the general recommendations for improving SAP HANA database performance, for specific scenarios, you can use further features and best practices to improve performance.
If appropriate, you can take the following actions to improve performance:
- For any required long-running transactions, you can use the SQL command ALTER SYSTEM RECLAIM VERSION SPACE to trigger the row store garbage collector to free up memory space and enhance system responsiveness.
- For multicolumn join scenarios, use dynamic joins rather than standard joins. In a dynamic join, the elements of a join condition between two data sources are defined dynamically based on the fields requested by the client query. It is used to improve the performance by reducing the number of records to be processed by the join node.
- When inserting or loading a large number of rows into a table that has a TEXT or SHORTTEXT column or uses a FULLTEXT INDEX, merge the delta of the table for better search performance.
- When loading data from CSV files using the IMPORT FROM command, use THREADS and BATCH to enable parallel loading and commit many records at once. In general, for column tables, a good setting to use is 10 parallel loading threads, with a commit frequency of 10,000 records or greater. You can also use TABLE LOCK , which locks the entire table and bypasses the delta table. Table locks are only recommended for initial loads.
## 3.15 Application Performance Analysis
This section provides a high-level guide for analyzing the performance of SAP HANA-based applications.
When you have an SAP HANA-based application with unsatisfying performance, you should start a systematic investigation considering the various layers that are involved. Here we focus on the database layer; approaches for analyzing UI and application server performance are described in the respective documents.
## 3.15.1  SQL Trace Analysis
The first step in application performance analysis is to figure out if the database layer is causing performance problems for your application at all.
## Context
You should analyze how many and which database calls are made and what their contribution to the overall application performance is. This should be done within the context of a given user interface step or transaction.
## Procedure
1. Start the tracing of database calls.
2. Run the application from its user interface or with any other driver.
Both, SAP HANA cockpit and SAP Web IDE for SAP HANA provide two main tracing tools, namely SQL trace and Expensive statements trace .
A convenient way to narrow the trace analysis to the scope of a user interface step or transaction is to use the passport-based filtering of Expensive statements trace in the SAP Web IDE for SAP HANA, which also offers aggregated statistics to quickly answer above questions.
Deep tracing (including complete execution plans) is provided by Plan trace in SAP HANA cockpit.
3. Terminate the tracing and review aggregated and individual results.
4. As a result of this investigation you might see some indicators for bad application logic creating excessive load on the database such as:
- Too many database calls (per transaction/UI step)
- Many identical executions, for example repeated identical selects
- Too many records returned (per execution or in total)
- Too many columns or all columns of a row selected
- Inefficient statement reuse, that is, statements that need to be optimized over and over again
- One or more database calls with unexpected bad performance, so you should further investigate those calls
## Related Information
Plan Trace [page 265]
Analyzing SQL Traces [page 238] Expensive Statements Trace [page 243]
## 3.15.2  Statement Measurement
Once you have determined which SQL statements are problematic you should first perform a sound measurement in order to get reliable performance numbers and to make sure that indeed your statements are causing the issues and not the current state of your SAP HANA system.
## Procedure
1. Execute your statements and measure their performance (in particular response time).
SAP HANA cockpit offers basic measurement of SQL statements. In addition, the SAP HANA database explorer and the SQL analyzer support executing and analyzing SQL statements.
2. Check your SAP HANA system status for disturbing conditions, such as high load, high resource usage and so on.
3. In case of disturbing conditions repeat your measurement from step 1.
4. Repeat your measurements until you get stable results without major variations (for example, 3 stable executions in a row).
##  Note
Higher initial response times could be an indicator of caches that are not properly filled. Depending on your business needs you can decide whether this is acceptable or not.
5. Once you have a stable result you may also acquire a detailed SAP HANA engine trace which will allow for a deeper analysis.
## Results
As a result of this activity you have reliable data for your query performance, both for initial query execution performance (possibly cold execution) and stabilized execution performance (warm execution).
## Related Information
Statement Performance Analysis [page 180]
## 3.15.3  Data Analysis
The easiest way to analyze a poorly performing SQL statement is to look at the data flow as this can be matched with the understanding of the business/application needs.
## Procedure
1. Check the result size, that is the number of records and number of columns, returned by your SQL statement and compare it with the actual business needs.
As a result of this investigation you might restrict the result size by changing the application logic (for example, the number of columns selected in the field list or by applying additional filters in the WHERE clause) with regards to its database call.
2. Check the usage of underlying tables, meaning the set of tables used, their size and the number of entries selected from those tables, and compare it with your understanding of the business needs.
As a result of this investigation you might identify tables that should not be involved at all and adapt your statement or the underlying database logic accordingly (for example, by checking joins and join types). SAP HANA cockpit offers data flow analysis with the Tables Used view in the SQL analyzer.
3. Check the data flow of your statement, that is, the order in which tables are joined, how filters are applied and the size of intermediate results.
As a result of this investigation you may identify:
- inefficient join orders (starting with table A and not with table B, when that is the much smaller result set)
- unexpectedly missing filters (that is, intermediate selections which seem too broad).
You can then adapt your statement or underlying database logic as necessary.
SAP HANA cockpit offers data flow analysis with the Plan Graph view in the SQL analyzer.
For detailed information about the SQL analyzer, refer to Analyzing Statement Performance in the SAP HANA Administration Guide .
## Related Information
Tables Used [page 263]
Analyzing Statement Performance SAP Note 2565156
## 3.15.4  Source Analysis
Before conducting a detailed technical analysis, it is recommended to first analyze source artifacts (models, scripts, views) for possible performance-impacting aspects.
## Context
The actual activities depend on the respective artifact (models, scripts, views).
## Procedure
1. Graphical Calculation Views
SAP Web IDE for SAP HANA offers dedicated performance debugging capabilities as part of the modeler for graphical calculation views; furthermore it provides deeper inspection of these views.
- a. Check the effect of the attributes, parameters and filters used in the query on the execution time.
- b. Check that the information is combined and aggregated in the correct way as required by the business scenario, for example:
- Is the aggregation behavior consistent with the intended semantics?
- Is the join cardinality correct for each join?
- Are dynamic joins used?
- Is the result always aggregated?
- Has Join Optimization been enabled?
- Do analytical privileges influence performance?
- c. Check whether any performance-relevant execution hints are activated, for example:
- Enforce execution via a specified database engine
- Enforce upper bound for parallelization
- d. Check whether modeling constructs are used that are known to be costly, for example:
- Calculated join attributes
- Complicated filter expressions
- Mixed models that involve engine switches
- e. Check whether intermediate steps produce reasonable results (in terms of size).
2. SQL Script
The Statement Statistics view (part of the SQL analyzer) supports analysis of SQLScript.
- a. Check if your procedure fits with the given guidelines For example, see Developing Procedures
- b. Analyze your script for most expensive steps / statements
3. Plain SQL
- a. Check if your statement fits with the guidelines for SQL Query Design For example, see Optimization of Critical SQL Statements
## Results
As a result of this activity you either figured out and solved problems at the source level or you now have to continue with an even deeper technical analysis.
## Related Information
Developing Procedures [page 194] Technical Analysis [page 203] Statement Statistics [page 257] Optimization of Critical SQL Statements [page 190]
## 3.15.5  Technical Analysis
The deepest level of performance analysis addresses the technical details of a database statement execution.
## Context
You should follow this in order to track down problematic performance symptoms from which you can derive possible root causes at higher levels.
There are a number of tools that can support you. SAP HANA cockpit offers the SQL analyzer which allows for deep technical analysis. Dedicated views and filters support the analysis along numerous dimensions. For detailed information about the SQL analyzer, refer to Analyzing Statement Performance in the SAP HANA Administration Guide .
## Procedure
1. Inspect aggregated execution KPIs (execution time, resource consumption, distribution characteristics) in order to figure out in which aspect or dimension you might look for possible issues.
2. For any suspicious KPI, track down the KPI to the deepest possible level manifesting the symptom.
3. From there, try to correlate the symptom with the cause, in higher level statement elements.
4. You might restrict the scope of your analysis by focusing on the critical path, or on a specific time interval, system node, engine, or execution plan operator.
## Related Information
Analyzing SQL Execution with the Plan Explanation [page 249]
Analyzing Statement Performance
SAP Note 2565156
## 3.16 System Hanging Situations
This section explains what checks to carry out and how to perform those checks if your SAP HANA instance is hanging. The database is said to be hanging when it no longer responds to queries that are executed against it.
## Context
The source of the system standstill might be related to any of the components involved, for example, the storage, OS and hardware, network, SAP HANA database or the application layer. On servers with Intel processors this situation may be caused by Transparent Huge Pages (THP).
For troubleshooting it is essential to collect information about the context of the active threads in the SAP HANA database, therefore avoid restarting the database as retrospective analysis might not always be possible. As SQL statements cannot usually be executed for analysis, you should perform the following steps if it is still possible to log on to the OS of the master host (for example, as the &lt;sid>= adm user):
## Procedure
1. Collect a runtime dump (see SAP Note 1813020 - How to generate a runtime dump on SAP HANA).
2. Collect CPU, memory and other information about threads currently running in the system by executing the command top -H and taking a screenshot of the output.
3. Provide this information when logging an incident with SAP support.
Refer also to SAP Note 1999020 SAP HANA: Troubleshooting when database is no longer reachable for further specific steps and guidance on proactive or reactive actions you can take.
## Related Information
SAP Note 1999020 SAP Note 1813020 Transparent Huge Pages on Linux [page 204] CPU Power Saving [page 206]
## 3.16.2  CPU Power Saving
The Linux Kernel shipped with SUSE Linux Enterprise Server (SLES) 11 and Red Hat Enterprise Linux (RHEL) 6 versions certified for usage with SAP HANA contain a new cpuidle driver for recent Intel CPUs. This driver leads to a different behavior in C-states switching and causes performance degradations.
## Context
See the SAP Notes below for further reference:
- SAP Note 1824819 SAP HANA DB: Recommended OS settings for SLES 11 / SLES for SAP Applications 11 SP2
- SAP Note 1954788 SAP HANA DB: Recommended OS settings for SLES 11 / SLES for SAP Applications 11 SP3
- SAP Note 2013638 SAP HANA DB: Recommended OS settings for RHEL 6.5
- SAP Note 2136965 SAP HANA DB: Recommended OS settings for RHEL 6.6
## Procedure
1. Check if the recommended driver is enabled and whether the CPU power safe mode is activated. Execute the following command as root user in the Linux shell:
cat /sys/devices/system/cpu/cpuidle/current_driver
The correct value for the cpuidle driver should be "acpi_idle". If so, no further steps are required. In case the output shows the wrong value "intel_idle", follow the steps in SAP Notes.
2. Check the CPU power save mode by running the following command:
```
cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
```
The correct value for the CPU power save mode should be "performance". If the output shows at least one line with "ondemand", follow the steps in SAP Note 1890444 - Slow HANA system due to CPU power save mode .
## Related Information
SAP Note 1824819
SAP Note 1954788
SAP Note 2013638
SAP Note 2136965
SAP Note 1890444
## 3.17 Troubleshoot System Replication
This section describes how to analyze, avoid and solve problems related to system replication.
The following topics are covered:
- Performance: system replication appears to slow down transaction processing
- Setup and initial configuration problems
- Intermittent connectivity problems
- Managing the size of the log file with logreplay.
## Further Resources
System Replication is described in detail in the SAP HANA Administration Guide and the SAP HANA System Replication Guide . Additionally, the following set of documents including illustrated step-by-step procedures is available on the SAP Community Portal:
- SAP HANA - High Availability ('White Paper')
- SAP HANA - Host Auto-Failover
- How To Perform System Replication for SAP HANA 2.0
- SAP HANA Network Requirements
The two FAQ SAP Notes listed here relate to High Availability and Replication:
## Related Information
SAP Note 2057595 SAP Note 1999880 SAP HANA Administration Guide SAP HANA System Replication
## 3.17.3  Log Shipping Timeout
This section gives possible reasons why the log buffer may not be shipped in a timely fashion from the primary to the secondary site.
## Possible Cause 1: Synchronous Replication Mode and Log Area Full on the Secondary Site
If the system replication mode is set to SYNC - full sync , the primary system waits before committing the transaction until it gets a reply that the log is persisted on the secondary system. If the log volume is full on the secondary then the commits on the primary are halted as logs can no longer be written on the secondary. On the secondary site, the trace files contain the following error:
```
 Output Code i EventHandler LocalFileCallback.cpp(00455) : [DISKFULL] (1st request) [W] , buffer= 0x00007f7eef8ae000, offset= 589299712, size= 0/524288, file= "<root>/logsegment_000_00000508.dat" ((open, mode= RW, access= rw-rw-r--, flags= DIRECT|LAZY_OPEN), factory= (root= "/hana/log/<SID>/mnt00001/hdb00003/" (access= rw-rw-r--, flags= AUTOCREATE_DIRECTORY, usage= LOG, fs= xfs, config= (async_write_submit_active=auto,async_write_submit_blocks=new,async_read_submi t=off,num_submit_queues=1, num_completion_queues=1,size_kernel_io_queue=512,max_parallel_io_requests=64, min_submit_batch_size=16,max_submit_batch_size=64))) {shortRetries= 0, fullRetries= 0 (0/10)}
```
To quickly mitigate the situation, you can disable the 'full sync' option by running the following command:
```
>hdbnsutil -sr_fullsync --disable
```
Afterwards, the log area on the secondary site needs to be analyzed with regard to why the log segments are not freed up. This is usually caused by an erroneous log backup mechanism.
For further details refer to the following SAP Notes:
```
SAP Note 2083715 - Analyzing log volume full situations SAP Note 1679938 - Log Volume is full
```
## Possible Cause 2: Intermittent Communication Issues on the Network Layer
Check the network communication channel between the primary and secondary site for issues that can block log shipping for a significant time.
See the section in this guide SAP HANA System Replication Communication Problems for more information about identifying problems on the network layer and SAP Note 2222200 FAQ: SAP HANA Network.
## Possible Cause 3: Problems on the Secondary Site
Verify that the secondary site is online and runs stable. Check for service crashes or shutdowns that impact the availability - see SAP Note 2177064 FAQ: SAP HANA Service Restarts and Crashes.
Check for performance issues on the secondary site, for example, the memory configuration may be too small resulting in a high column unload / load rate or slow performance of the disk I/O stack. See SAP Note 2000000 FAQ: SAP HANA Performance Optimization.
## Possible Cause 4: Problems on the Primary Site
A potential problem on the primary site is I/O issues accessing the shared file system. Refer to SAP Note 1999930 FAQ: SAP HANA I/O Analysis if you suspect that this may be the cause of the problem.
## Related Information
SAP Note 2083715 SAP Note 1679938 SAP HANA System Replication Communication Problems [page 229]
## 3.18 Network Performance and Connectivity Problems
This section covers the troubleshooting of problems on the network layer.
In cases where a subjectively slow performing system behaviour is experienced, but a first analysis of the SAP HANA resource utilization does not reveal any obvious culprits, it is often necessary to analyze the network performance between the SAP HANA server host(s) and SAP Application Server(s) / Non-ABAP clients, SAP HANA nodes (inter-node communication in SAP HANA scale-out environments), or, in an SAP HANA system replication scenario, between primary and secondary site.
## 3.18.2  Stress Test with NIPING
The SAP NIPING tool is a powerful tool which can be used to perform specific network stability tests.
## Prerequisites
You must have OS level access to the SAP HANA host and the client host.
## Procedure
Read SAP Note 500235 - Network Diagnosis with NIPING .
A stress test with SAP's NIPING tool may be performed in order to confirm the high network latency (or bandwidth exhaustion).
## Related Information
## 3.18.4  SAP HANA System Replication Communication Problems
Problems during initial setup of the system replication can be caused by incorrect configuration, incorrect hostname resolution or wrong definition of the network to be used for the communication between the replication sites.
## Context
System replication environments depend on network bandwidth and stability. In case communication problems occur between the replication sites (for example, between SITE A and SITE B), the first indication of a faulty system replication setup will arise.
## Procedure
1. Check the nameserver tracefiles.
The starting point for the troubleshooting activity are the nameserver tracefiles. The most common errors found are:
##  Sample Code
```
e TNS TNSClient.cpp(00800) : sendRequest dr_secondaryactivestatus to <hostname>:<system_replication_port> failed with NetException. data=(S)host=<hostname>|service=<service_name>|(I)drsender=2| e sr_nameserver TNSClient.cpp(06787) : error when sending request 'dr_secondaryactivestatus' to <hostname>:<system_replication_port>: connection broken,location=<hostname>:<system_replication_port> e TrexNetBuffer BufferedIO.cpp(01151) : erroneous channel ### from ##### to <hostname>:<system_replication_port>: read from channel failed; resetting buffer
```
Further errors received from the remote side:
```
 Sample Code Generic stream error: getsockopt, Event=EPOLLERR - , rc=104: Connection reset by peer Generic stream error: getsockopt, Event=EPOLLERR - , rc=110: Connection timed out
```
It is important to understand that if those errors suddenly occur in a working system replication environment, they are often indicators of problems on the network layer. From an SAP HANA perspective, there is nothing that could be toggled, as it requires further analysis by a network expert. The investigation, in this case, needs to focus on the TCP traffic by recording a tcpdump in order to get a rough understanding how TCP retransmissions, out-of-order packets or lost packets are contributing to the overall network traffic. How a tcpdump is recorded is described in SAP Note 1227116 - Creating network
traces . As these errors are not generated by the SAP HANA server, please consider consulting your in-house network experts or your hardware vendor before engaging with SAP Product Support.
2. Set the parameter sr_dataaccess to debug.
In the DB Administration area of the SAP HANA cockpit open the Configuration of System Properties monitor. In the [trace] section of the indexserver.ini file set the parameter sr_dataaccess = debug . This parameter enables a more detailed trace of the components involved in the system replication mechanisms. For more information about how to change parameters, see Memory Information from Logs and Traces .
## Related Information
SAP Note 1227116
Memory Information from Logs and Traces [page 66]
## 3.19 SAP HANA Dynamic Tiering (Deprecated)
Identify and resolve specific performance issues and enhance the performance of SAP HANA dynamic tiering.
##  Note Dynamic tiering is deprecated. SAP HANA Native Storage Extension replaces it. See SAP Note 3414255 .
Troubleshooting information for SAP HANA dynamic tiering is in the SAP HANA Dynamic Tiering: Administration Guide .
## 3.19.1  Tools and Tracing
This section gives you an overview of the tools and tracing options available for SAP HANA dynamic tiering.
## 3.19.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;
```
## 3.19.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.
2. Change the execution strategy from 'auto' to 'remote':
```
ALTER SYSTEM CLEAR SQL PLAN CACHE;
```
```
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.
## 3.19.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.
## 3.19.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.
## 3.19.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.
## 3.19.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:
2. Re-enable binary load (instead of the ASCII load):
```
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'bulk_inserts_as_load') = 'true' WITH RECONFIGURE;
```
```
ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('row_engine', 'bulk_load_as_binary') = 'true' WITH RECONFIGURE;
```
## 4 Tools and Tracing
This section gives you an overview of the available tools and tracing options that are available.
## 4.1 System Performance Analysis
As a first step to resolving SAP HANA performance issues, you can analyze detailed aspects of system performance in the SAP HANA studio on the Performance tab of the Administration editor.
When analyzing system performance issues, the information provided on the Performance tab enables you to focus your analysis on the following questions:
- What and how many threads are running, what are they working on, and are any of these threads blocked?
- Are any sessions blocking current transactions?
- Are any operations running for a significantly long time and consuming a lot of resources? If so, when will they be finished?
- How do different hosts compare in terms of performance?
On the Performance tab, you can take certain actions to improve performance, including canceling the operations that cause blocking situations. See the following topics in the guide SAP HANA Administration with SAP HANA Studio
- Thread Monitoring
- Session Monitoring
- Blocked Transaction Monitoring
- Monitoring SQL Performance with the SQL Plan Cache
- Expensive Statements Monitoring
- Job Progress Monitoring
- Load Monitoring - use the load graph for performance monitoring and analysis; for example, to get a general idea about how many blocked transactions exist now and in the past, or troubleshoot the root cause of slow statement performance.
## Related Information
Monitoring and Analyzing Performance in SAP HANA Studio