Re: slow import

I would in fact disagree with most of your statements (except *maybe* creating indexes separately).

For faster import I would try this:
Create buffer = 819200000 # ~80M or more

           commit = n # if the rbs allows it
           constraints=n # if you are ok with the
                                   headache of creating them later
           indexes = n
.. If creating indexes
           sort_area_size = big # ~ 40M or more

as Kenneth pointed out .. I have a feeling that the Buffer cache is small also. Maybe redo logs might be small too (look for too frequent log switches .. which are not desired).
The server tuning should be done after looking at the facts ... starting with v$system_event / v$session_wait

2c

Anurag

"Yang" <yg.yang_at_wanadoo.fr> wrote in message news:akolt4$q06$1_at_wanadoo.fr...
> - do not import with index (create them separately);
> - create your table with only one extent (resize it to the max size : 52Mo
> is too small);
> - import with option commit=y;
> - when you create index later, do not use initial=1M, give the max size of
> your index in order to have only one extent.
>
> hth
> yang
>
>
> "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.
> >
> > 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.
> >
> > 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 )
> >
> > 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'.
> >
> > What can I do to speed it up without restarting the database?
> >
> > Any help would be greatly appreciated.
> >
> > Murat BALKAS
>
>
Received on Fri Aug 30 2002 - 22:31:57 CDT