This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 11, 2013 12:40 PM by Jonathan Lewis RSS

poor insert performance

d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
Currently Being Moderated

I am currently facing an issue where an insert into a table is taking ~2hrs to insert 1 M rows. It appears that its due to a foriegn key constraint. is it normal that a ref constraint can slow down things 2X times? because its 2x times faster when i disable that constraint. what could be wrong with it?

  • 1. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    d485f28b-aa48-4f30-843f-8dd6f2646b6a wrote:

     

    I am currently facing an issue where an insert into a table is taking ~2hrs to insert 1 M rows. It appears that its due to a foriegn key constraint. is it normal that a ref constraint can slow down things 2X times? because its 2x times faster when i disable that constraint. what could be wrong with it?

     

    I suspect that the problem is NOT on the INSERT operation; but on the SELECT that most likely "feeds" the rows to the INSERT

     

    post the EXPLAIN PLAN

  • 2. Re: poor insert performance
    d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
    Currently Being Moderated

    I ran the select independently and it was very fast. I also ran a different test to insert into t select * from temp_table. I ran it with and w/o the constraint with flush after the first test. with constraint its taking over 90 mins and w/o constraint its taking 40 to 45 mins.

  • 3. Re: poor insert performance
    jgarry Guru
    Currently Being Moderated

    Yes, this is why you can defer constraints.  But watch out for bug 13798806.

  • 4. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    d485f28b-aa48-4f30-843f-8dd6f2646b6a wrote:

     

    I ran the select independently and it was very fast. I also ran a different test to insert into t select * from temp_table. I ran it with and w/o the constraint with flush after the first test. with constraint its taking over 90 mins and w/o constraint its taking 40 to 45 mins.

     

    My laptop can INSERT at least 1,000,000 rows per MINUTE into a table.

    Is there a TRIGGER on this table?

     

    SQL_TRACE=TRUE can reveal where actual time is being spent.

  • 5. Re: poor insert performance
    d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
    Currently Being Moderated

    HI Jgarry, thanks for your response.

     

    how does deferring constraints help ? it needs to verify at the end anyway , right? what saves us here?

     

     

    Hi sb92075,

     

    Tables doesn't have triggers.

     

    It has 7 parent tables , most of them are pretty small. only one of the parent tables are huge.

     

    when i monitored the session , it mostly waits on db file seq read. I will run the trace and post output.

  • 6. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    does every FK have an INDEX?

  • 8. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    >It has 7 parent tables

    Has all table data been normalized to Third Normal Form?

  • 9. Re: poor insert performance
    d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
    Currently Being Moderated

    yes.

     

    here is the content from trace o/p

     

    Event waited on                             Times   Max. Wait  Total Waited

      ----------------------------------------   Waited  ----------  ------------

      enq: FB - contention                         3951        0.00          1.63

      Disk file operations I/O                      143        0.00          0.00

      gc current grant 2-way                      39110        0.01         10.94

    db file sequential read                    766473        0.56       3834.87

      gc current grant busy                        1917        0.00          0.81

      gc current block 2-way                        782        0.00          0.34

      KJC: Wait for msg sends to complete           185        0.00          0.00

      gc current multi block request               1835        0.00          1.22

      gc current grant congested                    233        0.00          0.06

      enq: SK - contention                            1        0.00          0.00

      gc cr multi block request                     290        0.00          0.26

      row cache lock                                 55        0.00          0.01

      db file scattered read                        259        0.02          1.97

      gc current block congested                      4        0.00          0.00

      latch: gc element                               1        0.00          0.00

      log file switch completion                      2        0.03          0.05

      gc buffer busy release                          1        0.00          0.00

      enq: TT - contention                            1        0.00          0.00

      log file sync                                   1        0.00          0.00

      SQL*Net message to client                       1        0.00          0.00

      SQL*Net message from client                     1       85.93         85.93

    ********************************************************************************

  • 10. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    db file sequential read is due to INDEXED reads by the way


    against which INDEX does a majority of this time occur?


    Has child table or any of the parent tables had rows deleted from it "recently"?

  • 11. Re: poor insert performance
    d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
    Currently Being Moderated

    Hi sb,

     

    against which INDEX does a majority of this time occur? --> how to find this?

     

    no, nothing was deleted recently.

  • 12. Re: poor insert performance
    jgarry Guru
    Currently Being Moderated

    There may be some resource competition to read the source, add a row, add an index entry versus reading source, adding row all at once, then reading the new rows and adding index entries.  It can be more obvious when you can append above the high watermark.  Sometimes there is no difference.  If it's something big and repetitive, it's worth finding out.  You can trace to find out what is really happening.  ORACLE-BASE - SQL trace, 10046, trcsess and tkprof in Oracle

  • 13. Re: poor insert performance
    sb92075 Guru
    Currently Being Moderated

    >against which INDEX does a majority of this time occur? --> how to find this?

    It would be good know if this time is being spent doing INDEX maintenance on the new rows or if time is being spent doing constraint checks  against parent rows.

    You need  to read the raw content of the trace file.

    You can GOOGLE to learn how to interpret the content of the trace file.

     

    Does performance behavior change by doing INSERT /*+ APPEND */ ?

  • 14. Re: poor insert performance
    d485f28b-aa48-4f30-843f-8dd6f2646b6a Newbie
    Currently Being Moderated

    I think it behaves similar with append. In fact Prod version is using append. 

1 2 Previous Next

Legend

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