• No results found

System Performance Analysis

4 Tools and Tracing

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.

4.1.1 Thread Monitoring

You can monitor all running threads in your system in the Administration editor on the Performance Threads sub-tab. It may be useful to see, for example, how long a thread is running, or if a thread is blocked for an inexplicable length of time.

Thread Display

By default, the Threads sub-tab shows you a list of all currently active threads with the Group and sort filter applied. This arranges the information as follows:

● Threads with the same connection ID are grouped.

● Within each group, the call hierarchy is depicted (first the caller, then the callee).

● Groups are displayed in order of descending duration.

On big systems with a large number of threads, this arrangement provides you with a more meaningful and clear structure for analysis. To revert to an unstructured view, deselect the Group and sort checkbox or change the layout in some other way (for example, sort by a column).

Thread Information

Detailed information available on the Threads sub-tab includes the following:

● The context in which a thread is used

This is indicated by the thread type. Important thread types are SqlExecutor and PlanExecutor.

SqlExecutor threads handle session requests such as statement compilation, statement execution, or result fetching issued by applications on top of SAP HANA. PlanExecutor threads are used to process column-store statements and have an SqlExecutor thread as their parent.

Note

With revision 56, PlanExecutor threads were replaced by JobWorker threads.

Note

The information in the Thread Type column is only useful to SAP Support for detailed analysis.

● What a thread is currently working on

The information in Thread Detail, Thread Method, and Thread Status columns is helpful for analyzing what a thread is currently working on. In the case of SqlExecutor threads, for example, the SQL statement currently being processed is displayed. In the case of PlanExecutor threads (or JobWorker threads as of revision 56), details about the execution plan currently being processed are displayed.

Note

The information in the Thread Detail, Thread Method, and Thread Status columns is only useful to SAP Support for detailed analysis.

● Information about transactionally blocked threads

A transactionally blocked thread is indicated by a warning icon ( ) in the Status column. You can see detailed information about the blocking situation by hovering the cursor over this icon.

A transactionally blocked thread cannot be processed because it needs to acquire a transactional lock that is currently held by another transaction. Transactional locks may be held on records or tables.

Transactions can also be blocked waiting for other resources such as network or disk (database or metadata locks).

The type of lock held by the blocking thread (record, table, or metadata) is indicated in the Transactional Lock Type column.

The lock mode determines the level of access other transactions have to the locked record, table, or database. The lock mode is indicated in the Transactional Lock Type column.

Exclusive row-level locks prevent concurrent write operations on the same record. They are acquired implicitly by update and delete operations or explicitly with the SELECT FOR UPDATE statement.

Table-level locks prevent operations on the content of a table from interfering with changes to the table definition (such as drop table, alter table). DML operations on the table content require an intentional exclusive lock, while changes to the table definition (DDL operations) require an exclusive table lock.

There is also a LOCK TABLE statement for explicitly locking a table. Intentional exclusive locks can be acquired if no other transaction holds an exclusive lock for the same object. Exclusive locks require that no other transaction holds a lock for the same object (neither intentional exclusive nor exclusive).

For more detailed analysis of blocked threads, information about low-level locks is available in the columns Lock Wait Name, Lock Wait Component and Thread ID of Low-Level Lock Owner. Low-level locks are locks acquired at the thread level. They manage code-level access to a range of resources (for example, internal

data structures, network, disk). Lock wait components group low-level locks by engine component or resource.

The Blocked Transactions sub-tab provides you with a filtered view of transactionally blocked threads.

Monitoring and Analysis Features

To support monitoring and analysis, you can perform the following actions on the Threads sub-tab:

● See the full details of a thread by right-clicking the thread and choosing Show Details.

● End the operations associated with a thread by right-clicking the thread and choosing Cancel Operations.

Note

This option is not available for threads of external transactions, that is those with a connection ID of -1.

● Jump to the following related objects by right-clicking the thread and choosing Navigate To <related object> :

○ Threads called by and calling the selected thread

○ Sessions with the same connection ID as the selected thread

○ Blocked transactions with the same connection ID as the selected thread

● View the call stack for a specific thread by selecting the Create call stacks checkbox, refreshing the page, and then selecting the thread in question.

Note

The information contained in call stacks is only useful to SAP Support for detailed analysis.

● Activate the expensive statements trace, SQL trace, or performance trace by choosing Configure Trace

<required trace> .

The Trace Configuration dialog opens with information from the selected thread automatically entered (application and user).

Note

If the SQL trace or expensive statements trace is already running, the new settings overwrite the existing ones. If the performance trace is already running, you must stop it before you can start a new one.

Related Information

SQL Trace [page 141]

Performance Trace [page 166]

Expensive Statements Trace [page 144]

SAP HANA SQL and System Views Reference

4.1.2 Blocked Transaction Monitoring

Blocked transactions, or transactionally blocked threads, can impact application responsiveness. They are indicated in the Administration editor on the Performance Threads tab. You can see another

representation of the information about blocked and blocking transactions on the Blocked Transactions sub-tab.

Information About Blocked Transactions

Blocked transactions are transactions that are unable to be processed further because they need to acquire transactional locks (record or table locks) that are currently held by another transaction. Transactions can also be blocked waiting for other resources such as network or disk (database or metadata locks).

The type of lock held by the blocking transaction (record, table, or metadata) is indicated in the Transactional Lock Type column.

The lock mode determines the level of access other transactions have to the locked record, table, or database.

The lock mode is indicated in the Transactional Lock Type column.

Exclusive row-level locks prevent concurrent write operations on the same record. They are acquired implicitly by update and delete operations or explicitly with the SELECT FOR UPDATE statement.

Table-level locks prevent operations on the content of a table from interfering with changes to the table definition (such as drop table, alter table). DML operations on the table content require an intentional exclusive lock, while changes to the table definition (DDL operations) require an exclusive table lock. There is also a LOCK TABLE statement for explicitly locking a table. Intentional exclusive locks can be acquired if no other transaction holds an exclusive lock for the same object. Exclusive locks require that no other transaction holds a lock for the same object (neither intentional exclusive nor exclusive).

For more detailed analysis of blocked transactions, information about low-level locks is available in the columns Lock Wait Name, Lock Wait Component and Thread ID of Low-Level Lock Owner. Low-level locks are locks acquired at the thread level. They manage code-level access to a range of resources (for example, internal data structures, network, disk). Lock wait components group low-level locks by engine component or resource.

Monitoring and Analysis Features

To support monitoring and analysis, you can perform the following actions on the Blocked Transactions sub-tab:

● Jump to threads and sessions with the same connection ID as a blocked/blocking transaction by right-clicking the transaction and choosing Navigate To <related object> .

● Activate the performance trace, SQL trace, or expensive statements trace for the blocking transaction (that is the lock holder) by choosing Configure Trace <required trace> .

The Trace Configuration dialog opens with information from the selected thread automatically entered (application and user).

Note

If the SQL trace or expensive statements trace is already running, the new settings overwrite the existing ones. If the performance trace is already running, you must stop it before you can start a new one.

Related Information

SQL Trace [page 141]

Performance Trace [page 166]

Expensive Statements Trace [page 144]

SAP HANA SQL and System Views Reference

4.1.3 Session Monitoring

You can monitor all sessions in your landscape in the Administration editor on the Performance Sessions sub-tab.

Session Information

The Sessions sub-tab allows you to monitor all sessions in the current landscape. You can see the following information:

● Active/inactive sessions and their relation to applications

● Whether a session is blocked and if so which session is blocking

● The number of transactions that are blocked by a blocking session

● Statistics like average query runtime and the number of DML and DDL statements in a session

● The operator currently being processed by an active session (Current Operator column).

Note

In earlier revisions, you can get this information from the SYS.M_CONNECTIONS monitoring view with the following statement:

SELECT CURRENT_OPERATOR_NAME FROM M_CONNECTIONS WHERE CONNECTION_STATUS = 'RUNNING'

Tip

To investigate sessions with the connection status RUNNING, you can analyze the SQL statements being processed in the session. To see the statements, ensure that the Last Executed Statement and Current Statement columns are visible. You can then copy the statement into the SQL console and analyze it using

the Explain Plan and Visualize Plan features. It is also possible to use the SQL plan cache to understand and analyze SQL processing.

Monitoring and Analysis Features

To support monitoring and analysis, you can perform the following actions on the Sessions sub-tab:

● Cancel a session by right-clicking the session and choosing Cancel Session...

● Jump to the following related objects by right-clicking the session and choosing Navigate To <related object> :

○ Threads with the same connection ID as the selected session

○ Blocked transactions with the same connection ID as the selected session

● Activate the performance trace, SQL trace, or expensive statements trace by choosing Configure Trace

<required trace> .

The Trace Configuration dialog opens with information from the selected session automatically entered (application and user).

Note

If the SQL trace or expensive statements trace is already running, the new settings overwrite the existing ones. If the performance trace is already running, you must stop it before you can start a new one.

Related Information

SQL Trace [page 141]

Performance Trace [page 166]

Expensive Statements Trace [page 144]

SAP HANA SQL and System Views Reference

4.1.4 Job Progress Monitoring

Certain operations in SAP HANA typically run for a long time and may consume a considerable amount of resources. You can monitor long-running jobs in the Administration editor on the Performance Job Progress sub-tab.

By monitoring the progress of long-running operations, for example, delta merge operations and data compression, you can determine whether or not they are responsible for current high load, see how far along they are, and when they will finish.

The following information is available, for example:

● Connection that triggered the operation (CONNECTION_ID)

● Start time of the operation (START_TIME)

● Steps of the operation that have already finished (CURRENT_PROGRESS)

● Maximum number of steps in the operation (MAX_PROGRESS)

For more information about the operations that appear on the Job Progress sub-tab, see system view M_JOB_PROGRESS.

Related Information

SAP HANA SQL and System Views Reference

4.1.5 Load Monitoring

A graphical display of a range of system performance indicators is available in the Administration editor on the Performance Load sub-tab.

You can use the load graph for performance monitoring and analysis. For example, you can use it 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

SAP HANA Troubleshooting and Performance Analysis Guide