Re: copying large data on production environment
Date: Tue, 28 Sep 2010 08:59:03 -0700 (PDT)
Message-ID: <fe26e74e-6d1d-4e9a-b8b1-fb6082d07a1d_at_l32g2000prn.googlegroups.com>
On Sep 28, 6:55 am, "mick.w" <mic..._at_poczta.fm> wrote:
> I need to copy large data from one table to another (insert into..
> values (select * from ...)) on production environment (system must be
> running). I can do this in few parts, copying data partition by
> partition. Are there any hint for this problem to avoid slowdown of
> system?
Well, something has got to give. First of all, which exact versions of everything are you using? What kind of volumes of data are we talking about? Are there times when the system is less heavily loaded? How long do you have to do it? Are you using any tools like Informatica, Toad, or just sql, or pl/sql? Are you on the server or is a client machine involved? Does the data need any transform? Are constraints the same? What kind of storage are you using?
In general, if you can do it in sql that will be faster than pl/sql or other tools, although there are some situations where pl could be faster, and some situations where you might want start/stop capability. It is also possible that you could lower the priority of the os process doing the transfer, if you have flexible time constraints for completion.
Some obvious things to check in testing is how big redo files are, it may make sense to make them bigger for this time. Same for undo, though some people argue to let undo grow as much as needed (my opinion is this is an exception to that, unless you are doing this periodically).
Also, don't use select *, be explicit about the columns. That's just good programming practice, and has a slight performance effect (although normally too small to notice).
A more risky move would be to temporarily use noarchivelog mode, but users would have to sign off on the risk of losing production data during the load time. Not really a strategy for a system that needs to keep running, but may be worth it if a small time window overrides other considerations.
jg
-- _at_home.com is bogus. http://www.ocregister.com/articles/lucky-250808-horse-bradford.htmlReceived on Tue Sep 28 2010 - 10:59:03 CDT