Chained Rows
- From: Kenny Payton <k3nnyp@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Apr 2014 16:56:39 -0400
I’ve been tracking down and coming up with ways to online remedy a somewhat
well known derivative of chained rows known as migrated rows for some time. I
have also stumbled across intra-block chaining where rows that have a column
populated beyond position 255 causes the row to split into multiple row pieces
and these row pieces are frequently stored in the same block. What I didn’t
realize until this week was that sometimes these row pieces do not end up in
the same block and result in a single row stored in multiple database blocks.
I had a full table scan that was plagued by what I thought to be migrated rows
and after kicking off a process that batch deletes each row and re-inserts them
a good portion of these rows still showed up as chained. I came to the
realization that deleting and re-inserting the rows fixes the problem by
chance. If it so happens that the block the row is being inserted into has
enough space for the 1st row piece but not the 2nd it gets chained to a new
block.
I repeatedly processed these rows until nearly all of them were converted from
inter-block to intra-block chained. In all there were 10M rows that showed up
as chained, 2.5M of which did not get corrected during the first pass leading
to my investigation. I am still not 100% sure how many of them were
inter-block chained to begin with but minimally 2.5M. This was costing my
database somewhere between 700M and 800M physical reads per day ( or
approximately10k reads/sec ) due to a process that excessively scans this table
throughout the day.
I know a lot of the tricks for avoiding chained rows and migrated rows but the
only trick I know of to element these intra or inter block chained rows is to
break the table into multiple tables or the row into multiple rows. Neither
reduces the amount of IO I would need to do in order to retrieve the full data.
If you have any ideas send them on.
Kenny
--
//www.freelists.org/webpage/oracle-l
Other related posts:
