Re: Upgrade 9.2 to 11.2 with minimal downtime
From: Chuck <chuckh1958_nospam_at_gmail.com>
Date: Fri, 03 Jun 2011 11:15:44 -0400
Message-ID: <isatr0$p2p$2_at_dont-email.me>
On 6/2/2011 2:34 PM, joel garry wrote:
>
> When I was on older hardware that was the kind of performance I'd
> see. Are you using a large imp buffer (such as buffer=20000000)?
> statistics=recalculate? Sometimes it makes sense to do the import
> noarchivelog, switch to archivelog and make your first "real" backup,
> since most of your disk writing is probably redo/archiving and you can
> backup online. Statistics may need to be redone anyways, many of us
> have seen that oracle gets it mostly right by default, except for the
> histogram default, and there always seems to be a few sql that just
> work different across versions. Depending on how much time and effort
> you are putting into user acceptance, you may want to correct the
> statistics as a separate project and import and lock those.
>
> Sounds like most of your gain was the commit after every row that
> commit=y does with lobs. Using a large buffer would help on the
> tables without lobs.
>
> Another thought is the initial setting of each table. I've found it
> helps to precreate tables with proper sizing before imp (at least the
> biggest/most volatile ones if you have thousands), to avoid over or
> under allocating space. You can overallocate with compress=y, and
> spend a lot of time dealing with extent handling with a small initial,
> though less with LMT than DMT. lobs may have their own
> idiosyncracies.
Date: Fri, 03 Jun 2011 11:15:44 -0400
Message-ID: <isatr0$p2p$2_at_dont-email.me>
On 6/2/2011 2:34 PM, joel garry wrote:
>
> When I was on older hardware that was the kind of performance I'd
> see. Are you using a large imp buffer (such as buffer=20000000)?
> statistics=recalculate? Sometimes it makes sense to do the import
> noarchivelog, switch to archivelog and make your first "real" backup,
> since most of your disk writing is probably redo/archiving and you can
> backup online. Statistics may need to be redone anyways, many of us
> have seen that oracle gets it mostly right by default, except for the
> histogram default, and there always seems to be a few sql that just
> work different across versions. Depending on how much time and effort
> you are putting into user acceptance, you may want to correct the
> statistics as a separate project and import and lock those.
>
> Sounds like most of your gain was the commit after every row that
> commit=y does with lobs. Using a large buffer would help on the
> tables without lobs.
>
> Another thought is the initial setting of each table. I've found it
> helps to precreate tables with proper sizing before imp (at least the
> biggest/most volatile ones if you have thousands), to avoid over or
> under allocating space. You can overallocate with compress=y, and
> spend a lot of time dealing with extent handling with a small initial,
> though less with LMT than DMT. lobs may have their own
> idiosyncracies.
I'm using all LMT's on the destination database. I haven't used DMT's since LMTs became available in v8 I think. I was also using buffer=4m which is perhaps too small. I'll try 20m instead. This was my import line...
imp / fromuser=x touser=x recordlength=65535 buffer=4000000 log=import_adbase.log Received on Fri Jun 03 2011 - 10:15:44 CDT