Oracle Database Discussions

Hello !

Env: redhat Linux 5, Oracle 11.2.0.3.4 single instance

we have an application that processes "messages", the message comes in and passes various steps (up to 25 steps)
for each step the application does a commit.

Only one session per message (according to the vendor) so there shouldn't be two sessions updating the same data
but we processes many messages at the same time so we have around 90 sessions performing inserts and updates.

the messages are in one table and uses blobs, for each step the blob is updated

From time to time we see enq: tx - contention

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 22507 08-Jan-13 16:30:58 294 12.4
End Snap: 22508 08-Jan-13 16:45:04 293 12.4
Elapsed: 14.08 (mins)
DB Time: 139.55 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: TX - contention 7,901 4,836 612 57.8 Other
db file sequential read 280,463 1,421 5 17.0 User I/O
DB CPU 872 10.4
log file sync 147,117 492 3 5.9 Commit
direct path write 26,445 434 16 5.2 User I/O

Enqueue Activity DB/Inst: GGBISP01/ggbisp01 Snaps: 22507-22508
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TX-Transaction
287,537 229,859 57,678 7,896 4,835 612.33
TX-Transaction (index contention)
322 322 0 287 16 55.92
TX-Transaction (row lock contention)
5 5 0 5 0 2.00
DX-Distributed Transaction
748,789 748,705 0 2 0 .00


I have read various notes on Oracle Support but are a bit puzzled. If I understand rights this enqueue
is not related to "row locks" and if I'm right it shouldn't since only one sessions update the data and row locks contention has its own event
The same for itl issues and index contention.

So my question is can I drill down further to understand what is going on here ? (like locking info when it happens)
or is this as much information I get, what conclusions should I make if that is the case ?

Appreciate your comments !

Best regards
Magnus Johansson