RE: unexpected heavy buffered IO during partition bitmap index rebuild
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <JTornblad@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2012 17:24:24 -0400
I'm confused. After a CTAS, you might still might have chaining, but I don't
understand why there would be migration.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tornblad, John
Sent: Wednesday, September 26, 2012 4:44 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: unexpected heavy buffered IO during partition bitmap index
rebuild
Jonathan, thanks!
| There is no "access path" effect, Oracle is doing a table scan, but
when it
| creates the index it needs the rowid it has just acquired and the data
from
| the migrated row AT THE SAME TIME, so it has to follow the pointer."
What got me starting up a worry thread on "access path" was the observation
that the bitmap index rebuild on the old partition with zero migrated rows
what doing nearly all direct path reads... vs. the new partition which
(although 35% of rows are migrated) was nothing but buffered reads all over
the place. Struggling to understand "what"
little demon decision-maker in Oracle was deciding on that, and why...
hence got interested in _small_table_threshold, blocks already in the buffer
and other misc. points to consider why the decision to do one or the other
was so different (at least in 11.2.0.3).
Oracle support seems to be telling me to just make the migrated rows go away
and no problem. Ok, I agree... but it leaves me still wondering about the
original problem... could 35% migrated rows (in our
environment) have really lead to 285 times the IO? If so that is an
electric fence that gives quite a (disproportionate) jolt! I would
have thought (naively, I'm sure) that even with 100% of the rows
migrated-and only one hop away-would have been at worst 2 times the IO to
scan the table. In fact, after CTAS our recreated segment is 1/2 the size
(in blocks) of the segment with 35% of its rows migrated. Seems like
something is going astray in a Really Bad Way. However, this may be another
one of those moments were it just doesn't pay to be curious beyond a certain
point.
Looking forward to your code on calculating row size... perhaps I've missed
it somewhere but I don't think I've seen that anywhere.
-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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
