Re: db file sequential read waits on INSERT into non-indexed partitioned table

Wayne,

I can't explain why you are waiting for the enqueues, but I think the extra current block gets appear because every time a row in the array goes into a different partition from the previous row your session does a bitmap search for the first free block in that partition. The more partitions you have the more likely it is that the next row belongs in a different partition from the previous row. The difference (worst case) is probably three current gets per row, although I haven't tested your scenario, which may make it four.

Damage limitation:
a) sort the data by target partition before inserting it. b) use freelist management, not ASSM.

The db file sequentil reads are probably the effect of enabling flashback datase, every block you "new" may have to be read and copied into the flashback log.

The FB enqueues are Format Block - and I think you're formatting 16 blocks at a time; the TT enqueues are tablespace bitmap update locks taken as you modify space from the tablespace freespace bitmap (there are related reasons for TT's), and the HW lock is the highwatermark bump lock, taken as you update the segment header highwater mark. I can understand the TT resulting in a wait, but if you are the only person inserting into this table I don't understand why the FB and HW see waits.

I have a few clues that things are done a little differently in 11.2, and the changes may make a difference to the results you are seeing.

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l