Increase speed of import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Increase speed of import

  1. #1
    Join Date
    Feb 2001
    Posts
    22

    Angry

    Hi there,

    Currently, our production database produces 1G export dump. It contains some large table which contains 1 million rows.

    We need to import the Production Data into development machine for full testing everyday.

    However, I think that the import speed is quite slow.

    Our development Platform is SUN E420R with 2G RAM.

    Import take nearly 2.5 - 3 hours to completely load all data into DB. It is very time-consuming.

    Currently, I already used INDEX=N to prevent user index update during import.

    BUFFERS is also increased in IMP parameter.

    Any other way to increase the speed of import?


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Are you doing a regular conventional export or export direct. Export direct would speed up your import. Also have a good size of RBS and set the COMMIT=N, but COMMIT=Y prevents the RBS growing inordinately large.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can try DIRECT=Y option with INDEX=N

    If the TABLES are same in both PRDN and DEVL, do not drop the entire schema in DEVL DB. Just truncate all tables so that already allocated extents will be reused.

    Increase the REDO LOG FILE SIZE to 100MB in DEVL, ensure checkpoint occurs after the redo fills in.
    Or to minimize the redo log, before import alter all tables into NOLOGGING mode. This will definitely speed up your import process.


  4. #4
    Join Date
    Feb 2001
    Posts
    22
    If I use TRUNCATE TABLE instead of DROP USERNAME CASCADE, will it affect other objects like sequences, trigger, constraints ?

    [QUOTE][i]Originally posted by tamilselvan [/i]
    [B]You can try DIRECT=Y option with INDEX=N

    If the TABLES are same in both PRDN and DEVL, do not drop the entire schema in DEVL DB. Just truncate all tables so that already allocated extents will be reused.

    Increase the REDO LOG FILE SIZE to 100MB in DEVL, ensure checkpoint occurs after the redo fills in.
    Or to minimize the redo log, before import alter all tables into NOLOGGING mode. This will definitely speed up your import process.

    [/B][/QUOTE]

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If you are going to export the entire data every day, then I would suggest that you look into that of doing incremental export. This would help you to save time a lot.

    It didn't strike to me during my previous posting.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Feb 2001
    Posts
    40

    Talking

    Hi,
    I read that the truncate command does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations.

    Karthi

  7. #7
    Join Date
    Feb 2001
    Posts
    22
    It is extract of Oracle8i Utilities Manual.

    Important: Incremental, cumulative, and complete Exports are obsolete features
    that will be phased out in a subsequent release. You should begin now to migrate to
    Oracle’s Backup and Recovery Manager for database backups. See Oracle8i
    Operating System Backup and Recovery Guide for more information.
    Restrictions:

    You can do incremental, cumulative, and complete exports only in full
    database mode (FULL=Y). Only users who have the role
    EXP_FULL_DATABASE can run incremental, cumulative, and complete
    Exports.

    I think that incremental export is not suitable to my case. It is because I want several schemas in production database only.

    [QUOTE][i]Originally posted by sambavan [/i]
    [B]If you are going to export the entire data every day, then I would suggest that you look into that of doing incremental export. This would help you to save time a lot.

    It didn't strike to me during my previous posting.

    Sam [/B][/QUOTE]

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    <b> We need to import the Production Data into development machine for full testing everyday.

    However, I think that the import speed is quite slow.
    </b>

    As the above posting of yours did not specify the mode of exp/imp, I went ahead and gave the suggestion to do the incremental, asuming that you were doing FULL=Y. This incremental and etc, I'm pretty sure that it would be alive on its 8i env. This feature, I believe got started with 8.0.0 and had propagated to 8i. I'm not sure about its life span on other on coming versions. But currently it is available on 8i version for sure.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sambavan,
    Just a remark: incremental (cumulative...) mode of export was not introduced in 8i nor in 8.0, it was awailable way back, at least from version 6.0. So if it is awailable for mor than 10 years now, you can bet it will be awailable for at least another 10 years (until the release 17j, I suppose ;)).

    edwtsang,
    If you are on 8i, why don't you explore the "transportable tablespace" option - I think this feature was made exactly for the cases like yours. In essence, you group all the objects in your production database that you want to transfere occasionally to your dev database in one or more "self-contained" tablespace(s). When you wan't to transfer them, you simply put those tablespace(s) in read-onlymode, copy the files to the dev environment and "plug" them into your developement database.

    Check the documentation enad technet for details.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width