Question on tunning while INSERT SELECT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Question on tunning while INSERT SELECT

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question on tunning while INSERT SELECT

    Hi,

    As part of my new requirement, I need to load 360 million records using INSERT SELECT with append mode as single transaction and COMMIT once completed.

    I have been asked not to use partition swap, because data will be lost if the partition swap fails. And not allowed to include NOLOGGING as the database would be in ARCHIEVE mode.

    I need your expert brains. I need your help to speed up the performance of the INSERT SELECT.

    Technology: PLSQL
    Oracle Version: 11G R2
    Server: M5000 + 400 GB Flash

    Note: As of now, we do not have a 11g environment.

    Many thanks in advance.

    Regards,
    Cheers!
    OraKid.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quick question... do you have enough space on UNDOTBSx to handle a 360 million rows transaction?

    Assuming you do, and not starting a discussion on imposed limitations, is this load targetting different partitions in affected table? if this is the case you may try to convince your boss to let you run several load processes in parallel, one for each affected partition. Latelly we have done this to reload a 1.7 billion rows table with amazing results.

    Last but not least... are you allowed to drop indexes on target table?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks Paul

    We have been told there will be loads of UNDO space.

    We do not have any indexes.

    Entire 360 million belongs to one processing day. This will go to 1 single partition on the target table. That is why I thought swap partition is the best option.

    Is there anything we can do like adding new HINTS in 11g, Server configuration, etc.

    Thanks and Regards
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are absolutely correct - partition swap will do it instantly.

    By the way, since parition swap is a metadata operation I really cannot see how it may fail. As a safe net and to make your boss feel more secure you may want to propose to take an expdp of the 360M rows table previous to performing the operation.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks Paul

    Thanks and Regards
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width