slow import

From: Murat Balkas <murat.balkas_at_o2.com.tr>
Date: 30 Aug 2002 06:46:25 -0700
Message-ID: <b1148377.0208300546.5598b815@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 - 08:46:25 CDT