| Why imp very slower then exp [message #420568] |
Mon, 31 August 2009 08:13  |
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
|
Member |
|
|
Hi
I am importing(imp) a table with 35 million rows, It takes 150 min. But for export(exp), it took only 10 min.
I have given below the syntax.
Question
~~~~~~~~
How to reduce imp time? any other parameter will help?
Syntax used
~~~~~~~~~~~
1> exp abc/xyz@orcl tables=test1
file=/dbusr1/time_test.exp log=/dbusr1/time_test.log
direct=y compress=y buffer=50000 feedback=50000
2> imp def/ijk@orcl file='/dbusr1/time_test.exp'
log=/dbusr1/imp_time_test.log
buffer=50000 feedback=50000 fromuser=abc touser=def
I also tried
3> imp def/ijk@orcl file='/dbusr1/time_test.exp'
log=/dbusr1/imp_time_test.log
buffer=100000000 feedback=50000 fromuser=abc touser=def
Note: Due to some os level restriction I can not use expdp/impdp.
Thanks in advance
sbmk_design
|
|
|
|
| Re: Why imp very slower then exp [message #420569 is a reply to message #420568] |
Mon, 31 August 2009 08:19  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Simple i/o. It takes longer to put something into a database than take it out.
One reason is that indexes are being updated for every row inserted (when the table and index already exist). Or if the table is new and has indexes, the indexes have to be built at the end.
Even when there are no indexes, I usually see a 3:1 ratio for exp vs. import.
[Updated on: Mon, 31 August 2009 08:20] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Why imp very slower then exp [message #424437 is a reply to message #420568] |
Fri, 02 October 2009 07:03 |
mohd_dba
Messages: 19
Registered: October 2009
|
Junior Member |
|
|
It will be slow.Suppose if you are exporting the table backup and in that table if there are 1400000 Records.It will take approx 30 Mins.
While importing you are trying to move that data from one schema to another schema table.At that time if you need to specify the parameters like buffer,commit,feedback.
As usual imp will take alot of time.To load that much data.And there should be Sufficient space in the tablespace also.
We need to be verified the Undo tablespace also at that time.
If I had missed please add some more points.
Thanks,
Afroze.
[Updated on: Fri, 02 October 2009 07:03] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
