INSERT Performance Benchmark
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: INSERT Performance Benchmark

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    INSERT Performance Benchmark

    Hi All,
    I have a question on the INSERT INTO ...AS SELECT ... statement;

    I know this is workable and been use long time already.

    But I found is this the only best performance we can achieve.
    Anyone have better and fastest way of insert into another table.


    Regards,
    ckwan

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Sure:

    1) Put new table in nologging mode
    2) insert /*+ APPEND *//*+ APPEND */
    3) Put the table again in logging mode

    Cheers

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by aarroyob
    Sure:

    1) Put new table in nologging mode
    2) insert /*+ APPEND *//*+ APPEND */
    3) Put the table again in logging mode

    Cheers
    Because of the 'nologging'-clause it is wise to backup your tablespace/db right after the insert (if the data is very important).

    HTH
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do it parallel with nologging.

    Ex:
    alter table xxx storage (freelists 4) nologging;
    alter session force parallel dml parallel 4;

    insert /*+ append */ xxx nologging
    select .....from ....;

    alter session disable parallel dml;

    Recently, I did a benchmark. The above method is 75 % faster than just "insert /*+ append */ ".

    Tamil

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan

    alter table xxx storage (freelists 4) nologging;
    alter session force parallel dml parallel 4;
    Just a thought -- when you insert data using "append", are the freelists used at all? I wouldn't think that they are.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by slimdave
    Just a thought -- when you insert data using "append", are the freelists used at all? I wouldn't think that they are.
    No freelists are not used as append inserts above the high watermark.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    FREELISTS are used in parallelism.

  8. #8
    Join Date
    Oct 2000
    Posts
    250
    Thanks for all the valueable response.

    Do u all think the method of BULK COLLECT INTO method will be useful in this scenario ?

    Thanks

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by tamilselvan
    FREELISTS are used in parallelism.
    I'm some how not able to relate parallesim and freelists and append.

    AFAIK if append hint is there then freelist doesnot come into picture and inserts will not reuse the freespace below the HWM, only the freespace available above the HWM will be used. Now how does parallesim make use of the freelist or vice versa ?
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Tamil :

    I guess you belive Oracle Docs, herez the quotes.

    When you are inserting in parallel DML mode, direct-path INSERT is the default.
    Now how will direct-path INSERT work? the very basic and foremost quote is

    During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.
    Amar : I think you may now co-relate between Append/parallel/freelists


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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