committ=y and import performance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: committ=y and import performance

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    committ=y and import performance

    Hola,
    Working on getting an import to scream along. Just curious why Oracle docs suggest to set commit=y to improve the performance of a large import? I mean I can do test case after test case to show why one would not frequently commit in btach processing, what makes import so special?

    Thanks

    Steve
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Hello Steve

    I don't know why commit=y should improve performance either, but I believe that there is an exception to this rule. When importing LONG,LOB,BFILE,REF,ROWID or type columns array inserts aren't done, so a commit is performed after each row. They say that for tables with these kind of columns it is preferable to set commit=n (and hope your rollback segments are big enough!). I'm going to be working on similar project to yours this week, and i'm going to benchmark LONG column imports with commit=y and commit=n. I'll let you know what I find out.

    Also, you might find this free tool of interest (I've not used it myself):

    http://pepi.sourceforge.net/#who

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    Commit=y is good, it is your friend!

    If you do not use commit=y then Oracle (by default) will try to import the ENTIRE table before issuing a commit. This can be a vewy ( Elmer Fudd influence) bad thing if you have large tables, it can kill your RBS or redo, or undo, or whatever the hell you want to call it.

    I usually use commit=y and buffer=4000000 (give of take) this way oracle will commit after every 3MB or so. You can make the buffer bigger if you like, that way you will have less frequent commits.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't think that commit=y will make the import faster - it will probably slow it down. However it would give you a better chance of the import actually completing successfully without running out of rollback space.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    From the Oracle 8i docs:

    Specifying COMMIT=Y ... improves the performance of large imports
    Hmmmm, another Oracle myth I wonder? I think I might try bench marking this too.

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by hacketta1
    From the Oracle 8i docs:



    Hmmmm, another Oracle myth I wonder? I think I might try bench marking this too.
    from the utilities guide
    "Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports"

    I benchmarked this and it did not see an appreciable difference.

    Just wondering if others had any thoughts

    Steve
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    To speed up IMPORT

    - Use COMMIT=N. This will cause import to commit after each object (table), not after each buffer. This is why one large rollback segment is needed.


    from http://metalink.oracle.com/metalink/...T&p_id=93763.1

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Basically you need to know how large your tables are. If they are HUGE, put in a commit, it ain't gonna kill you and the impact will be marginal.

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    If you really want to speed up your import, indexes=no. Take the creation indexes script an increase your sort_area_size, do in parallel ...

    Anyway import with commit=no. Look for your biggest table and create or increase your rbs to at least this size.

    Cheers

  10. #10
    Join Date
    Apr 2003
    Posts
    353
    Originally posted by pando
    To speed up IMPORT

    - Use COMMIT=N. This will cause import to commit after each object (table), not after each buffer. This is why one large rollback segment is needed.


    from http://metalink.oracle.com/metalink/...T&p_id=93763.1
    It is not correct..
    During a large table import ..

    Exporting from one session:=
    14:54:37 SQL> host exp80 XXXXxx/asdf@asdf file=d:\ordrdetl.dmp direct=y constraints=n tables=ordrdetl

    14:55:54 SQL> select count(*) from ordrdetl;

    COUNT(*)
    ---------
    874350

    real: 2610
    Importing the same into another user.
    14:56:14 SQL> host imp80 backup/xxx@test file=d:\ordrdetl.dmp commit=y full=y

    From ANother session of the backup user
    I parallely run the count.. It increase ..gradually depending on the Buffer value we have
    given

    14:57:03 SQL> select count(*) from ordrdetl;

    COUNT(*)
    ---------
    33250

    real: 16

    14:58:13 SQL> /

    COUNT(*)
    ---------
    90972

    real: 31
    14:58:16 SQL> /

    COUNT(*)
    ---------
    115178

    real: 31
    14:58:21 SQL> /

    COUNT(*)
    ---------
    147630

    real: 32
    14:58:27 SQL> /

    COUNT(*)
    ---------
    165319

    real: 47
    14:58:30 SQL> /

    COUNT(*)
    ---------
    181146

    real: 31
    14:58:31 SQL> /

    COUNT(*)
    ---------
    182875

    real: 63
    14:58:33 SQL> show user
    USER is "BACKUP"

    But during commit=n you cannot even decribe the table..in the other user

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