Oracle Database Discussions

Hello everyone,

Working on 10g/windows

Top 5 events

EVENT TOTAL_WAITS TIME_WAITED AVG_MS PERCENT
----------------------------- ------------------------ ------------

CPU 9462339 48

enq: TX - row lock contention 12531 3660728 2921.34 18

control file parallel write 1300731 3088079 23.74 16

log file parallel write 1510503 1264080 8.37 6

log file sync 1072553 968007 9.03 5


Distribution of row lock wait during the last 4 days in the database server

END_INTERVAL_TIME TOTAL_WAITS TIME_WAITED_MICRO AVG_WAIT_MS
------------------- ----------- ----------------- ----------- -----
2008-04-01 16:00:58 909 2721008230 2993.41

2008-04-01 15:00:27 50 149941140 2998.82
2008-03-31 12:00:42 193 575595397 2982.36

2008-03-29 23:00:13 172 513058700 2982.9

2008-03-29 22:00:37 164 483940046 2950.85

2008-03-27 22:00:35 565 1667120838 2950.66

2008-03-26 18:00:59 348 1042918982 2996.89

My analysis:
It's obvious that the row lock contention wait time is huge, and this direct me to find out SQL stmt, causing this.
all the SQL statement was SELECT ....... FOR UPDATE stmt.
I was also able to find out locked tables.

My tuning idea:
1. I'm thinking to reorganize hot tables as well as their indexes, but by instinct it seems to not give so much value to avoid the huge row lock wait time.
2. I'm also seeing if I can reduce the number of rows per block, by increasing PCTFREE and diminishing PCTUSED, so the contention will spread over many blocks instead of one heavy block.

Question
As SQL stmt related to those locked tables are select ... for update, how could I tune this kind of stmt?

Does someone have other idea to come up with this row lock contention?

Tanks for your effort and help