Home » RDBMS Server » Server Utilities » Import taking forever to finish (Oracle 11.2.0.3, 3-Node RAC, AIX)
Import taking forever to finish [message #624446] Mon, 22 September 2014 16:32 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hello everyone,

The scenario is that I had to take an export of a few tables (total size=2TB), when the database was in read only mode.

I could not use expdp because the DB was in read only mode. As expdp creates some temporary export tables, it did not allow. So I ran export on the required tables.

Now, the database is in read/write mode, and its time to import those tables.

Tables already exist, have the constraints/indexes as well. I disabled the constraints and dropped the indexes.

And then started the import.

The import parameter file has these options:

file=/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_1.dmp
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_2.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_3.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_4.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_5.dmp
buffer=50000000
filesize=42949672960
log=/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0922.log
GRANTS=N
INDEXES=N
ROWS=Y
CONSTRAINTS=N
STATISTICS=NONE
ignore=y
commit=n
fromuser=CRDS_22
touser=CRDS_22
TABLES=DIAG,LINE,PRVDR,RSN,INDVD



I tried to track the import, but can't really predict how much time its going to take

SQL> select sofar,totalwork,time_remaining,START_TIME from gv$session_longops where sofar!=totalwork;

no rows selected


SQL> select USED_UREC,USED_UBLK  from V$TRANSACTION;

 USED_UREC  USED_UBLK
---------- ----------
   2994956      15641


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_minute
from
sys.v_$sqlarea
where
SQL_TEXT like 'insert %into "%' and COMMAND_TYPE = 2 and OPEN_VERSIONS > 0;

 TABLE_NAME                              ROWS_PROCESSED    MINUTES                   ROWS_PER_MINUTE
---------------
into "SYS"."SYS$SERVICE_METRIC           5022              4123.2                    1

into "SYS"."ALERT_QT"  (q_name           64                4123.4                    0

into "SYS"."AQ$_SYS$SERVICE_ME           10043             4123.1                    2




Is there any other better way to track the import?
Are there any other options that would make the import faster?

We don't have PARALLEL option in import, do we?

Please advise.

Thank you,
Ricky
Re: Import taking forever to finish [message #624455 is a reply to message #624446] Tue, 23 September 2014 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Increase buffer to 100M or more if you have RAM, 5M is tiny.
Have you LOB on your tables?

Re: Import taking forever to finish [message #624482 is a reply to message #624446] Tue, 23 September 2014 03:02 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
We don't have PARALLEL option in import, do we?

Yes, you do. Look at PARALLEL. It is available as both a command-line parameter and an interactive-mode parameter.

HTH
-g
Re: Import taking forever to finish [message #624494 is a reply to message #624482] Tue, 23 September 2014 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OP's is using old import not Data Pump, please take some time to read the posts before giving an answer or link.

Re: Import taking forever to finish [message #624496 is a reply to message #624494] Tue, 23 September 2014 04:12 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Apologies. I misread.
Re: Import taking forever to finish [message #624570 is a reply to message #624446] Tue, 23 September 2014 09:30 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Ricky_1362002 wrote on Mon, 22 September 2014 17:32
Hello everyone,

The scenario is . . .
. . . E t c
We don't have PARALLEL option in import, do we?

Please advise.

Execute in background a separate import for each table.

[Updated on: Tue, 23 September 2014 10:02] by Moderator

Report message to a moderator

Re: Import taking forever to finish [message #624691 is a reply to message #624570] Wed, 24 September 2014 10:59 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Michel - No LOB on the tables, thanks, will try that.

Gazzaq - No worries, thanks for the concern though.

LKBrwn - Will probably try that next time. Thank you.


I had to run the import for the biggest table, and it took close to 6 hours. Import is really slow - this has been a very good test.

Will try with the options you guys have suggested in a day or 2 and update here.

Thanks again, everyone.

Cheers!

Ricky
Re: Import taking forever to finish [message #624693 is a reply to message #624446] Wed, 24 September 2014 12:25 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
Some possible things that will affect the speed:


Are you importing across a network?
Is the filesystem where the files reside a remote mount?
Are there triggers on the tables being imported?

Re: Import taking forever to finish [message #624694 is a reply to message #624693] Wed, 24 September 2014 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
> and it took close to 6 hours. Import is really slow

ALTER SESSION SET SQL_TRACE=TRUE;

above can reveal where time is actually being spent.
Re: Import taking forever to finish [message #624853 is a reply to message #624694] Fri, 26 September 2014 10:51 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you Joy, and for your questions:

Quote:

Are you importing across a network?
Is the filesystem where the files reside a remote mount?
Are there triggers on the tables being imported?


-No, import is NOT being done across a network. Its done on the same server, same DB.

-Filesystem does not reside on a remote host, but they do in ACFS.

-Triggers are not imported, I don't see them in import log, and not in export log too.


Thank you BlackSwan. Will do that next time.
Re: Import taking forever to finish [message #624863 is a reply to message #624853] Fri, 26 September 2014 12:50 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
Ricky_1362002 wrote on Fri, 26 September 2014 11:51
Thank you Joy, and for your questions:


-Triggers are not imported, I don't see them in import log, and not in export log too.



Sorry, my message was a little unclear. I meant to ask are there any triggers in the destination tablesthat will be triggered upon importing rows?
Re: Import taking forever to finish [message #624865 is a reply to message #624446] Fri, 26 September 2014 13:10 Go to previous messageGo to next message
John Watson
Messages: 7957
Registered: January 2010
Location: Global Village
Senior Member
To speed things up:
Set all the constraints to enabled novalidate before you export. That will save you a lot of time in the import, it can take hours to validate constraints.
Set workarea_size_policy=manual and sort_area_size=2147483647 (which as big as you can go) and the index creation phase will be much faster.
Re: Import taking forever to finish [message #626601 is a reply to message #624865] Wed, 29 October 2014 17:06 Go to previous message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thanks John.
Previous Topic: escape double-quotes in sqlldr
Next Topic: Impdp - Index creation
Goto Forum:
  


Current Time: Tue Jul 23 15:30:09 CDT 2019