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

From: Bjorn Augestad <root_at_localhost.com>
Date: Fri, 30 Jan 2004 10:52:32 +0100
Message-ID: <APpSb.475$_c4.6501@news4.e.nsc.no>

Brian Peasland wrote:

>>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

>
>
> 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.
>
>

>>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.)

>
> 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

>
>
> HTH,
> Brian
>
>
Received on Fri Jan 30 2004 - 03:52:32 CST