enqueue wait events, locks and the meaning of id1/id2

Discussion:

(too old to reply)

Bjorn Augestad

2004-01-27 18:23:02 UTC

Hello,

Statspack telles me that my Oracle instance (8.0.5) has lots of enqueue
wait events and each event is quite costly, averaging 2200 ms.

The enqueue lock type is TX, with lock mode 6 (exclusive).

I'm trying to figure out the names of the objects locked, but cannot
find any documentation on how to interpret v$lock.id1 and id2 when the
lock type is TX. Can anyone please help?

Thanks in advance.
Bjørn

Bricklen

2004-01-27 18:33:16 UTC

Permalink

Post by Bjorn Augestad
Hello,
Statspack telles me that my Oracle instance (8.0.5) has lots of enqueue
wait events and each event is quite costly, averaging 2200 ms.
The enqueue lock type is TX, with lock mode 6 (exclusive).
I'm trying to figure out the names of the objects locked, but cannot
find any documentation on how to interpret v$lock.id1 and id2 when the
lock type is TX. Can anyone please help?
Thanks in advance.
Bjørn

Maybe this will help a bit:

SELECT a.object_name,
a.owner,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S - Sub Share (SS)',
3, 'Row-X - Sub Exclusive (SX)',
4, 'Share (S)',
5, 'S/Row-X - Share/Sub Exclusive (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.session_id sid,
b.oracle_username,
b.os_user_name
FROM all_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1;

Brian Peasland

2004-01-27 18:44:06 UTC

Permalink

In the case of a TX lock, the ID1 and ID2 columns do not directly
translate to a specific object. Rather, it points to a specific
transaction in your rollback segment. The TX lock does not directly lock
the object. The following query can help you determine which transaction
is requesting this lock:

SELECT TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq
FROM v$lock
WHERE sid=8 AND type='TX';

Use the appropriate SID in the above query for your session.

Note that when a transaction gets a TX lock, it also has a TM lock which
stops DML on the locked object. So for the same SID, you should find a
TM lock. For the TM lock of that SID, the ID1 column is the OBJECT_ID
found in DBA_OBJECTS. This is a good way to find out which objects a
session has locked.

HTH,
Brian

--
===================================================================

Brian Peasland
***@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Bjorn Augestad

2004-01-28 11:37:02 UTC

Permalink

First of all, let me say that usenet is great. Yesterday I posted a
question, and 15 minutes later I have several replies. Amazing. Thanks
to everyone making usenet so usable and valuable. :-)

Post by Brian Peasland
In the case of a TX lock, the ID1 and ID2 columns do not directly
translate to a specific object. Rather, it points to a specific
transaction in your rollback segment. The TX lock does not directly lock
the object. The following query can help you determine which transaction
SELECT TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq
FROM v$lock
WHERE sid=8 AND type='TX';
Use the appropriate SID in the above query for your session.

I modified the query a bit, to this:
SELECT
TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,65535)+0 slot, id2 seq
FROM v$lock
WHERE type='TX'
AND lmode = 6

This gives me the following output:
RBS SLOT SEQ
---------- ---------- ----------
6 80 1195257
6 70 1192089
6 42 1193083
5 72 1060150
5 96 1055042
6 16 1190901
5 14 1056651
5 9 1058258
6 1 1196554
5 51 1057005
5 1 1056275

RBS SLOT SEQ
---------- ---------- ----------
6 9 1189959

Is it possible to join this info with e.g. v$transaction to find the object?

Post by Brian Peasland
Note that when a transaction gets a TX lock, it also has a TM lock which
stops DML on the locked object. So for the same SID, you should find a
TM lock.

Hmm, I cannot find a TM lock for each TX lock. As a matter of fact, I
have zero TM locks. :-(

The number of TX locks varies, and for most of the time the lock is not
blocking other sessions. I've monitored v$lock and other sessions are
mostly able to aquire TM locks, but sometimes the TX lock changes from
non-blocking to blocking, causing other sessions to wait for a long time
(most likely timing out). I believe this is the cause for the long
enqueue wait times we have.

Some more facts:
- We have one 8.0.5 instance running on one server (Tru64)
- We have two application servers(Oracle App server 4.0) on two other
servers(Solaris)
- The app servers are the processes holding the TX locks
- The TX locks are helt until the app server process is restarted.
- I've joined v$lock with v$session and v$sqltext, and all sessions
holding TX locks are/was running PL/SQL procedures.
- We have lots of buffer busy requests as well, almost all of them of
type Data Block.

My current working theory is that the app server for some reason fails
to either commit or rollback a transaction, causing the TX lock to block
other transactions. The app server also fails to return the db
connection handle to the connection pool, causing the application to
slow down and ultimately fail due to lack of connections. The restart of
the app server process 'fixes' the symptoms, but not the cause.

The application running on the app servers is a mix of off the shelf SW
(Oracle iStore v.3 or 4) and custom code written by consultants no
longer available. I'm sure it'll be a lot of fun trying to debug that
mess...

For the TM lock of that SID, the ID1 column is the OBJECT_ID

Post by Brian Peasland
found in DBA_OBJECTS. This is a good way to find out which objects a
session has locked.

As I wrote earlier, the TX lock has no matching TM lock, but I'll try to
use the ID1 column for the TM lock for the blocked session. Hopefully
that'll give me some clue.

Thanks a lot for your help. I appreciate it a lot. :-)
Bjørn

Post by Brian Peasland
HTH,
Brian

Brian Peasland

2004-01-28 15:34:40 UTC

Permalink

Post by Bricklen
SELECT
TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,65535)+0 slot, id2 seq
FROM v$lock
WHERE type='TX'
AND lmode = 6
RBS SLOT SEQ
---------- ---------- ----------
6 80 1195257
6 70 1192089
6 42 1193083
5 72 1060150
5 96 1055042
6 16 1190901
5 14 1056651
5 9 1058258
6 1 1196554
5 51 1057005
5 1 1056275
RBS SLOT SEQ
---------- ---------- ----------
6 9 1189959

You might also want to include the SID in your query of V$LOCK so you
know which session these belong to. Also, the LMODE and REQUEST columns
are useful. If LMODE != 0, then a locked is obtained. If REQUEST!=0,
then the session is waiting on a lock.

Post by Bricklen
Is it possible to join this info with e.g. v$transaction to find the object?

Not to find the object. But the RBS, SLOT, and SEQ columns above match
the XIDUSN, XIDSLOT, and XIDSQN columns of V$TRANSACTION, respectively.

Alternatively, to find the object, query V$LOCKED_OBJECT. The RBS, SLOT,
and SEQ columns above match the XIDUSN, XIDSLOT, and XIDSQN columns of
V$LOCKED_OBJECT, respectively. From there, you should be able to get the
OBJECT_ID, which maps to DBA_OBJECTS.

HTH,
Brian
--
===================================================================

Brian Peasland
***@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Venkat

2004-01-28 18:42:35 UTC

Permalink

I don't have access to Oracle 8.0.5 so couldn't check if the
ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# columns exist in
V$SESSION. If these columns are there, it would already have all the
information required to understand the TX enq row lock waits, no?

-V

Post by Brian Peasland

Post by Bricklen
Is it possible to join this info with e.g. v$transaction to find the object?

Bjorn Augestad

2004-01-29 12:48:03 UTC

Permalink

Post by Venkat
I don't have access to Oracle 8.0.5 so couldn't check if the
ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# columns exist in
V$SESSION. If these columns are there, it would already have all the
information required to understand the TX enq row lock waits, no?

The columns exist, but they're all either -1 or 0 for the TX locks.

Bjørn

[snip]

Bjorn Augestad

2004-01-30 09:52:32 UTC

Permalink

Post by Brian Peasland

Post by Bricklen
Is it possible to join this info with e.g. v$transaction to find the object?

Not to find the object. But the RBS, SLOT, and SEQ columns above match
the XIDUSN, XIDSLOT, and XIDSQN columns of V$TRANSACTION, respectively.

I joined v$lock with v$transaction, but wasn't able to make much sense
of the output. UBAFIL+UBABLK is either 0 or refers to a RBS, and
START_UBAFIL+START_UBABLK always refers to a RBS. (That makes sense, I
guess.)

Post by Brian Peasland
Alternatively, to find the object, query V$LOCKED_OBJECT. The RBS, SLOT,
and SEQ columns above match the XIDUSN, XIDSLOT, and XIDSQN columns of
V$LOCKED_OBJECT, respectively. From there, you should be able to get the
OBJECT_ID, which maps to DBA_OBJECTS.

Hmm, it turns out that V$LOCKED_OBJECT is empty.

Some more facts & questions.
- The instance is set up with TRANSACTIONS = 489 and
TRANSACTIONS_PER_ROLLBACK_SEGMENT=49, but has only two rollback
segments(+ SYSTEM). Can this cause problems?

- The two RBS are IMHO rather large, 2GB, but the DBA insists that they
must be that big. The extent size is 4MB and block size is 8KB. Comments?

- A brute force trace of all blocked TM transactions identified a few
frequently blocked tables. These tables all have triggers which update
two columns with SYSDATE and USER. Will the precence of triggers create
multiple entries on the RBS? Other comments? ;-)

- In addition to the enqueue waits we enjoy, we also have lots of buffer
busy waits of type Data Block. I've identified the tables/indexes which
we have Data Block waits for, and they happen to be the same tables
which (probably) are blocked by the TX transactions. I plan to alleviate
the data block waits by adding more freelists to the tables/indexes and
bump the PCTFREE from 10 to 40. My hope is that this change may also fix
the enqueue problems...

- At least one of the indexes we wait for is of type BITMAP. I vaguely
remember that there are issues with bitmap indexes, but cannot remember
exactly what those issues are. Any pointers?

Thanks again.

Regards,
Bjørn

Post by Brian Peasland
HTH,
Brian