Install SwingBench On Window

1. Download swingbench for window.
2.  Pre Requestors : install Java JDK 8 on winow10.
3. Install oracle client.
4. Open command Prompt and set oracle client path.

set PATH=C:\app\client\varun\product\19.0.0\client_1\jdk\jre\bin;%PATH%

Go to swingbench path location :

\swingbenchlatest\swingbench\winbin

• run Swingbench.bat

• Set database connection string

    

Oracle Database connection through cloud.

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