Enqueue – is it a PK, FK or Bitmap Index problem?

November 30th, 2012

If one is seeing  ‘enq: TX – row lock contention’ there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4)  it’s typically going to be

  • inserting a unique key when  someone else has already inserted that key but not committed
  • Inserting a foreign when then parent value has been inserted but not committed or deleted and not commited (not to be confused with locks due to un-indexed foreign key which cause a “enq: TM – contention”  wait not a TX wait)
  • bitmap index chunk contention

Now how to tell which of these is happening? Well here is a query on ASH (I’ve commented out some of the useful fields to limit the output)  and a results cheat sheet:

col object for A15
col otype for A10
select
       substr(event,0,20)    lock_name,
       --ash.session_id        waiter,
       --mod(ash.p1,16)        lmode,
       --ash.p2                p2,
       --ash.p3                p3,
       o.object_name           object,
       o.object_type           otype,
       CURRENT_FILE#           filen,
       CURRENT_BLOCK#          blockn,
       --ash.SQL_ID            waiting_sql,
       BLOCKING_SESSION        blocker
       --,ash.xid
from
         v$active_session_history ash,
         all_objects o
where
           event like 'enq: TX%'
   and   mod(ash.p1,16)=4
   and o.object_id (+)= ash.CURRENT_OBJ#
/

uniq index

  OBJECT   OTYPE FILEN BLOCKN BLOCKER
  ------  ------ ----- ------ -------
      -1             0      0     158
      -1             0      0     158
      -1             0      0     158
      -1             0      0     158

FK 

  OBJECT   OTYPE FILEN BLOCKN BLOCKER
  ------  ------ ----- ------ -------
   CHILD   TABLE     1  60954       1
   CHILD   TABLE     1  60954       1
   CHILD   TABLE     1  60954       1

bitmap

  OBJECT   OTYPE FILEN BLOCKN BLOCKER
  ------  ------ ----- ------ -------
      I1   INDEX     0      0     144
      I1   INDEX     0      0     144
      I1   INDEX     0      0     144
      I1   INDEX     0      0     144

Each case has a different footprint.

  • unique key index issue object of “-1″
  • foreign key case has a blocker of “1″
  • bitmap index case as filen and blockn “0″

These cases were run on 10.2.0.3 thus the “footprint” could change on other versions.

The above ASH query and many other useful ASH queries are maintained on GitHub at

https://github.com/khailey/ashmasters


Uncategorized

  1. Trackbacks

  1. Comments

  2. December 1st, 2012 at 21:51 | #1

    Kyle,

    Nice test cases and observations.

    In my experience, isolated tests cases which result in a “-1″ for object id will often result in a misleading object id in real-world scenarios because in the test case the “-1″ will be something that was not relevantly populated in the run up to the tested scenario whereas often in the real world, the immediately preceding event had an appropriate object id set and just not overwritten.

    Not sure if that description makes as much sense as I’d like it to.
    But, for example, I modelled a real-world ETL problem recently here:
    https://orastory.wordpress.com/2012/11/02/modelling-a-simple-itl-problem/
    where my test case resulted in a “-1″ object id but the real world ash capture was just am id from a previous wait that was not overwritten by the itl wait.

    Cheers,
    Dominic

  3. December 3rd, 2012 at 23:30 | #2

    Hi Dominic,

    Yes, there are issues with some fields in ASH not getting cleared out from previous waits which is unfortunate. Some fields such as

    CURRENT_OBJ#
    CURRENT_FILE#
    CURRENT_BLOCK#
    CURRENT_ROW#

    only get populated for certain waits. Those waits are

    Buffer Busy Waits
    IO Waits
    Enqueue TX

    So for example, although it would be tempting to use these for something like “latch: cache buffers chains” it most likely will be misleading and left over from previous waits.

    If there is a “-1″ in the current object especially withe current file and block being 0, I interpret that as the field actually being successfully cleared out.
    Since above waits are TX enqueue waits i would expect the data to be dependable but I have found issues as well. For example with buffer busy waits on temporary table space header block, the object number is sometimes filled, when the problem is not a object per say. Here is output form ASH (the query is on ashmasters.com called ash_bbw.sql

    Time P1 P2 OBJN OTYPE FN BLOCKN BLOCK_TYPE
    —– — — —- —– — —— —————–
    11:44 202 2 TOTO TABLE 1 60218 file header bloc

    The P1 is the file number. This database only had 10 datafiles, so file #202 is a temporary file, not a data file. Temporary datafiles start counting at db_files+tempfile_number. The block type is correct but the CURRENT_OBJ#, CURRENT_FILE# , CURRENT_BLOCK# are all incorrect even though Oracle is suppose to fill these in for buffer busy waits AFAIK. Any know if Oracle clearly documents when CURRENT_OBJ# , CURRENT_FILE# ,CURRENT_BLOCK# are suppose to be filled in?
    But the whole point of the above locking examples on index was to show that they have a distinct profile, at least in this version of Oracle, 10.2.0.3, but that due to the nature of ASH and instrumentation changes this profile might be different in different versions.
    - Kyle

  4. August 16th, 2017 at 18:06 | #3

    I was curious if you ever considered changing the layout of your site? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or two images. Maybe you could space it out better?


+ 5 = eleven