SQL Queries on SAP HANA DB Runtime Dump Statistics Section Data

[Hai]

With the release of version 1.3 of the Supportability Tools for SAP HANA available in the Visual Studio Code marketplace, one significant enhancement that comes with this release is the ability to use SQL queries on the runtime dump file's statistics section data. This feature not only enables users to query individual statistics tables but also to join different tables to retrieve comprehensive results needed for in-depth analysis.Previously, users could only inspect statistics tables individually within separate tabs in the tool UI, which provided limited functionality such as sorting columns. This new SQL console capability substantially boosts the tool's utility, simplifying the process of identifying and resolving issues.

The functionality allows you to use the Statistics Viewer page to query statistics tables within a dump file. With this update, you can generate a SELECT statement to query on each statistics table, modify the SELECT statement to filter, aggregate or retrieve specific columns of data, and even save these queries for future use. For detailed instructions, please refer to the official document.

Practical Example:

Imagine the following scenario: A database administrator receives a report from an application user regarding  long-running jobs. After verifying that there are no resource overuse alerts, the administrator decides to collect a HANA DB runtime dump to investigate further. The administrator suspects that lock wait situations might be causing the delay. Here’s how end user can utilize the new SQL console to diagnose the issue:

Step 1: Identify Blocked Transactions

To check for blocked transactions, the administrator runs an SQL query on the statistics table

SELECT
  COUNT(*) "NUMBER_OF_BLOCKED_TRANSACTIONS"
FROM
  M_BLOCKED_TRANSACTIONS_

This query reveals that there are 23 blocked transactions, highlighting that lock contention could indeed be the root cause.

Step 2: Locate the Lock

Next, the administrator needs to determine where the lock is occurring:

SELECT
  SCHEMA_NAME,
  TABLE_NAME,
  WAITING_RECORD_ID,
  LOCK_OWNER_UPDATE_TRANSACTION_ID,
  LOCK_TYPE,
  COUNT(*)
FROM
  M_BLOCKED_TRANSACTIONS_
GROUP BY
  LOCK_OWNER_UPDATE_TRANSACTION_ID,
  LOCK_TYPE,
  SCHEMA_NAME,
  TABLE_NAME,
  WAITING_RECORD_ID

The query result shows that a single record in schema SAPSR3, table NRIV is locked, which causes the other 23 database transactions to wait.

Step 3: Identify the Lock Holder

To find out which transaction holds the lock and is blocking others, the administrator joins relevant tables:

SELECT
  C.TRANSACTION_ID,
  C.APPLICATION,
  C.APPLICATIONUSER,
  C.APPLICATION_SOURCE
FROM
  "M_CONNECTIONS_" C
WHERE
  C.TRANSACTION_ID IN (
    SELECT
      T.TRANSACTION_ID
    FROM
      "M_BLOCKED_TRANSACTIONS_" BT
      JOIN "M_TRANSACTIONS_" T ON BT.LOCK_OWNER_UPDATE_TRANSACTION_ID = T.UPDATE_TRANSACTION_ID
  )

This query enables the administrator to pinpoint the specific transaction and user who holds the lock, providing the necessary details to take corrective action.

Conclusion

This new functionality is a massive leap in diagnosing and troubleshooting issues with runtime dump files. With the ability to execute SQL queries on runtime dump file statistics section data, end user can conduct more thorough and efficient investigations. For a deeper dive into SQL syntax, refer to the official SQLite documentation.

We encourage you to try out this powerful feature and share your feedback to help us further enhance the tool.

Happy querying!