RE: unexpected heavy buffered IO during partition bitmap index rebuild

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Sep 2012 16:37:39 -0400

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Wednesday, September 26, 2012 4:04 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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).


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


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


Other related posts: