Re: slow import

If you really can't/won't' restart your database :

a)If it is possible regarding data integrity, try disabling the indexes and rebuild them after the import.

b) Increase the sort_area_size to, say 100 Mb.

If that doesn't help, and you are willing to restart the database, I would look at some SGA parameters, especially I would try to increase parameter db_block_buffers, it may be undersized.

• Kenneth Koenraadt

On 30 Aug 2002 06:46:25 -0700, murat.balkas_at_o2.com.tr (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
Received on Fri Aug 30 2002 - 13:43:35 CDT