Forum Stats

  • 3,873,491 Users
  • 2,266,586 Discussions
  • 7,911,551 Comments

Discussions

Estimate Chain Count

carajandb
carajandb Member Posts: 260 Bronze Badge
edited Jan 11, 2016 6:18PM in Database Ideas - Ideas

To identify tables with a need for reorganization it will be beneficial to have an advanced dbms_stats feature to estimate chain_cnt and other reorganization related columns for a table (e.g. avg_row_length, avg_space). Analyse table is not feasible because it destroys the optimizer statistics and analyze table list chained_rows into .. is too expensive.

carajandb954777user6604134User259623 -OracleChris Antogninivinaykumar2pankajrangaManish ChaturvediborneselmarkmevansArpit Jain -OraclectriebPravin TakpireberxLothar FlatzJitendraJagadekarabhagatsinghsysassysdbaAndreas BuckenhoferGeert Gruwezuser7048955BPeaslandDBAtonibony7Sven W.Martin PreissulohmannUser910243567
32 votes

Active · Last Updated

Comments

  • have you tried analyze table xx o LIST CHAINED ROWS?

  • carajandb
    carajandb Member Posts: 260 Bronze Badge

    List chained rows is far to expensive as is inserts all rows in the chain table. I only need an estimate.

  • Pravin Takpire
    Pravin Takpire Technical Services Manager Member Posts: 1,763 Gold Trophy

    I think along with this feature for table, we should also have some thing like rebuild candidate for indexes too.

    regards

    Pravin

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    The problem is that knowing the number of chained rows do not tell us if it's bad or not. I prefer to use the 'table fetch continued row ' statistic.

    BPeaslandDBA
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    The problem is that knowing the number of chained rows do not tell us if it's bad or not. I prefer to use the 'table fetch continued row ' statistic.

    I think Tanel Poder said something similar in http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/.

  • tsasscer
    tsasscer Member Posts: 1

    This SQL shows chained rows counts, and percent of chained rows.  This only shows tables with either more than 10,000 rows or more than 50% chained rows, so you have a better idea of whether it is "bad" or not.  The accuracy of these numbers depend on up-to-date table statistics, and if you reorg a table the values will not change until you gather new statistics.  Also, the FND_STATS package, if you use EBS, does not update the chained rows count for some reason.

    SELECT owner||'.'||table_name table_name, num_rows, chain_cnt, round(chain_cnt/num_rows*100, 2) pct_chained, avg_row_len, pct_free, pct_used

    FROM dba_tables

    WHERE num_rows != 0 and (chain_cnt/num_rows*100 > 50 or chain_cnt > 9999)

    ORDER by table_name ;

  • carajandb
    carajandb Member Posts: 260 Bronze Badge

    This SQL shows chained rows counts, and percent of chained rows.  This only shows tables with either more than 10,000 rows or more than 50% chained rows, so you have a better idea of whether it is "bad" or not.  The accuracy of these numbers depend on up-to-date table statistics, and if you reorg a table the values will not change until you gather new statistics.  Also, the FND_STATS package, if you use EBS, does not update the chained rows count for some reason.

    SELECT owner||'.'||table_name table_name, num_rows, chain_cnt, round(chain_cnt/num_rows*100, 2) pct_chained, avg_row_len, pct_free, pct_used

    FROM dba_tables

    WHERE num_rows != 0 and (chain_cnt/num_rows*100 > 50 or chain_cnt > 9999)

    ORDER by table_name ;

    your skript only works if you use ANALYZE table because with dbms_stats the column chain_cnt will not be updated.

  • sysassysdba
    sysassysdba Member Posts: 459 Silver Badge

    hello,

    Automatic Segment Advisor could do. But i think that Automatic Segment Advisor doesn´t populate DBA_TABLES.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    ... destroys the optimizer statistics ...
    

    I'd need to double check but the new Oracle versions of analyse table don;t destroy the optimiser stats.....

  • Racer I.
    Racer I. Member Posts: 113 Green Ribbon

    I also think gather_stats could be enhanced in this respect. Maybe populate separate migrated_rows, chained_rows, chained_row_pieces columns.