Oracle Segment Advisor

When there are lot updates, deletes inside database, it creates lot of empty pockets of space that are not large enough to insert new data. We call this type of empty space as fragmented free space. Database performance can be impacted by such fragmented space. The process of combining fragmented space into one big free space is known as de-fragmentation.

One of the simplest ways to do it by shrinking table, index segments to reclaim the wasted space. But before you can directly shrink table/index, you must run Oracle segment advisor to get recommendations as to how much space can you reclaim.

In the below activity I will show you how to work with the Oracle segment advisor

• Create test tablespace

• Create test table & index

• Analyze table & index

• Delete some rows

• Analyze table & index again

• Run Oracle segment advisor

• Review segment advisor

• Shrink table cascade

• Analyze table & index size

• Deallocate space

Create test tablespace

We will create a separate tablespace to store new table for this activity

CREATE TABLESPACE seg_tbs DATAFILE '/u01/data/oracle/testdb/db_files/testdb/seg_tbs.dbf' SIZE 2G;

Create test table & index

Let us create a test table, insert some records and create an index into the above tablespace

Note: you can use this method to create new tables into a different tablespace even though the default tablespace of a user is different.

CREATE TABLE seg_table(id NUMBER, text VARCHAR2(4000)) tablespace seg_tbs;

Insert some dummy 100K records inside the table

SET TIMING ON
BEGIN
FOR I IN 1..100000
LOOP
   INSERT INTO seg_table VALUES (I, DBMS_RANDOM.STRING('P','4000'));
   COMMIT;
END LOOP;
END;
/

Create index on table

CREATE INDEX seg_index ON seg_table(TEXT) tablespace seg_tbs;

Analyze table & index

Gather table and index stats

ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;

Let us check the size of each segment (table and index)

set lines 999;
col segment_name for a20;
SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS, 
EXTENTS FROM   DBA_SEGMENTS 
WHERE  SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');

Delete some rows

Let us delete half of the rows from the table. Note, the below procedure is deleting alternate rows from the table which will create lot of fragmented space

SELECT COUNT(*) FROM SEG_TABLE;

SET TIMING ON 
BEGIN
   FOR I IN (SELECT id FROM seg_table)
   LOOP
      IF MOD(I.id,2) != 0 
      THEN 
        DELETE seg_table WHERE id=I.id;
	COMMIT;
      END IF;
   END LOOP;
END;
/

SELECT COUNT(*) FROM SEG_TABLE;

Analyze table & index again

Now that we have delete half of the rows, let us gather stats for table and index again

ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;

Let us check the size of each segment (table and index)

Note: even after deleting half of the records, the segment size will still show same as it was before delete.

SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS, 
EXTENTS FROM   DBA_SEGMENTS 
WHERE  SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');

Run Oracle segment advisor

Even though we have delete half of the rows, the statistics show no change. We will run Oracle segment advisor and see what recommendations we will get

Create advisor task for table

SET SERVEROUTOUT ON
DECLARE
   TSK_ID          NUMBER;
   TSK_NAME        VARCHAR2(100);
   TSK_DESCRIPTION VARCHAR2(500);
   OBJ_ID          NUMBER;
BEGIN
   TSK_NAME        := 'ANALYZE_TABLE_TASK';
   TSK_DESCRIPTION := 'Segment advice for SEG_TABLE';
   
   -- 1. Create a Task
   DBMS_ADVISOR.CREATE_TASK(
      ADVISOR_NAME => 'Segment Advisor',
      TASK_ID      => TSK_ID,
      TASK_NAME    => TSK_NAME,
      TASK_DESC    => TSK_DESCRIPTION);
      
   -- 2. Assign the object to the task 
   DBMS_ADVISOR.CREATE_OBJECT(
      TASK_NAME    => TSK_NAME,
      OBJECT_TYPE  => 'TABLE',
      ATTR1        => 'SYS',
      ATTR2        => 'SEG_TABLE',
      ATTR3        => NULL,
      ATTR4        => NULL,
      ATTR5        => NULL,
      OBJECT_ID    => OBJ_ID);

   -- 3. Set the task parameters
   DBMS_ADVISOR.SET_TASK_PARAMETER(
      TASK_NAME    => TSK_NAME,
      PARAMETER    => 'recommend_all',
      VALUE        => 'TRUE');

   -- 4. Execute the task 
   DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
   
END;
/

Create advisor task for index

SET SERVEROUTOUT ON
DECLARE
   TSK_ID          NUMBER;
   TSK_NAME        VARCHAR2(100);
   TSK_DESCRIPTION VARCHAR2(500);
   OBJ_ID          NUMBER;
BEGIN
   TSK_NAME        := 'ANALYZE_INDEX_TASK';
   TSK_DESCRIPTION := 'Segment advice for SEG_INDEX';
   
   -- 1. Create a Task
   DBMS_ADVISOR.CREATE_TASK(
      ADVISOR_NAME => 'Segment Advisor',
      TASK_ID      => TSK_ID,
      TASK_NAME    => TSK_NAME,
      TASK_DESC    => TSK_DESCRIPTION);
      
   -- 2. Assign the object to the task 
   DBMS_ADVISOR.CREATE_OBJECT(
      TASK_NAME    => TSK_NAME,
      OBJECT_TYPE  => 'INDEX',
      ATTR1        => 'SYS',
      ATTR2        => 'SEG_INDEX',
      ATTR3        => NULL,
      ATTR4        => NULL,
      ATTR5        => NULL,
      OBJECT_ID    => OBJ_ID);

   -- 3. Set the task parameters
   DBMS_ADVISOR.SET_TASK_PARAMETER(
      TASK_NAME    => TSK_NAME,
      PARAMETER    => 'recommend_all',
      VALUE        => 'TRUE');

   -- 4. Execute the task 
   DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
   
END;
/

Verify tasks are completed

SELECT TASK_NAME, 
       STATUS 
FROM   DBA_ADVISOR_TASKS 
WHERE  ADVISOR_NAME='Segment Advisor' AND 
       TASK_NAME IN ('ANALYZE_TABLE_TASK','ANALYZE_INDEX_TASK');

Review segment advisor

Let us check what Oracle segment advisor has to say

SET LINES 300
SET PAGES 999
COL SEGNAME FOR A15
COL PARTITION FOR A10
COL TYPE FOR A10
COL MESSAGE FOR A60
SELECT DAO.ATTR2 SEGNAME, 
       DAO.ATTR3 PARTITION, 
       DAO.TYPE, 
       DAF.MESSAGE 
FROM   DBA_ADVISOR_FINDINGS DAF, 
       DBA_ADVISOR_OBJECTS DAO 
WHERE  DAO.TASK_ID = DAF.TASK_ID AND 
       DAO.OBJECT_ID = DAF.OBJECT_ID AND 
       DAF.TASK_NAME IN ('ANALYZE_TABLE_TASK','ANALYZE_INDEX_TASK');

Mostly the advisor will recommend you to run shrink space command on the table and give an estimate as to how much space can be saved.

Shrink table cascade

Use below command to shrink table but before that you must enable row movement

ALTER TABLE SEG_TABLE ENABLE ROW MOVEMENT;
ALTER TABLE SEG_TABLE SHRINK SPACE CASCADE;
ALTER TABLE SEG_TABLE DISABLE ROW MOVEMENT;

Note: the CASCADE option will shrink space for associated indexes with table. No need to run separate command for index.

Analyze table & index size

Post shrinking table and index, let us gather stats and check segment space savings

ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;

Let us check the size of each segment (table and index)

SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS, 
EXTENTS FROM   DBA_SEGMENTS 
WHERE  SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');

Note: this time the segment space size has been reduced from 797 earlier to 393.

Deallocate space

Now that we have shrink table and index, we can release the deallocated space to the tablespace so that new data can be inserted

ALTER TABLE seg_table DEALLOCATE UNUSED;
ALTER INDEX seg_index DEALLOCATE UNUSED;

At this stage, you can delete the advisor tasks that you created earlier

EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'ANALYZE_TABLE_TASK');
EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'ANALYZE_INDEX_TASK');