My Oracle Support Banner

Query Against v$lock Run from OEM Performs Slowly (Doc ID 1328789.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.1 to 11.2]
Information in this document applies to any platform.


Query slow on v$lock run from Oracle enterprise manager:

The query is:

/* OracleOEM */ WITH blocked_resources AS
  (SELECT id1 ,
    id2 ,
    SUM(ctime)   AS blocked_secs ,
    MAX(request) AS max_request ,
    COUNT(1)     AS blocked_count
  FROM v$lock
  WHERE request > 0
  GROUP BY id1,
  ) ,
  blockers AS
  (SELECT L.* ,
    BR.blocked_secs ,
  FROM v$lock L ,
    blocked_resources BR
  WHERE BR.id1 = L.id1
  AND BR.id2   = L.id2
  AND L.lmode  > 0
  AND L.block <> 0
  ||S.serial# AS id ,
  ||', '
  ||', LOCK_TYPE:'
  ||', PROGRAM:'
  ||', MODULE:'
  ||', ACTION:'
  ||', MACHINE:'
  ||', OSUSER:'
  ||', USERNAME:'
  ||S.username AS info ,
  B.blocked_secs ,
FROM v$session S ,
  blockers B
WHERE B.sid = S.sid


To view full details, 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 a vibrant support community of peers and Oracle experts.