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

Last updated on SEPTEMBER 15, 2016

Applies to:

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

Symptoms

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,
    id2
  ) ,
  blockers AS
  (SELECT L.* ,
    BR.blocked_secs ,
    BR.blocked_count
  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
  )
SELECT B.id1
  ||'_'
  ||B.id2
  ||'_'
  ||S.sid
  ||'_'
  ||S.serial# AS id ,
  'SID, SERIAL:'
  ||S.sid
  ||', '
  ||S.serial#
  ||', LOCK_TYPE:'
  ||B.type
  ||', PROGRAM:'
  ||S.program
  ||', MODULE:'
  ||S.module
  ||', ACTION:'
  ||S.action
  ||', MACHINE:'
  ||S.machine
  ||', OSUSER:'
  ||S.osuser
  ||', USERNAME:'
  ||S.username AS info ,
  B.blocked_secs ,
  B.blocked_count
FROM v$session S ,
  blockers B
WHERE B.sid = S.sid



Changes

None.

Cause

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