Huge wait on Enqueues when selecting for update ...

From: Sab Yani <sabyani_at_hotmail.com>
Date: 5 May 2003 18:01:50 -0700
Message-ID: <ce750dee.0305051701.41fccca3@posting.google.com>

I have a table which has several hundreds of rows. Hundreds of users are trying to update several rows ( around 10 -15) each time using select for update . Different users might try to update the same set of rows, causing huge wait on enques and very low throughput.

The TX enqueue with type 6 is the cause for the problem. I've optimized the block size and PCTFREE/PCTUSED/INITRANS to increase the concurrency at the block level.

I've also tuned all the SQLs to the best I can. I removed all table scans. The developer of the code insists that his code work 3 time better on other relational databases and therefore, he is now willing change his code for the sake of Oracle.

Following is a clip from the statspack out put :

                                                        Avg Wt        
Wait
Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)    

Time (s)
-- ------------ ------------ ----------- ----------- -------------

---

TX 56,280 55,997 0 21,819 21,413.10  467,212
TM 750,529 750,441 0 3,398 4,254.55   14,457

US        3,291        3,291           0          19          7.53    
       0
HW        1,865        1,865           0           3          2.00    
       0
CU        3,107        3,107           0           1          4.00    
       0
          -------------------------------------------------------------

Your help is appreciated ... Received on Mon May 05 2003 - 20:01:50 CDT