EM 12c, EM 13c: Is it possible to Monitor Long Running Database Queries using Enterprise Manager Cloud Control Metrics (Doc ID 2021457.1)

Last updated on MARCH 20, 2023

Applies to:

Goal

Often Oracle Support are asked if it is possible to monitor and alert on long running queries via Enterprise Manager Cloud Control metrics.

However from a performance perspective, there is not necessarily anything wrong with a long running query. What is important is how intensively something runs, and whether it affects other operations/processes.   For example, there might be a process which takes a long time to run, but doesn't do much and affects nothing, or there may be a query which normally runs very quickly which is suddenly running slowly, it is more important to investigate the reason for the change in behaviour.  Investigating certain SQL statements which take over a certain (arbitrary) amount of time to run, may not be useful in the long term.

Given below are three different metrics which may be of use when run alongside conventional performance monitoring.  Also please note that Enterprise Manager also provides SQL Monitoring via The Database Home Page/Performance/SQL Monitoring.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.