Space

How to Choose the Most Needed to be Reorganized Tables

We all know that table reorganization could improve the performance of DML on the tables. Your supervisor might ask you to do this action more proactively. Before deciding which tables should be reorganized, you have to know which table(s) is/are most needed to reorganize when next time available.

In most cases, ranking the number of chain rows for every table may not reflect the whole truth of your database. That is why I recommend the percentage of chain row below.
SQL> set linesize 250;
SQL> select * from (select table_name, 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 order by 3 desc, 4 desc) where rownum <= 10;

In the above statement, I retrieve only top 10 percentage of chain rows of tables.

Please note that, this result is based on usable and current statistics of your database. If you didn’t do regular analyzes on your tables. You can get fresh statistics by this statement:
SQL> exec dbms_stats.gather_database_stats;

Leave a Reply

Your email address will not be published. Required fields are marked *