DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: lots of chaining

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    15

    Question

    I just realized one of my tables has thousands of chained rows. Does anyone know the best method of getting rid of this chaining. I would like to rebuild the table with larger extent sizes. I have heard the with oracle 8 there is an option to move a table and change the extent sizes can anyone tell me how this works.

    thanks,
    lacey

  2. #2
    Join Date
    Dec 2000
    Posts
    75
    Your row is too big to fit in a database block so you have to increase the size your database block. How big is your block size, 2k? Now on the other hand, in case of row migration, you can increase PCTFREE so there will be enough room future row update.
    goodhealth

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    ALTER TABLE {table_name} MOVE {tablespace_name};


    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5648&pagenumber=1[/url]

  4. #4
    Join Date
    Sep 2000
    Location
    Singapore
    Posts
    32

    Row Chaining

    Hi,

    Rowchaining, Take a Export of the tables,

    Drop the tables

    Then import the table.

    Regards
    Felix
    DBA

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    goodhealth I think increasing pctfree will worse the situation, since this is chaining not row migration.
    exp/imp wouldnt wok unless you change the pctfree & pctused before import

  6. #6
    Join Date
    Aug 2000
    Posts
    52
    For row chaining ,u have only one solution ,i.e. You change the DB_BLOCK_SIZE init.ora parameter.Pl note that u have to recreate the database.I hope that u must be aware about the reparkations of recreating the database.Now, onwards before
    fixing the DB_BLCK_SIZE ,think about the average row size of your tables.

    SANJAY
    OCP DBA

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by pando [/i]
    [B]goodhealth I think increasing pctfree will worse the situation, since this is chaining not row migration.
    exp/imp wouldnt wok unless you change the pctfree & pctused before import [/B][/QUOTE]

    Pando, I don't think your remark about increasing PCTFREE will make situation about rowchaining is correct.

    If this is real *rowchaining* and not *rowmigration* or *combination of both* then PCTFREE has no efect on it whatsoever. Rowchaining is a situation when row is to big to fit in an empty block. Oracle allways try to place a whole row in a single block, regardles of the setting of PCTFREE. For example, if you have 4K blocksize and you insert the row that occupies 3K in a table that has PCTFREE set to 50%, oracle will put the row in a single block and will not split it in two different blocks to preserve free space as specified by PCTFREE. On the other hand, if the problem is rowmigration then increasing PCTFREE has a huge impact and can greatly reduce rowmigration.

    You can easily find out which type of "continued" rows you actualy have by exp/imp the table or by rebuilding it with MOVE. If after rebuilding the table no more "conitinued" rows are reported then "continued" rows were originaly migrated ones and increasing the PCTFREE will help prevent migrations in the future. If there are still many "continued" rows then they are chained ones and only recreating the database with bigger block size may help.

    In any case, changing the storage parameters for the extents have no efect on rowchaining or rowmigration, as was guesed in lacey's original post.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    absolutely corrct!
    if the rows are so large then recreating would be only solution.
    Seems like 9i would allow multiple block sizes in the database, at the end

  9. #9
    Join Date
    Dec 2000
    Posts
    75
    Hi Pando,

    On row migration, I think we were talking about the same thing. Maybe my posting wasn't clear on PCTFREE.

    goodhealth

  10. #10
    Join Date
    Oct 2000
    Posts
    80
    Alternative method and then a question:
    1. run utlchain.sql
    2. analyze table your_table_name list chained rows;
    3. select * from chained_rows where table_name = your_table_name;
    4. create table asdf as select * from your_table_name where rowid in
    (select head_rowid from chained_rows where table_name ='your_table_name';
    5.delete from your_table_name where rowid in
    (select head_rowid from chained_rows where table_name ='your_table_name');
    6. insert into your_table_name select * from asdf;
    7. drop table asdf;
    8. clean up chained_rows table
    9. analyze table your_table_name list chained rows;

    Anything rows in the last query are chained. The above procedure fixes migrated rows only.

    Now my question: Does this produce the same results as the move table option?
    John Doyle

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