Re: slow import

Murat Balkas wrote:

> 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

Your table definition is a 'poster' for how not to define a table. Why is NEXT different in size from INITIAL? Why on earth PCTINCREASE of 50%? Gadzooks. And why oh why PCTUSED 40%? Ye Gads! I can only wonder what else is defined like this. I half expect the default tablespace is SYSTEM too. Please tell me I'm wrong.

Please discuss this with your DBA. There are so many problems here I don't know where to start.

If you don't have a DBA then respond with the following information:

1. Size in bytes of the average row on insert.
2. Size in bytes of the average row after updates.
3. A copy of the initSID.ora. (also include the above information)

Daniel Morgan Received on Fri Aug 30 2002 - 13:42:22 CDT