Re: db file sequential read waits on INSERT into non-indexed partitioned table
- From: jonathan@xxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Sep 2011 13:43:00 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
