Chained rows

[BV1963]

I have noticed that database has chained rows in following tables:

TABLE_NAME CHAIN_CNT
------------------------------ ---------
IND$ 526
ICOL$ 472
CDEF$ 1860
TAB$ 100
USER$ 1
VIEW$ 30

Does anyone know why is it happening and how to avoid it? Are there any potential major performance problem related to this?

Thanks.

[Mnemonical]

Decrease pctfree.

Your queries may read more blocks than necessary for getting a row that is chained. More I/O when you could do less I/O = less performance.


F

[patel_dil]

You mean temper with SYS objects?

[SANJAY_G]

Originally posted by Mnemonical
Decrease pctfree.

Your queries may read more blocks than necessary for getting a row that is chained. More I/O when you could do less I/O = less performance.


F

First verify that the rows are migrated or chained, because DBA_TABLES doesn't differentiate between two.
If migrated rows then increase the PCTFREE and to remove the existing migrated rows.
Use
- utlchain.sql to create chained_rows table.
- analyze table list chained rows
- copy the rows to temporary table, delete and insert again.

You can't do much if the rows are chained rows.

But if the objects are sys objects then better leave it as it is.

Sanjay

[Calvin_Qiu]

Refer to 'Data blocks overview' section of Oracle Administrator Guide for information about Chain Row and Migration.

[ksarav]

hi,
rowchaining occurs if ur row cannot fit inside a block. better have a bigger block size.

[mike9]

Hi BV1963,

It is not surpising to see some row chaining in the data dictionary tables. It should not have any real performance impact.

Also I recommand you NOT to do any changes at these system tables to avoid row chaining.

[BV1963]

Thanks to everybody who replied.
I was a bit concerned doing any kind of "fixes" for system tables.I would expect Oracle to create data dictionary such a way that migration of the rows never happen.

[jmodic]

Originally posted by BV1963
I would expect Oracle to create data dictionary such a way that migration of the rows never happen.

Well, in some cases they realy can't do it that way, while in some other cases they should do something to prevent row migration.

An example case where they can't realy avoid row migration would be SYS.VIEW$. If you look the definition of that table you'll notice that it has set PCTFREE to the default value of 10%. You'll also notice that it stores the definition of a views in a column TEXT of type LONG. So what happens when you change the existing view with CRATE OR REPLACE? Oracle simply updates the TEXT column of the corresponding row in VIEW$ with the new view definition. And what happens when the new text of a view is much much longer than the previous one? Very good chance of a migrated row, I would say. But the fact is that no matter how high they would set PCTFREE for VIEW$, such row migrations are allways possible, there is no way to prevent that. In addition, there is not only the possibility to have migrated rows in that table - it is very trivial to create a view that will actually cause chained rows in VIEW$, not only migrated ones. Once again, no matter what PCTFREE they have set for that table, they can't actually prevent row chaining/migration in it. Actually 10 is quite decent compromise for the nature of that table, IMHO.

Howvere there are those second type of tables where they realy could do something to minimize the possibility of row migration. Some such examples are TAB$, IND$ and ICOL$. They all have PCTFREE=0 (!). Why is that? I belive this was set way back when there were no possibility for those tables to be updated ever. However with the apperance of CBO we actually analyze tables, indexes and indexed columns, don't we? And what happens in the dictionary when we analyze? Exactly those three tables (ammong some others) get updated! Hence pretty good chance for migrated rows, I would say. So I would say they simply forgot to adjust SQL.BSQ scripts with the introduction of CBO. PCTFREE should not remain at 0, it should be set to some more meaningfull value (IMHO the default 10% or something like that would be just fine).

But as other have mentioned, you shouldn't be worried about those few migrated/chaned rows, as the additional I/O owerhead is negligible in most cases when it comes to data dictionary tables.