DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: row chaining & migration

  1. #1
    Join Date
    Feb 2001
    Posts
    75
    Hi,

    1. How do you know whether a row is chained or migrated? I mean either of the two.

    2. Given that a table has a lot of chained/migrated rows, how can one determine the cause.

    I have a table with hardly any varchar2 fields, last columns not null and it still has a lot of chained rows.


    Kailash Pareek

  2. #2
    Join Date
    Oct 2001
    Posts
    122
    Row chaining usually happens due to incorrect PCTFREE setting. There are lots of scripts available to find out
    what tables have this problem.

    Once found out you need to reorganise the table.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    You should do several steps:

    1. exec script utlchain.sql from
    -- for UNIX - $ORACLE_HOME/rdbms/admin/
    -- for NT - $ORACLE_HOME/rdbms8x/admin/
    in you schema

    2. execute
    analize table main_table list chained rows;


    3. in table CHAINED_ROWS oracle write all chained and migrating rows

    4. then you can recreate all bad rows
    (if length of row less then DB blocksize)

    create table temp_main_table as
    select *
    from main_table m
    where exists (select 1 from CHAINED_ROWS c
    where c.owner_name = 'OOOOO' and
    c.table_tame = 'MAIN_TABLE'
    c.head_rowid = m.rowid);

    check all rows in temp_main_table

    delete from main_table m
    where exists (select 1 from CHAINED_ROWS c
    where c.owner_name = 'OOOOO' and
    c.table_tame = 'MAIN_TABLE'
    c.head_rowid = m.rowid);

    insert into main_table select * from temp_main_table;

    good luck.


  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Chaining and Migration are different, but both are called CHAINING in the database. Migration CAN be fixed and as prafful and Shestakov have pointed out, Chaining on the other hand indicates the DB Block size isn't large enough to accompidate a row. DB_BLOCKSIZE can only be specified at DB creation and IS NOT dynamic, so if your DB contains MOSTLY chaining and NOT migration, you may have to consider a database recreation and re-import the data. How do you tell if it CHAINING and NOT migration... after you have corrected the migrated records as seen via Shestakov's script, and you reanalyze the table and there are STILL chained rows... then these rows are CHAINED and NOT MIGRATED, and can only be resolved by recreating the database and re-importing that data.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Oct 2000
    Posts
    467
    if you're running 8i, then check out for the column chain_cnt in dba_tables.
    Once identified, you can either adopt the method given by grjohnson or do an export/import.
    Vinit

  6. #6
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by vinit:
    if you're running 8i, then check out for the column chain_cnt in dba_tables
    After analyzing the table(s) !

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    Originally posted by Raminder
    Originally posted by vinit:
    if you're running 8i, then check out for the column chain_cnt in dba_tables
    After analyzing the table(s) !
    ofcourse after analyze..!!
    Vinit

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    If need list of tables with chained(migration) row you can use something like this:

    1. analize table ... compute statistics;

    2. execute query -->
    !! you chould change (4096 - 110) for DB blksize and average length of block header from you database

    col Prc_free format 999.99 heading '% Free' justify right ;
    col len format 99999 heading 'Avg|length|record' justify right ;
    col Rec_Blk format 9999.99 heading 'Records|in|blocks' justify right ;
    col own format a20 heading 'Schema' justify left
    ;
    col tbl format a20 heading 'Table|name' justify left
    ;
    col count_ext format 9999 heading 'Total|extents' justify right ;
    col nrow format 99999999 heading 'Total|records' justify right ;
    col chain format 99999 heading 'Chained|records' justify right ;
    col blk format 999999 heading 'Total|blocks' justify right ;
    col emp format 999999 heading 'Empty|blocks' justify right ;

    select a.OWNER own,
    a.TABLE_NAME tbl,
    a.AVG_ROW_LEN len,
    a.blocks blk,
    a.EMPTY_BLOCKS emp,
    decode(a.blocks,0,100,(a.EMPTY_BLOCKS/(a.EMPTY_BLOCKS+a.BLOCKS))*100) Prc_free,
    a.NUM_ROWS nrow,
    a.CHAIN_CNT chain,
    decode(a.AVG_ROW_LEN,0,0,((4096 - 110)/a.AVG_ROW_LEN)) Rec_Blk,
    count(d.extent_id) count_ext
    from all_all_tables a,
    dba_extents d
    where a.TABLE_NAME = d.segment_name and
    a.OWNER = d.owner and
    a.owner not in ('SYS','SYSTEM' ) and
    d.segment_type = 'TABLE'
    group by a.OWNER ,
    a.TABLE_NAME ,
    a.AVG_ROW_LEN ,
    a.blocks ,
    a.EMPTY_BLOCKS ,
    decode(a.blocks,0,100,(a.EMPTY_BLOCKS/(a.EMPTY_BLOCKS+a.BLOCKS))*100),
    a.NUM_ROWS ,
    a.CHAIN_CNT ,
    decode(a.NUM_ROWS,0,0,(a.CHAIN_CNT/a.NUM_ROWS)*100) ,
    decode(a.AVG_ROW_LEN,0,0,((4096 - 110)/a.AVG_ROW_LEN))
    having count(d.extent_id) >= 5 or a.CHAIN_CNT > 0
    order by 1,2
    ;

    col Prc_free clear
    col len clear
    col Rec_Blk clear
    col own clear
    col tbl clear
    col count_ext clear
    col nrow clear
    col chain clear
    col blk clear
    col emp clear


  9. #9
    Join Date
    Feb 2001
    Posts
    75

    Row chaining & migration

    Hi,

    Thanks to all those replied. I already knew that all.

    I wanted to know whether one can find whether the specific problem is chaining or migration. Oracle support says that Oracle engine treats them same and there is no way to find it.

    Second, having the problem, how can one find why it happened. I have following table.

    CREATE TABLE vip_effort_pricing (
    eitem_find_num VARCHAR2(20) NOT NULL,
    effort_num NUMBER(10,0) NOT NULL,
    base_um_brkpt_qty NUMBER(9,4) NOT NULL,
    adv_um_brkpt_qty NUMBER(9,4) NOT NULL,
    adv_um VARCHAR2(2),
    adv_price NUMBER(9,2),
    last_modified_dt DATE,
    source_archive_dt DATE,
    source_control_dt DATE
    )
    PCTFREE 05
    PCTUSED 40
    TABLESPACE modstbs01
    LOGGING;

    The block size is 16K. The avg_row_len, num_rows &chain_cnt for this table are 46,2263419, 137741. There is no update to first col which is part of composite keyl. The last col also always have a value. The other columns prior to it are initially blanks & updated later. But oracle is supposed to allocate space for them as the last column has value for it.

    The chained rows are about 6% of total. What % is OK?

    Kailash Pareek

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    6% in this table is high value.
    I thing you problem is:

    - PCTFREE value is very high for updatable table (95%)
    - you have a lot of UPDATE statments with increase row lenght ~20-30% of initial lenght
    - you should set PCTFREE ~ 80% or little bit less.

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