Chained Rows
Date: Wed, 23 Apr 2014 16:56:39 -0400
Message-Id: <B32F68ED-E939-466D-AC5C-EC48B4B40E37_at_gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 23 2014 - 22:56:39 CEST