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 JUNE 05, 2017

Applies to:

Enterprise Manager for Oracle Database - Version 12.1.0.4.0 and later
Information in this document applies to any platform.

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms