APEX

I'm hosting an APEX app on one of my Oracle servers

Oracle 10.1.0.4.0
APEX 3.0.0


The users (and APEX developer) are complaining about slow performance. One of the primary reasons seems to be as a result of row lock contention. And as this is on an APEX table, I think it is fair to think that this is not a finger-up-nose mistake from the APEX developer.

This is what I for example just saw some minutes ago. 3 APEX sessions running for application 103 and page 1 according to V$SESSION.

Wait status for all 3 sessions (ticking over into many minutes) is:
enq: TX - row lock contention

The one session shows this SQL:
INSERT INTO WWV_FLOW_DATA ( FLOW_INSTANCE, ITEM_ID, ITEM_VALUE )
VALUES ( :B4 , :B3 , :B2 ||':'||:B1 ||':0:0')

The other two sessions shows this SQL:
UPDATE WWV_FLOW_DATA SET ITEM_VALUE = NVL(NVL(:B6 ,:B5 ),1)||':'
|| NVL(NVL(:B4 ,:B3 ),15)||':0:0' WHERE ITEM_ID = :B2 AND FLOW_I
NSTANCE = :B1

Fair to assume that all 3 are hitting the same row? (same flow instance and item?)

I've done some searching and it seems that a lot of dead locks have been reported on similar APEX tables. Is this potentially the same underlaying issue, but with just slightly different symptoms?


Some speculation... tabbed browsing is very common with Firefox. I often open multiple tabs on the same APEX app for different pages. What will the back-end impact be if this is done on the same page? Could it be that APEX expects a single-HTTP-request-at-a-time-from-a-web-session?

What does not make sense though is why the update will show row lock contention, assuming all 3 hit the same row. The INSERT should when inserting a row with the same PK as that of an uncommitted inserted row of another session. But why would the UPDATEs in this scenario show row lock enqueues? The INSERT row enqueue points that the row has not yet been committed. If so, the 3rd session's UPDATE should return "no row found" and not be stuck on a row lock enqueue.. right?

This does not make much sense to me.

Any comments, suggestions, and ideas to troubleshoot and resolve this will be much appreciated.