Re: slow import

"Murat Balkas" <murat.balkas_at_o2.com.tr> wrote in message news:b1148377.0208300546.5598b815_at_posting.google.com...
> Hi,
>
> Oracle 8.1.5 on E-3500 with 2xCPU and 2048 MB RAM.

Recommendation 1. Move to a supported release.

>
> I'm trying to import a table with 22 million rows. The original
> table, rows being inmported to, has indexes. Exported table, table on
> dump, doesn't have indexes. My table has 10 columns and 4 indexes.
> But, importing is very slow.
>
> I set sort_area_size to 1000000 and import's buffer to 1000000. I've
> enough redo log groups and enough rollback segments. indexes are
> created with initial 1 M and next 100 k.

Recommendation 2. Substantially increase import buffer (say 100M)

>
> My table's script is as following :
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 52428800
> NEXT 117964800
> PCTINCREASE 50
> MINEXTENTS 1
> MAXEXTENTS 121
> FREELISTS 1 FREELIST GROUPS 1 )
Recommendation 3. Don't use a create table scripts from the 80's, move into 00's. Use LMT (which will thankfully make these awful storage clause options redundant) or at least make initial and next the same and pctincrease 0, or better still, have no storage clause and use a well set tablespace default of initial=next with pctincrease = 0

>
> Every 3-4 minutes Oracle stops responding and waits for 40-45
> seconds. During these time, I see 'write complete waits' or 'free
> buffer waits'.

Recommendation 4. Sounds like your buffer cache is too small (therefore increase), your redo logs are be too small (if redo switches is high, resize larger) and your db writer(s) are not coping but may improve if you do the above.

Good Luck

Richard
>
> What can I do to speed it up without restarting the database?
>
> Any help would be greatly appreciated.
>
> Murat BALKAS
Received on Sat Aug 31 2002 - 02:55:43 CDT