Table A is having initrans 120 (and its associated indexes), pctfree 20. While performing concurrent updates on this table from our application, we are consistently seeing that after 1000+- updates, the updates will get slower by 3000%, and not too long after that, the time taken for each updates will be back to normal.
The update is on the PK column(index unique scan).
The table size is 3000 records.
Average row length is 1000+-.
I don't think i can do anything to improve the sql. Considering setting the freelists for the table, but would it help in this case as it is update, not insert? What is the optimal value for initrans for 120 concurrent updates on this table? Does it have anything to do with the archiver archiving redo log (we don't multiplex redo log)? We are not doing batch commit, as this is an OLTP app. Log buffer is sized at 1M. Would parallelism on this table helps (take note that currently we are talking about 3000 records)? How do i monitor the contention on a particular table, as statspack only give me overall database performance stat?
Please ask me if you still need any info.
We are running Oracle 220.127.116.11 on UNIX.
(Since i am asking so many questions, please treat this as a sharing session rather than points earning attempt, appreciate and thanks for your effort in advance)