Import running slow!

Hi all,

Db: 8.1.7.2
db Size: 20Gb Data / 12gb Indexes
OLTP System
Sun Solaris OS

We are executing an export/import on our DB as we have tables with many extents etc...

We did an export:
>exp userid=$usr_id file=$file_name statistics=none compress=y >> $log_file
This is fine and it took 14 hrs to export.

To test the time it takes to import we did a test import on our development server (Dell server 1gb mem running Linux). This took 36 hrs to import which we thought was good.

We then imported the same export file into our live db (Sun server 2gb mem running solaris) BUT this takes nearly 80hrs. WHY?? We have checked all the parameters and they are the same, SGA, Rollbacks, Redo etc...

The import script looks like this:
>imp userid=$usr_id file=$file_name fromuser=$user_from touser=$user_to buffer=5000000 commit=y ignore=y feedback=1000000 >> $log_file

I've even checked the Disks on both servers and they are all 10,000rpm.

Can anyone suggest any ideas??

Also I have been running the following script to check the progress on the live import:
> select substr(sql_text,instr(sql_text,'INTO "'),30) 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
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

At first the import is inserting 102,000 records per min but when it gets to our biggest table that has 50million records it slows down to 7,000 inserts per min..... Why?? Any ideas??

Thanks in advance

Sorry for the long message! :)

Check this thread in Asktom. should help.

http://asktom.oracle.com/pls/ask/f?p...1574966483719,

export with compress=n, you asking for troubles using compress=y

import with indexes=n then create the indexes manually with big sort_area_size, turn the archive log off

regarding the speed, disks same speed so what, you have same RAID, same amount of disks and same disk I/O layout?

36 hours is a lot anyways for that size of database

36 Hrs ..........
80 Hrs ........

I think your hardware is of 1st generation kind.

Go for new hardware and buy as many disks and as many controllers. Your perf will be better.

Tamil

Do an export with direct=Y and while importing specify recordlength to its max value.And as told by pando do not import the indexes.It would faster for index creation after only data has been imported.
Make sure u set the sort_area_size=1GB with nologging clause.

regards
anandkl

Quote:

---

Originally posted by anandkl
Do an export with direct=Y and while importing specify recordlength to its max value.And as told by pando do not import the indexes.It would faster for index creation after only data has been imported.
Make sure u set the sort_area_size=1GB with nologging clause.

regards
anandkl

---

1GB you must be kidding right...? more than 32MB does no extra benefit

Hi Pando,
I have done with sort_area_size ranging from 9MB to 800MB.
I have seen the difference.

I have used sort_area_size < 10MB when using parallel process(degree 4).If i set > 200MB for parallel process oracle errors out.

regards
anandkl

And the difference is...?! How long does one take and the other?

Index creation which were taking 40Min, now get done in max 6 min.

try 32mb and see how long it takes

we have benchmarked from 16mb to 256mb, after passing 32 the difference is minimum

Ok..i will try this.

regards
anandkl

hi,

when indexing....

you can pre-allocate the disk space for index
to avoid the dynamic allocation.

if it is to a different physical disk (other
than the one where your tables are) the
disk contention can be reduced.

also you can set the following parameters high enough.

sort_multiblock_read_count

db_file_multiblock_read_count


-Raja

Quote:

---

Originally posted by krw
[B]We are executing an export/import on our DB as we have tables with many extents etc...

---

How many extents were there per table?

This reply may be a little late but you could check if there isnt any triggers(audit:insert or update) activated on your tables.Disable triggers...import....enable triggers.:D