How to monitor DB transactions or long running jobs or sessions

book

Article ID: 225576

calendar_today

Updated On:

Products

CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management for z Systems CA Unified Infrastructure Management SaaS (Nimsoft / UIM) NIMSOFT PROBES

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.

Cause

- monitoring guidance

Environment

Release : 20.3.3

Component : UIM - ORACLE

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

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