PT - Fragmentation and Defragmentation Table using shrinking table

Table Defragmentation Methods : - 

• Shrinking tables

• Moving tables (Alter Table Move example)

• Online redefinition (Row Migration Using Online Redefinition ) 

• Export/Import ( EXPDP | IMPDP - table_exists_action=tuncate  |  Or drop and recreate expdp | impdp )
  
  

Fragmentation and Defragmentation Table scenario using shrinking table

C:\Users\varun>sqlplus sys/system123@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 5 21:12:10 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.6

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn soe/soe@pdb

Connected.

SQL> set sqlprompt "ADMIN> "

• Drop and recreate table cust

ADMIN> DROP TABLE CUST CASCADE CONSTRAINTS;

Table dropped.

ADMIN> CREATE TABLE CUST

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) PCTFREE 0 TABLESPACE SOETBS;

Table created.

• Insert values into CUST table 

ADMIN> INSERT INTO CUST

  2  SELECT LEVEL AS CUSTOMER_NO,

  3   DBMS_RANDOM.STRING('A', 15) FIRST_NAME,

  4   DBMS_RANDOM.STRING('A', 12) LAST_NAME,

  5   DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,

  6   DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2

  7  FROM DUAL

  8  CONNECT BY LEVEL <= 100000;

100000 rows created.

ADMIN> COMMIT;

Commit complete.

• Create Index CUST_NO_IDX, CUST_FNAME_IDX on CUST object

ADMIN> CREATE INDEX CUST_NO_IDX ON CUST (CUSTOMER_NO) NOLOGGING TABLESPACE SOETBS;

Index created.

ADMIN> CREATE INDEX CUST_FNAME_IDX ON CUST(FIRST_NAME) NOLOGGING TABLESPACE SOETBS;

Index created.

ADMIN> BEGIN

  2  FOR I IN 1..100000 LOOP

  3   IF MOD(I,2)=0 THEN

  4   UPDATE CUST SET NOTE1=NULL, NOTE2=DBMS_RANDOM.STRING('A',

  5  TRUNC(DBMS_RANDOM.value(0,10))) WHERE CUSTOMER_NO=I;

  6   IF MOD(I,100)=0 THEN

  7   COMMIT;

  8   END IF;

  9   ELSIF MOD(I,3)=0 THEN

 10   DELETE CUST WHERE CUSTOMER_NO=I;

 11   END IF;

 12  END LOOP;

 13  COMMIT;

 14  END;

 15  /

PL/SQL procedure successfully completed.

ADMIN> -- index dropped because we want to test full table scan (FTS)

ADMIN> DROP INDEX CUST_NO_IDX;

Index dropped.

• Analyze table cust to detremine status of used and free blocks

ADMIN> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

• Script to display deleted space on CUST  table

ADMIN> SELECT BLOCKS, BLOCKS*8192/1024 TOTAL_SIZE_KB, AVG_SPACE,

  2  round(BLOCKS*AVG_SPACE/1024,2) FREE_SPACE_KB

  3  FROM USER_TABLES WHERE TABLE_NAME='CUST';

    BLOCKS TOTAL_SIZE_KB  AVG_SPACE FREE_SPACE_KB

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

      1762         14096       4122       7092.74

• Query to enable row movement in the CUST table. 
• ( It means already some update in progress stop or let complete)

ADMIN> ALTER TABLE CUST ENABLE ROW MOVEMENT;

ALTER TABLE CUST ENABLE ROW MOVEMENT

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

• Again run enable row movement.

ADMIN> ALTER TABLE CUST ENABLE ROW MOVEMENT;

Table altered.

•  Execute to shrink the CUST table

ADMIN> ALTER TABLE CUST SHRINK SPACE CASCADE;

Table altered.

• Analyze table CUST

ADMIN> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

• Check the space status of CUST Table

ADMIN> SELECT BLOCKS, BLOCKS*8192/1024 TOTAL_SIZE_KB, AVG_SPACE,

  2  round(BLOCKS*AVG_SPACE/1024,2) FREE_SPACE_KB

  3  FROM USER_TABLES WHERE TABLE_NAME='CUST';

    BLOCKS TOTAL_SIZE_KB  AVG_SPACE FREE_SPACE_KB

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

       866          6928         73         61.74

ADMIN>

PT - Row Migration Using Online Redefinition

Row Migration Using Online Redefinition

• The chaining percentage (CHAIN_PCT) is not gathered using stats gathering. 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST');

PL/SQL procedure successfully completed.

• Using Analyze CHAIN_CNT (row migration and row chaining of blocks will be detected).

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';

• Correcting Row Migration Using Online Redefinition

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

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

     61267      30.63         431         10                 13036

• To solve the row migration issue by online redefinition. The script rebuilds the table in its same tablespace.

SQL> BEGIN

  2  DBMS_REDEFINITION.REDEF_TABLE(

  3   UNAME => 'SOE',

  4   TNAME => 'CUST',

  5   TABLE_PART_TABLESPACE => 'SOETBS',

  6   INDEX_TABLESPACE => 'SOETBS');

  7  END;

  8  /

PL/SQL procedure successfully completed.

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         10                 13059

Correcting Row Chaining by using Larger Block Size

 Correcting Row Chaining by using Larger Block Size

• Row chaining Scenario Creation :

SQL> DROP TABLE CUST_ORDERS;

Table dropped.

SQL> DROP TABLE CUST;

Table dropped.

SQL>

SQL> set timing on

SQL> CREATE TABLE SOE.CUST

  2  ( CUSTOMER_NO    NUMBER(6),

  3   FIRST_NAME VARCHAR2(40),

  4   LAST_NAME  VARCHAR2(40),

  5   NOTE1 VARCHAR2(4000),

  6   NOTE2 VARCHAR2(4000)

  7  ) TABLESPACE SOETBS;

Table created.

Elapsed: 00:00:00.00

SQL>

SQL> INSERT INTO SOE.CUST

  2  SELECT LEVEL AS CUSTOMER_NO,

  3         DBMS_RANDOM.STRING('A', 40) FIRST_NAME,

  4         DBMS_RANDOM.STRING('A', 40) LAST_NAME,

  5         DBMS_RANDOM.STRING('A', 4000) NOTE1,

  6         DBMS_RANDOM.STRING('A', 4000) NOTE2

  7  FROM DUAL

  8  CONNECT BY LEVEL <= 10000;

10000 rows created.

Elapsed: 00:00:52.86

SQL> set timing off

SQL> COMMIT;

Commit complete.

SQL>

SQL> CREATE INDEX CUST_NO_IDX ON CUST(CUSTOMER_NO) NOLOGGING TABLESPACE SOETBS;

Index created.

• Analyze Table CUST

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

• Check row chaining using query below. 

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

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

     10000        100        8101         10                 20048

• Connect database check and create tablespace with db_block_size=16|32. In our case it is window machine we are getting issue by increasing 32 so we use the value to 16.

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 4 23:03:30 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';

VALUE

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

8192

• Testing curresnt session CPU used by this session and DB time 

SQL> @ test_queries.sql 10000

System altered.

System altered.

PL/SQL procedure successfully completed.

Table created.

old   4: FOR I IN 1..&1 LOOP

new   4: FOR I IN 1..10000 LOOP

PL/SQL procedure successfully completed.

Table created.

MYSTAT

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

CPU used by this session: 96

DB time: 651

table fetch continued row: 0

Table dropped.

Table dropped.

• Set DB_16K_CACHE_SIZE scope is memory 

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME                 BLOCK_SIZE CURRENT_SIZE

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

DEFAULT                    8192         3552

DEFAULT                   16384          160

• Create Tablespace TBS16K with block size 16K

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

• Set Quota to schema on tablespace

SQL> ALTER USER SOE QUOTA UNLIMITED ON TBS16K;

User altered.

SQL> conn soe/soe@pdb

Connected.

• Move table cust to new tablespace

SQL> ALTER TABLE CUST MOVE TABLESPACE tbs16k;

Table altered.

SQL> ALTER INDEX CUST_NO_IDX REBUILD;

Index altered.

• Analyze and check status

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        8095         10                 10090

• Testing curresnt session CPU used by this session and DB time

SQL> @ test_queries.sql 10000

System altered.

System altered.

PL/SQL procedure successfully completed.

Table created.

old   4: FOR I IN 1..&1 LOOP

new   4: FOR I IN 1..10000 LOOP

PL/SQL procedure successfully completed.

Table created.

MYSTAT

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

CPU used by this session: 81

DB time: 544

table fetch continued row: 0

Table dropped.

Table dropped.

SQL>