DBMS_STATS.GATHER_TABLE_STATS , Table Reorg

In OLTP database environment some times performance degraded because of Table plan changes, row-chaining and Row-migration issues. Based on transactions on table, it may require maintenance activities such as table reorganization. After table reorg, it is seen performance improved a lot. 

How to determine whether a table "reorg" is needed or not?

1. It is recommended to test in similar prod/pre-prod environment with gather statistics on the required table.  The preferred percentage for the gather is 100%.

2. Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:

Run using sys user:

SELECT di.index_name,
       trunc((dt.num_rows / di.clustering_factor) /
             (dt.num_rows / dt.blocks),2)
  FROM dba_indexes di, dba_tables dt, dba_constraints dc
 WHERE di.table_name = dt.table_name
   AND dt.table_name = dc.table_name
   AND di.index_name = dc.index_name
   AND dc.CONSTRAINT_TYPE = 'P'
   AND dt.table_name = upper('&Table_Name');


3. As per the result for above query:

VALUE ABOVE 0.75 - DOES NOT REQUIRE REORG

VALUE BETWEEN 0.5 AND 0.75 - REORG IS RECOMMENDED

VALUE LOWER THAN 0.5 - IT IS HIGHLY RECOMMENDED TO REORG

To gather table stats with 100% sampling:

exec dbms_stats.gather_table_stats('&owner','&Table_Name',estimate_percent => 100, cascade=>true, method_opt=>'for all columns size AUTO');

Oracle 10g and 11g : "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan.

Gathering statistics for all objects in a schema:

exec dbms_stats.gather_schema_stats(ownname => '&Schema_name ',cascade => TRUE,method_opt => 'FOR ALL COLUMNS SIZE AUTO' );