1 2 Previous Next 17 Replies Latest reply: Oct 11, 2013 2:40 PM by Jonathan Lewis RSS

    poor insert performance

    1046920

      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

          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
            1046920

            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

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

              • 4. Re: poor insert performance
                sb92075

                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
                  1046920

                  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

                    does every FK have an INDEX?

                    • 7. Re: poor insert performance
                      1046920

                      yes.

                      • 8. Re: poor insert performance
                        sb92075

                        >It has 7 parent tables

                        Has all table data been normalized to Third Normal Form?

                        • 9. Re: poor insert performance
                          1046920

                          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

                            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
                              1046920

                              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

                                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

                                  >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
                                    1046920

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

                                    1 2 Previous Next