Re: Chaining and Migration

The difference between chained and migrated rows is pretty minimal. Upon update, Oracle decides if a migration or chaining operation is needed and picks the one that involves the least work. It will chain if only a little data is to be added to the row, but will migrate if a bunch of stuff changes or is added. The exact algorithm for deciding this is proprietary and is subject to change anyway.

The main thing is that migration and chaining are conceptually similar, since they typically involve multiple fetches as you described. Migration will "for sure" cause a second fetch, while chaining might not, if the column you want happens to be in the first row piece(s) in the original block.

Adjust pctfree as you mentioned. Export and recreate and import to get rid of the nasty buggers.

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

Doug Cowles wrote:
>
> Hi..I'm looking for the real under the covers stuff for chaining and
> migration.
> As I understand it, migration means the entire row was moved to
> another data
> block, and a pointer remains in the original block (doubling physical
> access),
> and chaining means it was split up..generally because it was to big
> for the data block.
>
> I have a small table, 866 rows, that after analyzing table compute
> statistics etc.
> reports a CHAIN_CNT of 89.
>
> I believe I discovered why. The PctFree is set to 10%, and frequent
> updates are
> made that make the lengths of these rows go from about 39 to 49
> bytes. (Still
> pretty small). I'm using The O'Reily Performance Tuning Book, and
> gathering
> from this formula (p. 348) , that I should set the PCTFREE to 20%, and
> my problem
> will probably be solved.
> Formula is pctfree = (end row length - start length ) / end
> row length * 100.
>
> Question though is.. how can I identify what is migrated and what is
> chained.
> The reason I am confused is, the book says the following
> I can get a count of the number of chained rows, by doing this:
>
> select substr(name,1,40), VALUE
> from v$sysstat
> where name = 'table fetch continued row'
>
> Then doing a select count(*) from offensive table,
> and redoing the above query, and then comparing the result.
>
> When I do this, I get the same VALUE number, 1559,
> leading me to believe the rows are migrated, not chained.
>
> But...
>
> When I use the following method (pg 663) to get migrated rows -
>
> select substr(rowid,1,8) blk
> from offensivetable
> group by substr(rowid,1,8)
> having
> sum(nvl(vsize(col1) + 1,0)
> + nvl(vsize(col2) + 1,0)
> .....
> + nvl(vsize(col9) + 1,0) + 5) > 'BLOCKSIZE' - 100)
>
> I only get three blocks.
>
> (This method adds 1 byte for each NULL column, 5 for each row, and 100
> for header)
>
>
> So the question is..
> Why is the CHAIN_CNT 89?
> Does this mean that within those 3 blocks, there are 89 chained rows?
> Do I really have chained rows, or are they all migrated rows.
> If I do have chained rows, how can I find out more about them.
> If I have migrated rows, same question.
>
> I did a
> ANALYZE TABLE OFFENSIVETABLE LIST CHAINED ROWS;
> and I do get 89.
> But, in the book, it says
> "In Oracle 7, you can use the analyze command to identify chained
> rows.
> By issuing ANALZE in the <above form> you tell Oracle to record
> all chained and migrated row references into the system table....."
>
> Emphasis on "chained AND migrated"
>
> I'd like to distinguish them from each other.
>
> Thanks,
> Slightly retentively yours,
> Dc.
Received on Mon Jan 11 1999 - 23:00:47 CST