RE: unexpected heavy buffered IO during partition bitmap index rebuild

Upon examination of chained rows it appears this is a HUGE difference between the two segments... as it turns out the segment/partition with great bitmap index rebuild performance has ZERO chained rows... while the segment/partition with poor bitmap index rebuild performance has 35% of its rows chained/migrated. Whoops! Thought we already looked at that.

Obviously that shoots to the top of our suspect list.

I think we have "migrated" rows (which I think are rows that grew to be bigger than a block over time) and not true "chained" rows (which I think are rows that were too big for a block to begin with). Our pattern is definitely insert smallish rows... then update, update, update over days and months.

The migrated rows issue I think is our number one lead to follow, until we prove or disprove it is an issue. It could be that the migrated rows are *also* triggering an access path issue during the bitmap index rebuild itself, so we could have a bad interaction between these two specific things. Having 35% of the rows migrated doesn't sound like a crisis per se but I am worried if they are being migrated multiple times
(is there an additive effect?) or if there is something "peculiar" about
how the bitmap index rebuild really accesses the data blocks (one would presume it is a FTS) but perhaps that is not true, or not always true.

Our oldest partitions were moved to secondary / tiered storage during which I would not be surprised if COMPRESS were a part of that MOVE. However, we are not updating those oldest partitions (only the most recent 6 months).

Our PCTFREE was 40% on these segments. I think the immediate calculation to be done is: about how big are our rows when they start life, and then how big are they growing to over time (up to 6 months for our oldest updates)? I suppose the way to get about this is brute force: see how much space a set of "baby rows" takes up vs. an equal set
(in cardinality) of "geezer rows". If there are any better tools /
methods to conduct this analysis, I'd appreciate hearing about it. I would like to have a better statistical description of row sizes. Is there a direct method for querying "row size"?

-john

</pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.

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