INSERT Performance Benchmark - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: INSERT Performance Benchmark

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by ckwan
    Thanks for all the valueable response.

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

    Thanks
    It won't beat the parallel append insert for speed, nor for the effort to write the code.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  2. #12
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by abhaysk
    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.
    Well ur quote from the docs says it very clearly that during parallel DML, Append is the default and free space is not reused. So you prove my point. No freelist in picture so giving a high freelist of 4 is of no use as shown by Tamil.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    So you prove my point. No freelist in picture so giving a high freelist of 4 is of no use as shown by Tamil.
    Exactly, which is all we know, but i wana see Tamil's response on these quotes.

    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"

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===========
    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.

    Abhay
    ========================================

    I agree your point. During my benchmark, I observed oracle create 4 processes (let me say 441, 442, 443, 444) for insert operation. And the temp segments in the table's tablespace are marked with 441.1717 with extent id 0, 441.1718 (temp segment name) with extent id 1...., and for the 2nd process 442.1818 with extent id 0, 442.1819 with extent id 1...,
    and for the 3rd process 443.120 with extent id 0, 443.121 with extent id 1..., and for the 4th process 444.891 with extent id 0, 444.892 with extent id 1, 444.893 with extent id 2.....
    And finallly oracle merges all the extents for the table.

    I do not know how to find out freelists used in the processes. The manual says it is not used. But I gave 4 freelists, I got my best performance with the combination of parallel dml and append hint.

    I will do more tests and inform you later.

    Tamil

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Did you see any change in response time when/if you omited freelist?
    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"

  6. #16
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Adding more freelists does not help when I did the test again with one freelists. I got the same execution time.
    PARALLEL DML+APPEND HINT+NOLOGGING will not use FREELISTS, even though multiple extents are created by each parallel process.

    Tamil

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