TX Enqueue | what to do?

My understanding of TX Enqueue is that it is a transaction level wait state. Meaning, that within an 8k block, there may be several rows of data. When one of those rows within the block is marked as locked for an oracle transaction, other rows are subsequently blocked from access. This is the condition of a TX Enqueue wait state. Additional understanding leads me to believe that if I have this symptom occur frequently, I would need to adjust my block size to something smaller to avoid transaction contention. Since Oracle 9i supports multiple block sizes at a table level, this is not a big deal to do a rebuild targeting the tables in question.

Here is the quesitons:

1. Would Oracle partitioning help me in this case? It would seem to me less likely if its adjacent data within the same block.
2. Is redesigning the application to use optomistic locking my only other choice? There is a lot of code time involved in doing this and also doesn't guarantee the integrity as well as pessimistic locking.
3. How do I target which blocks are the "offenders" in this condition?

I see TX Enqueues as my top wait event within a set of statspack reports during a LoadRunner test. If anyone has any further clarification on this point or if any of my understanding is inaccurate, please do tell. :)

ken k Received on Wed Apr 23 2003 - 06:10:23 CDT