-
Import too slow
Hi all ,
I am trying to import a database in orcle 8. My export file
size is 2.4 GB. My DB block size is 8192 . It's taking around
12 hours . There are some big indexes on a massaive table.
Still.... is there are ways to improve on this time ?
Is this 12 hours to import is something normal ?
Regards
Oracle DBA
National Bank Of Kuwait
-
Definitely, 12 Hrs is not a normal, for a 2.4GB export file. What is the OS? Check if there is any other process going on in your server? Check your CPU utilization also.
Sathish
-
Have you tried to import without indexes first, and after do an import with indexfile ... After that modify the index creation script with nologging, parallel and with sort_area_size high?
Cheers
-
If you are recalculating your statistics, it will add a lot of time.
David Knight
OCP DBA 8i, 9i, 10g
-
Hi all ,
My Operating system is windows NT. How will
stop calculating statistics. ( or How will I know
it's calculating statistics )
Regards
Oracle DBA
National Bank Of Kuwait
-
HElo
Pls increase ur BUFFER(one of the Import Parameter) value other than default one and try.
Regards,
Sudheer
Project Lead,India
-
Most importantly, have you checked the alert log if at all there are any error whil import was in progress, specifically related with temporary tablespace?
as you said indexes are big, they will need a huge sort area in temporary tablespace for the same.
Are you using temporary tablespace as LMT ?
OCP 8.0, 8i, 9i
-
Can you attach a copy of the parfile you used.
Did you set BUFFER= and COMMIT= in the parfile?
MH
I remember when this place was cool.
-
I was mot specifying the buffer size , so
the system was using the default.
Can someone recommand a better buffer size
How does COMMIT parameter going to affect the
performance.
Also can someone suggest a beter SORT_AREA_SIZE.
Presently the value for sort area & retian size
is 264000.
Oracle DBA
National Bank Of Kuwait
-
try BUFFER=20000000
and to build the index separately issue the below commands - if you have enough memory you can give more than 20Megs.
alter session set sort_area_size = 20000000 ;
alter session set sort_area_retain_size = 20000000 ;
In addition for large index :
Use the noLogging feature so the logging does not take place.
Use parallel degree creation to create the Index in parallel.
Make sure you give a Lot of Space on the Temp Tablespace.
Put the Temp Tablespace on a Separate Controller so that there is no contention.
Also if possible make sure this is the only session which is accesing the temp tablespace.
Giani
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
|