RE: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts? ....)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <sbootsma@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Feb 2006 11:28:25 -0500
Hi Sam,
Not sure what SQL OEM executes, but the Tom Kyte script looks ok. I
like Steve Adams' script, which can be found at
http://www.ixora.com.au/scripts/sql/enqueue_locks.sql
Also, as to exact rowid, if a session is waiting on a specific row, then
the following columns in V$SESSION will tell you which one:
ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW#.
However, remember that not all waits are row-level waits. Waits on
enqueues of type other than TX will never be row-level waits, and even
TX enqueues may not necessarily be row-level waits.
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sam Bootsma
Sent: Wednesday, February 08, 2006 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts?
....)
Hello,
Friday afternoon, our trial tool (DBFlash) detected an enqueue problem.
I investigated the locking problem with both Oracle Enterprise Manager
and some scripts I lifted from Tom Kyte's book "Expert-One-on-One". OEM
reported that user "A" was blocking user "B". Tom Kyte's script
reported user "A" was blocking user "B" plus 15 additional users.
Although OEM did not show these 15 additional users as blocked, OEM did
show 1 of these 15 additional users was holding a row exclusive lock and
the remaining 14 users were holding row share locks.
So who do I believe? Is OEM that comes with Oracle 9i R2 known to be
buggy? Or does the script from Tom Kyte need to be tweaked for Oracle
9i? Or do they not contradict one another ... meaning the problem is
with me and my understanding is not correct?
Thanks for your input! We are running Oracle 9.2.0.6 on AIX 5.1. Here
is the script from Tom Kyte:
select
(select username from v$session where sid=a.sid) blocker, a.sid, '
is blocking ',
(select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block = 1 -- means this lock is blocking another lock
request
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
Another script from the DBFlash software agreed with the results from
Tom Kyte's script:
-- this script comes from DBFlash software
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2,
lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE
request>0)
ORDER BY id1, request
/
Does anybody have a script that provides the exact row id(s) that a
waiter is waiting on?
Thanks Again,
Sam Bootsma
Oracle DBA
George Brown College
sbootsma@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx>
416-415-5000 x4933
BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD
Other related posts:
