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 20, 2019
Applies to:
Enterprise Manager for Oracle Database - Version 12.1.0.4.0 and laterInformation 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| References |