RE: unexpected heavy buffered IO during partition bitmap index rebuild

Hmmm... beyond our initial insert of a row that contains amounts and counts that are mostly zero (16 different NUMBER columns), we update these counters over time (and only the NUMBER columns). Going from the native numeric storage representation of "Oracle's zero" to a non-zero amount may be eventually biting us and causing the migration it appears (I say this only after recently re-learning Oracle's native representation in base 10 exponent format). And here I was thinking I had "initialized" the storage of the zero columns enough to prevent unreasonable row migration (based on whimsy, apparently). "1" takes twice as many bytes to store as "0"!

Oh it's dangerous to Not Know Things.

-john

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Wednesday, September 26, 2012 1:38 PM To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org Subject: RE: unexpected heavy buffered IO during partition bitmap index rebuild

Brilliant. One slight nit though, I think. I believe the one step away bit
is correct regarding the first migrated row piece, but that it is possible
for a row to be both migrated and chained. I hope that is an edge case for
most people.

I have not tested this recently, but I *think* it has to be true. Still, they shouldn't need more than one forwarding reference per row piece.

When the final length of a column that starts out null (or with a currently
short dummy default) is predictable within a reasonable range, row migration
can be mitigated by making the default value understood to be a dummy a length somewhere in the upper half of the range of final sizes. Whether this
turns out to be convenient depends on many things.

Quite possibly JL's soon to be published code is even better or can be used
to predict what a useful default length might be for columns in future rows
as well as a smart pctfree.
Of course if the variation in row length is due to the growth of numbers in
length as numbers get farther away from zero, you cannot really set artificial defaults for that. Fortunately most of the time the bulk of the
row vsize change is due to non-numeric values.

Good luck.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, September 26, 2012 4:04 PM To: oracle-l_at_freelists.org
Subject: Re: unexpected heavy buffered IO during partition bitmap index rebuild

Comments in-line

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Tornblad, John" <JTornblad_at_emdeon.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, September 26, 2012 8:06 PM Subject: RE: unexpected heavy buffered IO during partition bitmap index rebuild

| 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.

    "However, to get 6.7M random I/Os in 19M rows my initial thought was perhaps the data load does something like:" -- that's not a coincidence
then.

|
| I think we have "migrated" rows (which I think are rows that grew to
be
| bigger than a block over time)

Not quite right - migrated rows are small enough to fit in a block, but were too big to fit the space that was available in the block at the moment
they
were updated.

| 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

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. This
is different from a simple query where Oracle (usually) doesn't have to

report the rowid, which means it can simply wait until it reaches the relocated row before reporting it.

| I am worried if they are being migrated multiple times

Migrated rows can only be "one step away". The original location holds a

rowid that points to the actual location of the WHOLE row. If the row migrates a second time the WHOLE row migrates again and the pointer at the
original location points to the latest location, you don't get a chain of
pointers.

|
| 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.

Coincidentally I was looking at a piece of code I wrote about 20 years ago
(Oracle 6) to do exactly this, and thinking I really ought to publish it.
I'll see if I can publish it tomorrow night (i.e. about 24 hours from now).

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


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

</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