Home » RDBMS Server » Server Utilities » Why imp very slower then exp (Oracle-10.2 /Linux)
Why imp very slower then exp [message #420568] Mon, 31 August 2009 08:13 Go to next message
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 Go to previous messageGo to next message
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 #420570 is a reply to message #420568] Mon, 31 August 2009 08:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Major factor is that
while importing you are rebuilding the indexes (if any).
Do a simple math.
In the source look for size of tables and size of indexes.
While exporting, exp will grab only table data.
While importing, imp is rebuilding the indexes also.

Again,
What is the rationale for values of BUFFER you are using?
Re: Why imp very slower then exp [message #420571 is a reply to message #420568] Mon, 31 August 2009 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note: Due to some os level restriction I can not use expdp/impdp.

Which ones?

Regards
Michel
Re: Why imp very slower then exp [message #424437 is a reply to message #420568] Fri, 02 October 2009 07:03 Go to previous messageGo to next message
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

Re: Why imp very slower then exp [message #424444 is a reply to message #424437] Fri, 02 October 2009 07:46 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not that (almost) your posts are missing something but they (mostly) add nothing to previous answers.

Regards
Michel
Previous Topic: import .dmp file
Next Topic: Data Pump issue
Goto Forum:
  


Current Time: Fri Mar 29 03:42:59 CDT 2024