Enqueue – is it a PK, FK or Bitmap Index problem?
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
Trackbacks
Comments
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
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
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?