929643 wrote:the top/leading block of PK is always HOT since every session is trying to write to it.
Hello Forum, I have been trying to figure out the root cause of a transient spike in reponse time of an insert sql in our Oracle database 11g
SQL statement is a plain insert statement
insert into TJ(x,y,z) values (?,?,?);
x is the primary key generated from a db sequence( select seq.nextval from dual)
why would a simple insert sql without any where conditions require to access index structureAn INSERT has to add entries to the index. Every fresh INSERT call has to start from the root block to identify the leaf block where the new key:rowid values are to be added.
the sudden spike in log file synccommit waits. The session is waiting for LGWR to confirm that the commit has been processed (written to online redo log)
along with enq-US contention (undo contention)a separate wait. An Undo segment can be shared by multiple sessions, each session working on the same or different tables for different transactions. Contention can occur if there are few Undo segments. Automatic Undo Management does add new segments but in a very volatile environment (many new transactions "suddenly appear"), AUM may not respond quickly enough to add new segments so transactions may still contend for the same Undo segments.