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
Note: Before change in production, must be tested dev/ preprod env. Load test must be followed.
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' );
Ref:
Oracle support Doc ID : 1587179.1
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
Note: Before change in production, must be tested dev/ preprod env. Load test must be followed.
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' );
Oracle support Doc ID : 1587179.1
