This discussion is archived
8 Replies Latest reply: Dec 20, 2012 8:20 PM by moslee RSS

Performance Issue on Traditional Import and Export

moslee Newbie
Currently Being Moderated
Scenario
=====
Oracle 9i Enterprise Edition (9.2.0.8)
Windows Server 2003 (32 bit)
--- to ---
Oracle 11g Enterprise Edition (11.2.0.3)
Windows Server 2008 Standard R2 (64 bit)

Hi to all

I'm doing a upgrade from 9i to 11g and i am using native imp/exp to migrate those data.. For my 1st round of testing, I have done the following:

1) Full DB Export from 9i. exp system/<pwd>@db FULL=Y FILE=export.dmp log=export.log
Encountered warning "EXP-00091: Exporting questionable statistics." (On hindsight, I know that I need to set the characterset as per my server before exporting) Nevertheless, I proceeded on with this 8.4GB dmp file that has the warning "EXP-00091: Exporting questionable statistics." The characterset in 9i is US7ASCII. My export took 1 hour, my 9i is actually only a small 26GB.
2) Full import to 11g. My 11g is a newly created DB with characterset WE8MSWIN1252. I know that schemas and objects will be automatically created in 11g.
3) However, the problem I face is that this importing of data has been running for the past 4 hours and counting and it is still not done.

My question is:
Is it because of the difference in the characterset in the dmp file and 11g that is causing this importing to be slow? Could it be that characterset conversion is causing high overhead?

OR

Is it because I exported all the indexes from 9i and now during importing, it is taking a long time to create those indexes in 11g? Should I have export full but set index=F and create a index creation script from 9i and run it in 11g, so as to save time?

OR

Both of the above is causing the importing to keep on running? Or ??

Edited by: moslee on Nov 21, 2012 11:54 PM

Edited by: moslee on Nov 22, 2012 12:01 AM
  • 1. Re: Performance Issue on Traditional Import and Export
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    All things being equal, rule of thumb is that an import will take about twice as long as an export. Since you have not provided the exact imp command, nor do we know what hardware and IO specs are on the source and target systems, it is hard to say. The change in characterset is not a factor in the import speed. You could try to create a trace file as per this MOS Doc

    How to trace and produce tkprof output for a traditional export (exp)/import (imp) performance issue on Windows platform [ID 1154365.1]

    HTH
    Srini
  • 2. Re: Performance Issue on Traditional Import and Export
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    the most likely thing is the index creation. exp just create a load of create index statements which then have to run during the import - this obvously means a lot more work is done during import. With exp/imp parallelisim is not used by the index build 9as you can specify with impdp). You may be able to set parallelism on all the indexes in the source system before you do the export so they have the parallel clause when it comes to create them ( i think that would work) - or just extract all the index ddl and run them in a session with force parallel ddl specified.

    There could also be some other reason for the slowness - make sure your PGA is very large for the index builds for instance.

    Cheers,
    Harry
  • 3. Re: Performance Issue on Traditional Import and Export
    moslee Newbie
    Currently Being Moderated
    Hi to all

    All my tablespaces in oracle 9i database is on 4096 block_size... But I pre-created those same tablespaces in 11g and they are on 8192 block_size..
    Am I right to say that it is the differences in block size that is slowing down this importing?
    If so, how can I solve this problem? If I use "IGNORE=Y" in my import statement, will it help? Thanks..

    I think i will follow my 9i db and create the tablespaces in 11g in 4096 block_size...



    Here's the new server (11g) specs (i know this is not officially supported by oracle yet):
    Win Server 2012
    HP Proliant DL360p
    Intel Xeon CPU @ 2GHz
    8GB Ram


    ======
    Logfile
    ======

    C:\>imp system/<pwd>@dbnew full=y file=export.dmp

    Import: Release 9.2.0.1.0 - Production on Thu Nov 22 11:29:08 2012
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
    Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Export file created by EXPORT:V09.02.00 via conventional path
    Warning: the objects were exported by OEM, not by you

    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    IMP-00017: following statement failed with ORACLE error 29339:
    "CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'F:\ORADATA\UNDO\"
    "UNDOTBS.DBF' SIZE 5120 , 'F:\ORADATA\UNDO\UNDOTBS1.DBF' SIZE 5120 "
    " EXTENT MANAGEMENT LOCAL "
    IMP-00003: ORACLE error 29339 encountered
    ORA-29339: tablespace block size 4096 does not match configured block sizes
    IMP-00017: following statement failed with ORACLE error 29339:
    "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 4096 TEMPFILE 'D:\ORADATA\TEM"
    "P\TEMP.DBF' SIZE 7524 AUTOEXTEND ON NEXT 20971520 MAXSIZE 8192M EXTE"
    "NT MANAGEMENT LOCAL UNIFORM SIZE 10485760"
    IMP-00003: ORACLE error 29339 encountered
    ORA-29339: tablespace block size 4096 does not match configured block sizes
    .
    .
    .
    IMP-00015: following statement failed because the object already exists:
    "REVOKE "OEM_MONITOR" FROM SYSTEM"
    IMP-00015: following statement failed because the object already exists:
    "CREATE ROLE "HS_ADMIN_ROLE""
    IMP-00015: following statement failed because the object already exists:
    "REVOKE "HS_ADMIN_ROLE" FROM SYSTEM"
    . importing O3's objects into O3

    Edited by: moslee on Nov 22, 2012 6:45 PM

    Edited by: moslee on Nov 22, 2012 7:07 PM

    Edited by: moslee on Nov 22, 2012 7:13 PM

    Edited by: moslee on Nov 22, 2012 7:28 PM
  • 4. Re: Performance Issue on Traditional Import and Export
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    The tablespace block size really doesn't make any difference to anything in most real world cases. The error can just be ignored. The IGNORE=Y option just tells oracle to ignore the fact that the objects already exist and won't help you with the speed.

    To prove if the indexes are the problem you can simply say

    indexes=n

    on the imp command line and then see what the runtime is like.

    This may slow down things later on though when constraints are added but you shoudl at least see some difference in the behaviour.

    Also check your redo logs (number and sizes) - both of which may need increasing - this is a common cause of slow imports as you are constantly waiting for logfiles to switch.

    Cheers,
    Harry
  • 5. Re: Performance Issue on Traditional Import and Export
    moslee Newbie
    Currently Being Moderated
    Hi to all

    Thanks for your reply..

    Actually I redo my whole testing with a new installation of server in new hardware and 11g database...

    I tried using INDEXES=N and is pleased to find out that INDEXES=N makes the export faster by 1 min. Import remains 2x the timing of Export..

    For this testing, both source and destination databases are in different character sets and different db_block_size.

    Remain open for more testing...
  • 6. Re: Performance Issue on Traditional Import and Export
    moslee Newbie
    Currently Being Moderated
    Reckon that difference in characterset during export does affect time taken for importing..

    Tried each NLS_LANG=.US7ASCII and NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 during exporting.
  • 7. Re: Performance Issue on Traditional Import and Export
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    The NLS_LANG value setting should match the database characterset when the export is invoked, else there may be data corruption/truncation. This will be evidenced in the first 10 lines of the export log. An example
    Export file created by EXPORT:V09.02.00 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    export client uses US7ASCII character set (*possible charset conversion*)
    HTH
    Srini
  • 8. Re: Performance Issue on Traditional Import and Export
    moslee Newbie
    Currently Being Moderated
    Hi Srini

    How should I change then? set NLS_LANG=.US7ASCII when I am doing importing into 11gR2 64bit?

    C:\> Set NLS_LANG=.US7ASCII
    C:\> imp system/<pwd>@isdwhnew FULL=Y IGNORE=Y FILE=ISDWH_NOINDEX.DMP LOG=import_ISDWH_NOINDEX.log

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points