Hi Tom,

Being a DBA at a hosting company hosting hundreds of Oracle databases, I frequently come across so called database performance issues which seem to be rooted in the way developers are coding their applications.

Currently I am involved in yet another such so called database performance tuning effort, but I am seeing a lot of issues that I think should be addressed in the application. The main issue is the amount of parsing going on, but thanks to your book "Effective Oracle by Design" I know what to tell the developers on that.

I have come across another seemingly strange construction of code in the database, that I would like to ask your advice on.

The database is constantly flooded by "enq: TX - row lock contention", caused by 2 specific sessions always being connected:

SID SERIAL# PROGRAM USERNAME MACHINE EVENT P1 P2 P3 WAIT_SEC REMAIN_SEC SINCE_LAST_SEC STATE
------- ------- ---------------- -------- ----------- ------------------------------ ---------- ---------- ---------- ---------- ---------- -------------- -------
1156 27847 JDBC Thin Client READING hubpreapp05 enq: TX - row lock contention 1415053318 11206666 27952 6176 0 0 WAITING
1518 733 JDBC Thin Client CHANGE hubpreapp05 enq: TX - row lock contention 1415053318 4325396 1635615 5980 0 0 WAITING


These 2 sessions are constantly fighting each other like this:

MACHINE OSUSER PROGRAM USERNAME SID SERIAL# SPID SQL_ADDRESS SQL_HASH_VALUE SQL_ID PIECE SQL_TEXT
----------- ------------ ---------------- -------- ----- ------- ------ ---------------- -------------- ------------- ----- --------------------------------------
hubpreapp05 HUBPREAPP05$ JDBC Thin Client READING 1156 27847 8836 000000020EF0D2D8 2639741861 4f539s2fpfhx5 0 SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE
hubpreapp05 HUBPREAPP05$ JDBC Thin Client CHANGE 1518 733 11224 000000020EF0D2D8 2639741861 4f539s2fpfhx5 0 SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE


Upon detecting this issue I did alert the developers, but the answer they gave somewhat surprised med.

It turns out that this is deliberate design decision.

The aim is was to create a "mutex" like mechanism, so that only one session would ever have "control of the data" at anyone given time - using SELECT FOR UPDATE to implement this.

I don't have much experience in coding "mutex" mechanisms, but it would seem to me that this solution is very-quick and very-dirty.

I would expect something involving "autonomous transaction", which would not create this sort of "active" waiting on the part of the "inactive" session. It seems to me that the above is really misusing a Oracle database feature in a way that it was not intended to be used.

I am aware that I am not giving you a lot of hard facts, but non the less I would very much your answer on the general principles of application design regarding the above situation.

QUESTIONS:
1) What are the performance penalties using this kind of "mutex" coding approach ?
2) What would be a proper solution ?