Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Chaining and Migration
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