EM 12c: How Create Metric Extension For Blocking Session Count in Enterprise Manager 12.1.0.4 CLoud Control (Doc ID 2104115.1)

Last updated on FEBRUARY 25, 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

Enterprise Manager (EM) 12.1.0.4 can be affected by this bug:

<BUG 19793192> - Some of the Blocking Session Count Alerts are not cleared

 BUG is fixed on Enterprise Manager (EM) 12.1.0.5

On Enterprise Manager (EM) 12.1.0.4 apply Agent Bundle Patch (EM-AGENT-BP) 12.1.0.4.8 <Patch 20822942> or later.

As a workaround, a Metric Extension (ME) can be created using the following sql statement :

SELECT a.blocking_sid, SUM(num_blocked) num_blocked
FROM (SELECT id1
, id2
, MAX(DECODE(block, 1, sid, 0)) blocking_sid
, SUM(DECODE(request, 0, 0, 1)) num_blocked
FROM v$lock
WHERE block = 1
OR request > 0
GROUP BY id1, id2) a
, v$session b
WHERE a.blocking_sid=b.sid
and b.username is not NULL
GROUP BY blocking_sid

 

 

 

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