RE: unexpected heavy buffered IO during partition bitmap index rebuild
Date: Wed, 26 Sep 2012 18:02:01 -0400
Message-ID: <022601cd9c32$8f3119b0$ad934d10$_at_rsiz.com>
There is a dirty trick for that. If you add a "padding" column that you initialize so that the inserted row with zero number values is similar to the eventual length, you can have an on update trigger that punches the padding column to null if it is not already null. Combined with a low value for percent used, you should not see blocks going back on the free list from this, and you'll tend to end up with a workable number of rows per block so they do not migrate. It will tend to be a little less dense than setting the proper pctfree if I'm thinking correctly, but I think with your mostly numeric getting updated that should not be a problem. You can take into account the actual sizes of the non-numerics that are final on insert if they vary much, or you can default the padding column so you don't have to change any programs. The idea is that each row is close in size to the final average size row in length when you insert it. Don't obsess over it being perfect, because all you need here is a pretty good statistical win to pretty much eliminate row migration.
Calculating the proper pctfree and/or "minimizing" records per block to the good value for you are other things you can do, but the minimizing thing does not work if you have too many rows per block in something already. Hmm. Just found out Hotsos abstracts are due on 9/28. Maybe this is a topic.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Tornblad, John
Sent: Wednesday, September 26, 2012 5:20 PM
To: oracle-l_at_freelists.org
Subject: RE: unexpected heavy buffered IO during partition bitmap index
rebuild
| 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.
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 26 2012 - 17:02:01 CDT