• Arun Kumar

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 Oracle segment advisor:


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-tablespace

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;

create-table

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

3-insert-records

Create index on table

CREATE INDEX seg_index ON seg_table(TEXT) tablespace seg_tbs;

4-create-index

Analyze table & index


Gather table and index stats

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

5-computer-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');

6-check-segment-size

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;

7-delete-records

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

8-check-segment-size

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

advisor-output

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.

10-table-shrink-space

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.

11-check-segment-size

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;

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

13-delete-advisor-tasks

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback