export performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: export performance

  1. #1
    Join Date
    Feb 2003
    Posts
    7

    export performance

    i am trying to export a 120 GB 8.1.7 DB from solaris box to Linux ASE but the export and import are taking 4.5 and 8 hours respectively which far beyond acceptable range.
    i am using direct=y recordlength=16K additionally.

    how can i improve the performance of both export and import.
    i want to use parallelism with both these processes but don't know how to?

    thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you can split the export into multiple parallel steps, that would help. For example, export schemas independently of each other at the same time, or isolate four or five big tables and dedicate an export session to each of them alone. The aim is to maximize i/o here -- once you have reached the limit, that's about all you can do.

    The major overheadon import is that Oracle will build indexes sequentially -- this can lead to greatly under-used resources. parallel imports of the multiple export files will help, but consider that you may still be building indexes on multiple tables simultaneously. For faster index building you really want to be building all indexes on a particular table at the same time. You reduce the chances that you will be re-reading blocks from disk many times, once for each index.

    A solution to this problem is to ...

    i) extract the index ddl statements from the export dump file using import with "indexfile = "

    ii) run the regular import(s) with "indexes=n" (noting that some index creation is unavoidable, for unique constraint enforcement for example)

    iii) submit multiple index creation statements at the same time (optionally with nologging and parallel for each index).

    It would be very worthwhile boosting your sort_area_size before the import also.

    Just to illustrate the benefits of submitting these index builds in parallel and in the right order, I recently had to rebuild index partitions on a fifty partition DW fact table -- sixteen indexes altogether. First time i did it i submitted index rebuilds in parallel, eight at a time (using DBMS_JOB), but i submitted them in the order of index name, then partition name. the result was that every index had to read physical i/o -- it took forever, effectively reading about 1600Gb of physical io. When i submitted the rebuilds in the order of partition then index name, each partition fitted within sga, total physical io was about 80gb, and it finished while i drank a cup of coffee.

    Well that was a longer answer than i expected, but good luck with it. Max out those resources.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by slimdave
    It would be very worthwhile boosting your sort_area_size before the import also.

    Yes!!
    If your maxchine has enaough men the sort_area_size could be as large as your greatest index.
    Your sort_area_retianes_size should be large too.
    Please rfecognize that every parallel process allocates the sort_area_size for it self!

    also there is a init.ora -param some like parallel_auto_tuning;
    Set this param and allocate enaugh LARGE_POOL, it's the best communiion-area for parallel Processes.

    switch off archive log mode and after switching on again make a full hot-backup
    do not place your Dump-file on the same Disk Oracle has it's files because then the import lasts a lot longe due to immense disk-head-movements

    Orca


  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Orca777
    do not place your Dump-file on the same Disk Oracle has it's files because then the import lasts a lot longe due to immense disk-head-movements
    that will certainly affect the non-index import, but the disk head movement is going to happen anyway -- probably a raid system. If i/o on the device the dump file is on then you could certainly spread them out over multiple devices. the index builds will not be affected by this, of course.

    Good point on the parallel process sort_area_size orca. don't want to choke up the memory.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2003
    Posts
    7
    thanks Slimdave and Orca777
    i'll put in these suggestions and let's see how it goes.

  6. #6
    Join Date
    Oct 2000
    Posts
    250
    U also can consider to use the TRANSPORTABLE TABLESPACE. This is much faster than conventional export method. But there are some limitation ... Pls Read the documentation to find out.

  7. #7
    Join Date
    Jun 2000
    Posts
    295
    I saw performance gain a lot by changing
    buffer size if your have a lot free memory on
    OS level.

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