Yes, in talking to the web logic developers that is pretty much how the app
works. That is why I stated you see blocking and waiting thru the views.
The upate "lags" so the connection pool throws another session out there to
update the record. After a while there are 100 - 200 sessions and you know
what happens.
I hit send too fast...
What happens is the catalog table record is being constantly updated with latitude and longitude values ( i.e. the Spatial index ). As the update is occurring to the catalog table, the connection pool starts creating 100 to 200 users and trying to update the same record in the catalog table. This is the way the app works because the app is constantly trying to track the position. This record in the catalog table tracks lat/long position. So,
So by design all your sessions wait for one point of serialization? Is this neccesary?
So whenever you commit, then a next waiting session will immediately update the same record to a new value?
If the definite serialization isn't necessary, you might want to keep track of current position by inserting values with some ascending ID into some tracking table and query the current position with "where ID = select max(ID) from table". This would shift your contention from TX enqueus to "rightmost" blocks in your index on ID. So you'll get buffer busy waits instead of TX enqueue waits, but the first ones should be much shorter.
However, you're not going to be completely serialized and your queries might see old positions even when a transaction has committed a new one already. And you have to have a mechanism for getting rid of old data efficiently (dropping/truncating old partitions).
Otherwise you could use 10046 trace to find out how long your transactions are (XCTEND in the tracefile means transaction end and normally the EXEC phase of your DML allocates TM and TX enqueues). You can combine this tracing with event 10704 level 4 to track enqueue gets and releases too.
Tanel.
-- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
