This discussion is archived
10 Replies Latest reply: Apr 26, 2012 12:44 AM by 932646 RSS

Insert SQL performance issue

932646 Newbie
Currently Being Moderated
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)
table has about 34million records
this insert sql is run concurrently by about 100 users

our db settings are undo =2G, sga_target=3G, pga=1G
DB Server hardware: 4 processor 2.5GHz 8GB Ram
DB CPU utilization is very low (about 5%)

From ASH logs, i observed the following wait events during the transient spike which lasts for about 15seconds

P1
54 is a index segment i01_TJ on TJ(y)
64 is table TJ
1431502854 is UNDO segment

SAMPLE_TIME     SQL_ID     SESSION_ID     EVENT     P1     P2     WAIT_TIME     SESSION_STATE
2012-04-23 04:06:59     6wfx4jsnctu8q     11     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     19     db file sequential read     64     936143     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     23     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     24     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     31     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     37     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     38     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     39     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     47     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     66     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     688     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     694     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     697     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     700     db file sequential read     64     936144     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     701     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     723     db file sequential read     1     541     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     735     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     739     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     741     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1369     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1374     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1378     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1386     db file sequential read     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1393     read by other session     64     936902     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     1401     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     2076     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     2082     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:59     6wfx4jsnctu8q     2087     db file sequential read     53     254978     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     11     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     19     db file sequential read     64     936143     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     23     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     24     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     31     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     37     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     38     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     39     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     47     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     66     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     688     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     694     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     697     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     700     db file sequential read     64     936144     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     701     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     723     db file sequential read     1     541     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     735     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     739     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     741     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1369     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1374     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1378     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1386     db file sequential read     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1393     read by other session     64     936902     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     1401     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     2076     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     2082     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:58     6wfx4jsnctu8q     2087     db file sequential read     53     254978     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     11     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     19     db file sequential read     64     936143     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     23     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     24     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     31     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     37     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     38     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     39     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     47     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     66     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     688     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     694     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     697     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     700     db file sequential read     64     936144     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     701     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     723     db file sequential read     1     541     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     735     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     739     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     741     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1369     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1374     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1378     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1386     db file sequential read     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1393     read by other session     64     936902     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     1401     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     2076     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     2082     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:57     6wfx4jsnctu8q     2087     db file sequential read     53     254978     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     11     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     19     db file sequential read     64     936143     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     23     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     24     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     31     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     37     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     38     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     39     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     47     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     66     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     688     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     694     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     697     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     700     db file sequential read     64     936144     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     701     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     723     db file sequential read     1     541     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     735     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     739     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     741     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1369     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1374     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1378     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1386     db file sequential read     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1393     read by other session     64     936902     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     1401     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     2076     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     2082     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:56     6wfx4jsnctu8q     2087     db file sequential read     53     254978     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     11     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     19     db file sequential read     64     936143     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     23     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     24     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     31     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     37     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     38     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     39     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     47     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     66     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     688     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     694     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     697     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     700     db file sequential read     64     936144     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     701     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     723     db file sequential read     1     541     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     735     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     739     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     741     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1369     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1374     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1378     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1386     db file sequential read     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1393     read by other session     64     936902     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     1401     enq: US - contention     1431502854     1     0     WAITING
2012-04-23 04:06:55     6wfx4jsnctu8q     2076     enq: US - contention     1431502854     1     0     WAITING

My Question is
1. why do i see db file sequential read for an insert sql without any where predicates?
2. how to tune the undo contention

Appreciate your support!

Thanks in Advance!
  • 1. Re: Insert SQL performance issue
    sb92075 Guru
    Currently Being Moderated
    929643 wrote:
    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)
    the top/leading block of PK is always HOT since every session is trying to write to it.
  • 2. Re: Insert SQL performance issue
    932646 Newbie
    Currently Being Moderated
    Thanks for reply.

    But, this issue does not appear always. It's transient and lasts only for a few seconds(15 seconds). during this time most of the insert/update sql statements slowdown and users observe spike in application response time.

    if you notice there are db file sequential read wait events on the TJ table index i01_TJ which is on TJ(y) not the primary key. why would it require to scan this index ?

    And during the good time-frame when there are no issues, we don't see any waits from enq-US contention, db file sequential read and also log file sync occurs 4 times in 60seconds. but during spike log file size waits increase drastically; there are also read by other session waits and db file sequential reads;enq-US contention

    Additional info
    log_buffer=32MB
    redo log =3 log groups each having 500MB
  • 3. Re: Insert SQL performance issue
    hitgon Expert
    Currently Being Moderated
    Hi

    can you elaborate the detail behavior of your application?

    You can try the following thing?

    1.) may be you need to used hash table partition and global index partition for better data balance
    across the even number of table partition.

    2.) you can increase the size of cache inside the sequence

    3.)drop the primary key and create the unique index of same column

    4.) you need to increase the init_tran of table and index

    Regards
    Hitgon
  • 4. Re: Insert SQL performance issue
    932646 Newbie
    Currently Being Moderated
    Thanks for response!
    ok, the application module in question is storing transaction journal records using simple inserts and as i mentioned we have about 100 concurrent users performing transactions, so each transaction would insert a record into TJ table

    1.) may be you need to used hash table partition and global index partition for better data balance
    across the even number of table partition.
    Partition is not an option for us at this time

    2.) you can increase the size of cache inside the sequence
    DB sequence cache size is 100

    3.)drop the primary key and create the unique index of same column
    how would this help?

    4.) you need to increase the init_tran of table and index
    we are using ASSM

    what i am trying to find is.. why would a simple insert sql without any where conditions require to access index structure ? also the sudden spike in log file sync events along with enq-US contention (undo contention)
  • 5. Re: Insert SQL performance issue
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    the top/leading block of PK is always HOT since every session is trying to write to it
    Sessions would be reading the root block but writes would be rare (writes would occur only in the case of branch block splits).


    Hemant K Chitale
  • 6. Re: Insert SQL performance issue
    hitgon Expert
    Currently Being Moderated
    Hi,

    can you please drop the primary key and then perform the same steps and share the result the same.

    why would a simple insert sql without any where conditions require to access index structure ?
    when we insert the records in table then same time oracle internally maintain same records inside the internal index table so that without any where conditions require to access index structure.

    Regards
    Hitgon
  • 7. Re: Insert SQL performance issue
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    why would a simple insert sql without any where conditions require to access index structure
    An 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 sync
    commit 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.


    Hemant K Chitale
  • 8. Re: Insert SQL performance issue
    932646 Newbie
    Currently Being Moderated
    Thanks for response!

    An 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.
    [Raghav]this is done by oracle internal process. does this cause db file sequential read?
    we have multiple indexes on the TJ table. why does it hit only one particular index?

    commit waits. The session is waiting for LGWR to confirm that the commit has been processed (written to online redo log)
    [Raghav] true, but i trying to understand what could cause a sudden spike in these events

    I can try the change suggested in the previous reply -to drop primary key and create a unique key on the same(with not null constraint off-course)
    but, I need to know how would this help? what is the reasoning behind this change
  • 9. Re: Insert SQL performance issue
    932646 Newbie
    Currently Being Moderated
    Okay - The issue is resolved.

    First we increased SGA_TARGET, this showed up as different issue
    Top 5 wait event
    Event     Waits     Time(s)     Avg wait (ms)     % DB time     Wait Class
    enq: TX - index contention     152     1,445     9508     53.93     Concurrency
    log file sync     48,821     702     14     26.20     Commit
    read by other session     9     255     28309     9.51     User I/O
    db file sequential read     925     164     178     6.13     User I/O
    DB CPU          112          4.18     

    Next we identified from ASH the session causing enq: TX -index contention as insert into TJ values(?,?,?)
    we defragmented the TJ table
    alter table TJ enable row movement;
    alter table TJ shrink space cascade;

    this helped to resolve the issue.
  • 10. Re: Insert SQL performance issue
    932646 Newbie
    Currently Being Moderated
    Step#1 Increase SGA_TARGET

    Step#2
    alter table TJ enable row movement;
    alter table TJ shrink space cascade;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points