Table Fragementation - Very Urgent...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Table Fragementation - Very Urgent...

  1. #1
    Join Date
    Jun 2002
    Posts
    88
    Dear Gurus,

    Today morning i use this command in my database following query:

    select count(chain_cnt) from dba_tables;

    count(CHAIN_CNT)
    ----------------
    12210


    And also i use following query:

    SQL> select owner, table_name, num_rows, chain_cnt,
    (chain_cnt*100/num_rows) pct
    ,
    2 empty_blocks, blocks
    3 from dba_tables
    4 where chain_cnt > 0
    5 and owner not in ('SYS','SYSTEM')
    6 ;

    OWNER TABLE_NAME NUM_ROWS
    ------------------------------ ------------------------------ ----------
    CHAIN_CNT PCT EMPTY_BLOCKS BLOCKS
    ---------- ---------- ------------ ----------
    BIS BIS_TARGET_LEVELS 323
    2 .619195046 5 15

    HR FF_FORMULAS_F 18
    1 5.55555556 240 25

    APPLSYS FND_COMPILED_DESCRIPTIVE_FLEXS 3763
    33 .876959872 480 555


    OWNER TABLE_NAME NUM_ROWS
    ------------------------------ ------------------------------ ----------
    CHAIN_CNT PCT EMPTY_BLOCKS BLOCKS
    ---------- ---------- ------------ ----------
    BEN BEN_EXT_FLD 799
    86 10.7634543 0 20


    I got following result. How to avoid this problem?

    How to avoid the chaining in my database?

    How to analyze my tables? beacause now people are accesing in the
    database.

    When i analyze it would be effect my table or database? This following
    command is
    correct:

    execute dbms_stats.gather_table_stats (ownname => 'APPLSYS'
    , tabname => 'FND_COMPILED_DESCRIPTIVE_FLEXS'
    , partname=> null
    , estimate_percent => 20
    , degree => 5
    , cascade => true);



    Can u please explain step by step,

    Awaiting ur reply,

    Thanks in advance,
    Iyyappan.M

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by iyyappan
    select count(chain_cnt) from dba_tables;

    count(CHAIN_CNT)
    ----------------
    12210
    That is very amusing query. It tells you absolutely nothing about your row chaining. What it realy tells you is how many tables in your database has been analysed, nothing else! If you wanted to find out how many chained/migrated rows you have in your etire database, you should use:

    select SUM(chain_cnt) from dba_tables;

    Regarding other three tables that realy have some chained/migrated records, you could rebuild them (ALTER TABLE MOVE) with increased PCTFREE parameter to prevent future row migration. But if you are expiriencing row chaining (not row migration) then your block size is too small, you need larger database block size to eventually avoid this.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    and what's so urgent?

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