Chained rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Chained rows

  1. #1
    Join Date
    Nov 2001
    Posts
    335

    Chained rows

    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.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    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

  3. #3
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    You mean temper with SYS objects?
    -- Dilip

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
    Last edited by SANJAY_G; 11-06-2002 at 10:07 PM.

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Refer to 'Data blocks overview' section of Oracle Administrator Guide for information about Chain Row and Migration.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #6
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    hi,
    rowchaining occurs if ur row cannot fit inside a block. better have a bigger block size.
    saravana kumar

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    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.
    Last edited by mike9; 11-07-2002 at 06:49 AM.

  8. #8
    Join Date
    Nov 2001
    Posts
    335
    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.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width