10 Replies Latest reply: Apr 26, 2012 2:44 AM by 932646 RSS

    Insert SQL performance issue

    932646
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Step#1 Increase SGA_TARGET

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