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.
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