INSERT Performance Benchmark

[slimdave]

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.

[adewri]

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.

[abhaysk]

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.

[tamilselvan]

===========
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

[abhaysk]

Did you see any change in response time when/if you omited freelist?

[tamilselvan]

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