How to monitor DB transactions or long running jobs or sessions

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

We need to know how to proceed to monitor database transactions/long running jobs/sessions for Oracle, MS SQL Server and/or Postgres databases.

Environment

Release : 20.3.3

Component : UIM - ORACLE

Cause

- monitoring guidance

Resolution

You can activate the 'long queries' checkpoint to collect the metrics and generate alarms. You can also create a custom query using the oracle or sqlserver probe to use your own preferred query for long running transactions or any database query for that matter.

• Use the oracle probe for Oracle.
• Use the sqlserver probe for MS SQL Server.
• Use the sql_response probe for postgreSQL
• Alternatively, you could use the jdbc_response probe to monitor postgres.

https://knowledge.broadcom.com/external/article/35831/

sqlserver allows you to monitor long queries and long running jobs.

oracle long_queries counts the number of long running queries so with oracle you would have to create your own custom checkpoint to monitor and alert on how long the query is currently running or based on historical table data.

Ask your DBA for the query they would like to use for the given database, for example refer to:

Find long running SQL queries

How to create a custom checkpoint for the oracle probe

https://knowledge.broadcom.com/external/article/14959

Additional Information

Here is a link that describes a query to monitor long running queries in postgres which you can setup in sql_response:

https://medium.com/little-programming-joys/finding-and-killing-long-running-queries-on-postgres-7c4f0449e86d

See also:

sqlserver metrics

oracle metrics