Row Migration and Row Chaining - Your Developer knows best!
So today a developer called me an said the production db is slow in doing some updates and the reason is row chaining or row migration. On the development db he does 60 updates a second, on production only about 2 or 3.
Did I tell you already that he is a developer? 
Let me explain the situation
- 6′500′000 rows
- two attributes defined as varchar(2000)
- one of the varchar get fille at insert time, the other later with an update
- characterset AL32UTF8 (1-4 bytes)
- varchar(x) means x bytes and not x characters
- max record size about 2k + 2k + 100bytes = ~4k
- block size 8k
The situation look like row migration could happen often. I startet to check it. First let’s check the “fetch continued row”. This gives an evidence of row chaining or row migration. It indicated how many time since the last startup (2 days ago) a second block hat to be loaded.
SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 706806
Well, the is something. Let’s check deeper. The updates happen on the table BUUBUU (anoymized table name for security reason, the application is high confidential).
Standard Check
SQL> @utlchain.sql -- installed the the chained_rows table
SQL> ANALYZE TABLE BUUBUU LIST CHAINED ROWS;
-- LIST CHAINED ROWS lets you identify migrated and chained rows of the analyzed table.
SQL> ANALYZE TABLE scott.emp LIST CHAINED ROWS;
SQL> SELECT * FROM chained_rows
Thath is about 1.3% of the records. No big deal.
But let’s check more. How many rows are in a block. First I tried to crap around with the vsize stuff found in the internet. After 30 min. needed one of my davidoff mini cigarillos - while smoking I hat an idea. Why use the inflexible vsize stuff to get a imprecise result. The ROWID will me tell an excat result.
Detailed Result for each block:
select substr(rowid,1,6) "object",
substr(rowid,7,3) "file",
substr(rowid,10,6) "block",
count(substr(rowid,16,3)) "countrows"
from BUUBUU where rownum < 30
group by substr(rowid,1,6), substr(rowid,7,3), substr(rowid,10,6)
order by count(substr(rowid,16,3))
/
Summary:
select count(1) ,sub.countrows from (
select substr(rowid,1,6) "object",
substr(rowid,7,3) "file",
substr(rowid,10,6) "block",
count(substr(rowid,16,3)) countrows
from BUUBUU
group by substr(rowid,1,6), substr(rowid,7,3), substr(rowid,10,6)
) sub
group by sub.countrows
order by 1
/
The result:
COUNT(1) COUNTROWS
---------- ----------
89 12
1437 4
1876 3
2056 2
2577 1
2599 10
2684 5
3273 9
4940 8
25696 11
432275 6
600786 7
12 rows selected.
We see most blocks contains from 6 to 7 rows. (let’s discuss about a bigger block size another time).
Futher I wanted to check about the update statments. How many rows are updated with the pricemessage.
select count(1) from BUUBUU where BLOBOBJECT is null;
COUNT(1)
----------
0
select count(1) from BUUBUU where PRICEMESSAGE is null;
COUNT(1)
----------
6911440
select count(1) from BUUBUU;
COUNT(1)
----------
7238457
Just 327017 record got an update. That’s aobut (ca. 5%)
My position is now clear, that this not a real issue on this table (may be a point for optimization).
Tomorrow we will do a trace the seek the wait times (I could tell you the result already, it will be net waites)
Btw. If you have a problem with row migration or row chaining:
- Row Chaining: Make your blocks bigger or use LOB’s not stored inline.
- If you have row migration think about increasing your PCTFREE on the table