-
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.
-
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.
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|