RE: unexpected heavy buffered IO during partition bitmap index rebuild
- From: "Tornblad, John" <JTornblad@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2012 14:06:31 -0500
Thanks very much for the responses.
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
