Re: Faster imports

From: <mnma_at_my-deja.com>
Date: Wed, 19 Jan 2000 18:36:09 GMT
Message-ID: <86506o$h3q$1@nnrp1.deja.com>

I have been working on import times for about the past month. You can not use direct load with import, only sqlloader. i don't know what your sga is but this is what i do.
set db_block_buffers to a big size ie 5000 have log_buffer a multiple of your block size up to 1 meg, after that there is really no difference.
set your buffer in your import parameter file to about 10 million have commit=n if you have a rollback segment at least as big as your largest table
have commit=y but like a previous post said have lots of big redo logs drop all of your tables with cascade constraints before you start use the indexfile option to create your indexes after the import have noarchiving.
you will have to bounce your database but when you do, check out this script to monitor the speed of your import to specific tables

REM This script monitors how fast rows are imported REM in an import job, for the current table

select substr(sql_text,14,instr(sql_text,'(')-16) table_name,

         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd
hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-

mm-dd hh24:mi:ss'))*24*60)) rows_per_min, buffer_gets, disk_reads   from v$sqlarea where sql_text like 'INSERT INTO "%'     and command_type = 2 and open_versions > 0 /

hth

mnma

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 19 2000 - 12:36:09 CST