Import running slow!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Import running slow!

  1. #1
    Join Date
    Jan 2003
    Posts
    35

    Thumbs down Import running slow!

    Hi all,

    Db: 8.1.7.2
    db Size: 20Gb Data / 12gb Indexes
    OLTP System
    Sun Solaris OS

    We are executing an export/import on our DB as we have tables with many extents etc...

    We did an export:
    >exp userid=$usr_id file=$file_name statistics=none compress=y >> $log_file
    This is fine and it took 14 hrs to export.

    To test the time it takes to import we did a test import on our development server (Dell server 1gb mem running Linux). This took 36 hrs to import which we thought was good.

    We then imported the same export file into our live db (Sun server 2gb mem running solaris) BUT this takes nearly 80hrs. WHY?? We have checked all the parameters and they are the same, SGA, Rollbacks, Redo etc...

    The import script looks like this:
    >imp userid=$usr_id file=$file_name fromuser=$user_from touser=$user_to buffer=5000000 commit=y ignore=y feedback=1000000 >> $log_file

    I've even checked the Disks on both servers and they are all 10,000rpm.

    Can anyone suggest any ideas??

    Also I have been running the following script to check the progress on the live import:
    > 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_min
    from sys.v_$sqlarea
    where sql_text like 'INSERT %INTO "%'
    and command_type = 2
    and open_versions > 0;

    At first the import is inserting 102,000 records per min but when it gets to our biggest table that has 50million records it slows down to 7,000 inserts per min..... Why?? Any ideas??

    Thanks in advance

    Sorry for the long message!

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Check this thread in Asktom. should help.

    http://asktom.oracle.com/pls/ask/f?p...1574966483719,
    -- Dilip

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    export with compress=n, you asking for troubles using compress=y

    import with indexes=n then create the indexes manually with big sort_area_size, turn the archive log off

    regarding the speed, disks same speed so what, you have same RAID, same amount of disks and same disk I/O layout?

    36 hours is a lot anyways for that size of database

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    36 Hrs ..........
    80 Hrs ........

    I think your hardware is of 1st generation kind.

    Go for new hardware and buy as many disks and as many controllers. Your perf will be better.

    Tamil

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Do an export with direct=Y and while importing specify recordlength to its max value.And as told by pando do not import the indexes.It would faster for index creation after only data has been imported.
    Make sure u set the sort_area_size=1GB with nologging clause.

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by anandkl
    Do an export with direct=Y and while importing specify recordlength to its max value.And as told by pando do not import the indexes.It would faster for index creation after only data has been imported.
    Make sure u set the sort_area_size=1GB with nologging clause.

    regards
    anandkl
    1GB you must be kidding right...? more than 32MB does no extra benefit

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Pando,
    I have done with sort_area_size ranging from 9MB to 800MB.
    I have seen the difference.

    I have used sort_area_size < 10MB when using parallel process(degree 4).If i set > 200MB for parallel process oracle errors out.

    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    And the difference is...?! How long does one take and the other?

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Index creation which were taking 40Min, now get done in max 6 min.
    anandkl

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    try 32mb and see how long it takes

    we have benchmarked from 16mb to 256mb, after passing 32 the difference is minimum

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