When opening and refreshing the Locks Tab in Session Browser, Toad hangs at the below background statement processing for a very long time.
SELECT lk.SID, se.username, se.OSUser, se.Machine, DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML',
'UL', 'PL/SQL User Lock', lk.TYPE) lock_type, DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)',
4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held, DECODE (lk.request, 0, 'None',
1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request))
mode_requested, TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, DECODE (block, 0,
'No', 1, 'Yes', 2, 'Global') block, se.lockwait FROM v$lock lk, v$session se WHERE (lk.TYPE = 'TX') AND (lk.SID = se.SID)
There is a known Oracle problem (Metalink Note 431770.1) that if statistics for this table exist, only a /*+ RULE */ hint in the statement helps to make the query work properly.
Change the field "All Others" in the Optimizer Hints in Toad at:
1. Select View | Toad Options | Oracle | Optimizer Hints
2. Select /* +RULE */ under All Others dropdown
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center