Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Chaining and Migration

Chaining and Migration

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Mon, 11 Jan 1999 19:39:57 -0500
Message-ID: <369A99DD.E5E462D6@bigfoot.com>


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 - 18:39:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US