• 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-table
create-table


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

2,024 views

Recent Posts

See All

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i

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

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose