-
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!
-
-
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
anandkl
-
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
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.
anandkl
-
try 32mb and see how long it takes
we have benchmarked from 16mb to 256mb, after passing 32 the difference is minimum
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|