PT- Correcting Row Migration by Increasing PCTFREE

 Correcting Row Migration by Increasing PCTFREE

Scenario: 

We have table name CUST, After anlayze table we find out CHAIN_CNT migrated and chained row of table.

One of the method by increasing PCT_FREE, but block size will increase after using it. Stats gather do not capture CHAIN_CNT so we analyze table.

Solution:

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

     61267      30.63         431         10                 13036

SQL> ALTER TABLE CUST PCTFREE 20;

Table altered.

SQL> ALTER TABLE cust MOVE TABLESPACE SOETBS ONLINE;

Table altered.

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

         0          0         429         20                 15443