SAP HANA Cloud: What’s New (and Changed) – Monitoring Session, Threads, and Statements

Video Tutorial

In this second video tutorial about monitoring SAP HANA Cloud with SAP HANA cockpit, we take a look at the remaining cards and apps

• Sessions
• Threads
• Blocked Transactions
• Monitor Statements
• Table Usage

What’s Changed?

Compared to the SAP HANA cockpit for SAP HANA platform (on-premise), the following changes have been made:

• Configure Expensive Statement Trace is removed from Monitor Statements as file-based tracing is no longer available for SAP HANA Cloud.

SAP HANA Cloud – SAP HANA Cockpit: Sessions and Threads

System Overview

Monitoring Sessions, Threads, and SQL Statements

The Monitoring and Administration topic area in System Overview includes

• Sessions
• Threads
• Top SQL Statements (Monitor Statements)
• Monitoring: Open blocked transactions
• Monitoring: Monitor Table Usage

When selected, the cards open the corresponding related apps with the same name.

For the documentation, see

• Cards Available on the System Overview Page
• Monitoring Performance in SAP HANA Cockpit
• Analyzing System Performance, SAP HANA Cloud Administration Guide

System Overview

Sessions

The Sessions app enables us to monitor session activity in the database with detailed information about each session (34 different statistics available for selection). The Summary header shows several aggregates (Top 5). When a session is selected, we can cancel the session (requires SESSION ADMIN system privilege) or navigate to the Threads or the Blocked Transactions app with the same connection ID.

For the documentation, see

• Session Monitoring, Cockpit Guide
• Session-Specific Information for Connections, Administration Guide

Recommended KBAs

• 2092196 – How-To: Terminating Sessions in SAP HANA

Sessions

Threads

The Threads app enables us to monitor thread activity in the database with detailed information about each thread (57 different statistics available for selection). The Summary header shows several aggregates (Top 5). When a thread is selected, we can cancel the operation (requires SESSION ADMIN system privilege) or navigate to the Sessions or the Blocked Transactions app with the same connection ID. In addition, we can request the call stack for a particular thread (use case: SAP Support request).

Recommended KBAs (lists and describes the different type of threads available)

• 2114710 – FAQ: SAP HANA Threads and Thread Samples

Threads

Blocked Transactions

The Blocked Transaction app enables us to view details information about blocked transactions, when available (51 different statistics available for selection). When a transaction is selected, we can navigate to the Sessions or the Threads app with the same connection ID.

Recommended KBAs

• 1999998 – FAQ: SAP HANA Lock Analysis

Blocked Transactions

--session 1:
CREATE TABLE X (A INT);
INSERT INTO X VALUES (0);
COMMIT;
-- setting 10 minute timeout
SET TRANSACTION LOCK WAIT TIMEOUT 600000;
UPDATE X SET A = 1;   

--session 2:
SELECT A FROM X FOR SHARE LOCK

Simple Test Case

Related Apps

Sessions, Threads, and Blocked Transactions are related apps and we can easily navigate between the apps by using either the lateral navigation menu in the header or the context aware Navigate To … menu in the footer which preserves the connection ID.

Lateral Navigation

Navigate To…

Monitor Statements

The Monitor Statement app enables us to statement activity in the database with detailed information about each statement (overview, active statements, SQL Plan Cache, Expensive Statements). The Top SQL Statement card on System Overview opens this app.

From the SQL Plan Cache tab, we can save SQL Statement execution plans as PLV files or open the plan directly in SQL Analyzer.

For the documentation, see

• Monitor Statements, Cockpit Guide

Recommended KBAs (lists and describes the different type of threads available)

• 2000002 – FAQ: SAP HANA SQL Optimization

Overview

Monitor Statements – Overview

Monitor Statements – SQL Plan Cache

Table Usage

The Table Usage app displays a bubble graph (and table view) of the largest column tables in the database. A filter selection can be made on reads, writes, read/writes, and size.

For the documentation, see

• Monitoring Table Usage

Table Usage

SAP HANA Database Explorer

SQL Console and Statement Library

The information displayed in the Sessions, Threads, and Blocked Transactions apps is populated from M_SESSION_CONTEXT, M_SERVICE_THREADS, and M_BLOCKED_TRANSACTIONS and related system monitoring views, which you can query using the SQL Console in SAP HANA Database Explorer.

SQL Console

Although not specific to SAP HANA Cloud, the Statement Library of the SAP HANA Database Explorer also contains many statements and script that can be useful for performance analysis. Due to changes in the catalog, some statements return errors and require editing.

For the documentation, see

• Use the Statement Library to Administer Your Database, SAP Cloud Platform, SAP HANA Service (not yet available for SAP HANA Cloud)

Recommended KBA

• 1969700 – SQL Statement Collection for SAP HANA

Statement Library

Learned Something New?  

Post a comment, share on social media, and/or give a like. That’s how the community works. Thanks.

If you would like to receive updates, connect with me on

• LinkedIn > linkedin.com/in/dvankempen
• Twitter > @dvankempen

Best,

Denys van Kempen

Original Article:
https://blogs.sap.com/2020/04/20/sap-hana-cloud-whats-new-and-changed-monitoring-session-threads-and-statements/