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! :)