SQL statements processing a high amount of data, or using inefficient processing strategies, can be responsible for increased memory requirements.
How to Analyze SQL Statements
A key step in identifying the source of poor performance is understanding how much time SAP HANA spends on query execution. By analyzing SQL statements and calculating their response times, you can better understand how the statements affect application and system performance.
You can analyze the response time of SQL statements using the following:
Monitor Statements
SQL Trace
From the trace file, you can analyze the response time of SQL statements.
Expensive Statements Trace
On the Expensive Statements tab, you can view a list of all SQL statements that exceed a specified response time.
In addition, you can analyze the SQL plan cache, which provides a statistical overview of the statements are executed in the system.
The Monitor Statements Application
The SQL Statements card displays the number of long-running statements and long-running blocking situations currently active in the database. Statements are ranked based on a combination of the following criteria:
Runtime of the current statement execution
Lock wait time of the current statement execution
Cursor duration of the current statement execution
Open the Monitor Statements page by choosing either the long-running statements or long-running blocked statements on the card. The Monitor Statements page allows you to analyze the most current statements running in the database. Here you can see the following information:
The 100 most critical statements, listed in order of the longest runtime
The full statement string and ID of the session in which the statement is running
The application, the application user, and the database running the statement
Whether a statement is related to a blocking transaction
To support monitoring, you can perform the following actions on the Monitor Statements page:
If a statement is in a blocked transaction or using an excessive amount of memory, you can cancel the session that the statement is running in (or the blocked session) by choosing Cancel Session in the footer toolbar.
To access information about the memory consumption of statements, choose Enable Memory Tracking in the footer toolbar.
To set up or modify workload classes, choose a statement's Workload Class name. To create a new workload class, choose New, or, to select a workload class from a list, choose Existing, and fill out the fields.
Use and Analyze the Expensive Statements Trace
Use the Expensive Statements trace to analyze individual SQL queries whose execution time is above a configured threshold. Analyzing expensive statements can help you to understand why they exceed duration thresholds.
By default, the Expensive Statements trace is deactivated.
You can find the Expensive Statements trace in the Monitor Statements application.
Use the Expensive Statements trace to analyze individual SQL queries whose execution time was above a configured threshold. The Expensive Statements trace records information and displays it on the Expensive Statements tab.
The Expensive Statements trace records information about the expensive statements for further analysis and displays it on the Expensive Statements tab.
To support monitoring and analysis, you can perform the following actions on the Expensive Statements Trace page:
The expensive statements trace is deactivated by default. To activate and configure it, in the footer bar, choose Configure Trace.
Define the monitored date.
Filter expensive statements, refresh the list, choose the sorting parameter, and filter by parameter.
To save the data sets as a text or HTML files, choose Save As...
To configure the threshold parameters, choose Configure Trace and enter information on the Configure Expensive Trace page.
To open an expensive statement with the SQL analyzer, next to the statement string, choose More.
Set up or modify workload classes by choosing a statement's workload class name. To create a new workload class, choose New, or to select a workload class from a list, choose Existing, and fill out the fields.
Use and Analyze the SQL Plan Cache
Use the SQL plan cache to get an insight into the workload of the SAP HANA database. It lists all statements currently cached in the SAP HANA database.
Analyzing all statements currently cached in the SAP HANA database can help you to identify statement hashes, as well as determining if a statement has been cached correctly.
You can find the SQL Plan Cache tab in the Monitor Statements application.
Technically, the plan cache stores compiled execution plans of SQL statements for reuse, which gives a performance advantage over recompilation at each invocation. For monitoring reasons, the plan cache keeps statistics about each plan, for example, the number of executions, minimum/maximum/total/average runtime, and lock/wait statistics. Analyzing the plan cache is very helpful as one of the first steps in performance analysis because it gives an overview of the statements that are executed in the system.
Note
Due to the nature of a cache, seldom-used entries are removed from the plan cache.
The SQL plan cache is useful for observing overall SQL performance because it provides statistics on compiled queries. You can get an insight into frequently executed queries and slow queries with a view to finding potential candidates for optimization.
To support monitoring and analysis, you can perform the following actions on the SQL Plan Cache page:
To open an SQL statement with the SQL analyzer, next to the statement string, choose More.
To save the data sets as a text or HTML file, choose Save As...
The collection of SQL plan cache statistics is enabled by default. To disable it, choose Configure.