1 2 Previous Next 17 Replies Latest reply: Oct 11, 2013 2:40 PM by Jonathan Lewis Go to original post RSS
      • 15. Re: poor insert performance
        jgarry

        Append avoids table undo, but not index undo.  Some things may change a direct path load to a conventional load, like foreign key referential integrity.  Ask Tom Inserts with APPEND Hint.

         

        Some things may vary by version.

        • 16. Re: poor insert performance
          Alvaro

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

           

          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 am a bit confused. First you say that only one FK constraint is to blame, and then you go on to say that you have 7 parent tables, therefore it's 7 FKs!

           

          If that is indeed the case, I don't see a particular issue here. For each insert oracle has to validate against parent rows (read PK indexes) seven times, and it's only slowing you down 2x. Sounds about right to me.

           

          Also, just a side note.

           

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

           

          You can enable NOVALIDATE the constraint in case you know the data inserted is sound and won't cause any trouble, so it will not validate the newly inserted rows.

          • 17. Re: poor insert performance
            Jonathan Lewis

            If disabling just the one constraint on the large primary key doubles the rate of insert then it's possible that your time is being spent doing lookups on the primary key index on that table. (This does seem a little unlikely - but it's possible if the table is extremely large, check the trace file to identify the problem object, or use a couple of AWR snapshots around the insert to check "segments by physical reads".)

             

            If it's the primary key index, you could try sorting the data to be inserted by that foreign key to reduce the randomness of the access to the index - it MIGHT help, but it could have side effects in terms of the cost of updating indexes on the table itself, and on the clustering_factor of those indexes which could result in execution plans changing (over time) for some current queries.

             

            Regards

            Jonathan Lewis


            1 2 Previous Next