Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Chained Rows again!

    Hi,

    I reorganized a table which had around 21000 out of 23000 rows as chained. I did the reorg with create table as select .

    I checked immediately after recreating that it again has 230 rows chained.

    The db block size is 16k and the average row length is 1832.

    This table is highly active for DMLs and queries and also undergoes lot of Full Table Scans.

    What should I do to make it chained rows free. The db version is 9i.
    Thanks for any help.

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    PCTFREE for the table is 10 and PCTUSED is 40.

  3. #3
    Join Date
    Oct 2003
    Posts
    87
    You need to increase PCTFREE to greater than the avg. that rows in a data block are lengthened due to updates.
    Oracle - DB2 - MS Access -

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Please show DESC output for the table.

    Could it be that those 230 rows are too big to fit on one block?

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    There are 266 columns in the table and the describe output may not fit in this box.
    I HAVE altered PCTFREE to 30 now.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could attach it. But anyway, have you investigated how long your longest rows are? Could they exeed 16K? Seems quite plausible with 266 columns, if there are a few long VARCHAR2s or even CLOBs in there.

  7. #7
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Yes, I think thats the reason. I do see many VARCHAR2(100).
    A different block size be used for a tablespace (possible in 9i.)
    I guess thats the only solution!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Probably the best solution is just to accept it as a fact of life! 230 chained rows isn't really going to bring your database to its knees, is it?

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    No,
    The reason it is bothering me is that the rate with which chaining increases on this table is high. Last time before it was recreated , it had 98% rows chained.
    While it had 0.98% chained rows immediately after it was created, today when I checked it has become 1% !!
    This table is highly active one and so much chained rows would be causing considerable I/O , Wouln't it?
    If there is no other solution, ofcourse I will accept the FACT!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, I agree that you don't want to end up 98% chained again, if possible! A higher PCTFREE, which you have already done, should help. It sounds like this table gets a lot of updates that make big increases to row size. High PCTFREE (maybe more than 30% even?) and low PCTUSED are probably desirable.

  11. #11
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Thank You!
    I will reduce PCTUSED to 20 from 40.

  12. #12
    Join Date
    Oct 2003
    Posts
    87
    Based on the info we have before us, it appears you have 230 rows that are greater in length than a data block. Thus, most of your chaining was caused by low PCTFREE and high PCTUSED in the past. You'll just have to keep upping PCTFREE and lowering PCTUSED 'til you find the "sweet spot."
    Oracle - DB2 - MS Access -

Posting Permissions

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