Mir Sayeed Hassan – Oracle Blog

Use Segment Advisor in Oracle Database for Space Management Optimization 

Overview

Segment Advisor in Oracle Database helps identify and reclaim unused space in database segments such as tables and indexes. It provides recommendations like segment shrink and reorganization to optimize storage utilization and improve performance. By incorporating Segment Advisor into regular maintenance, DBAs can ensure efficient space management and maintain a high-performing database environment

Frequent updates and deletes in an Oracle Database create fragmented free space within data blocks that is too small for new inserts, leading to inefficient storage utilization and reduced performance.

This unused space is known as fragmented free space, which can negatively impact Oracle Database performance by reducing storage efficiency and slowing data operations

Segment Advisor in Oracle Database is used to identify segments that can benefit from segment shrink, helping improve space utilization and overall performance.

Oracle Segment Advisor can be executed in both automatic and manual modes, enabling flexible and efficient space management optimization.

Before performing a table or index shrink, you should run the Oracle Segment Advisor to analyze segments and estimate how much space can be reclaimed for optimal space management.

SQL> CREATE TABLESPACE segment_advisor

DATAFILE ‘/u01/app/oracle/oradata/mirdb/datafiles/segment_advisor01.dbf’
SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Check the free space in tablespace is 98%. 

SQL> SELECT tablespace_name,
       ROUND(SUM(free_bytes)/SUM(total_bytes)*100,2) AS free_space_pct
FROM (
    SELECT tablespace_name, bytes AS total_bytes, 0 AS free_bytes FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0, bytes FROM dba_free_space
)
WHERE tablespace_name = 'SEGMENT_ADVISOR'
GROUP BY tablespace_name;

TABLESPACE_NAME      FREE BYTES PCT_FREE
———————————————————————————----------------
SEGMENT_ADVISOR 63052324 64049842   98.073

Create a table mir_test1 and some bulk data in mir_test1 table.

SQL> create table mir_test1 (eno number (20), ename varchar2(100))  tablespace segment_advisor;

Insert by using the below method for this table.

begin
for i in 1..1000000
loop
insert into mir_test1 values ( i, 'hassan' ); 
end loop;
commit;
end; 
/ 

Verify the table and its data;

SQL> Select * from mir_test1;

SQL> Select * from mir_test1;

Now check the PCT_FREE is 44%

SQL> SELECT 
    df.tablespace_name AS tablespace,
    ROUND((fs.free_bytes / df.total_bytes) * 100, 2) AS pct_free
FROM 
    (SELECT tablespace_name, SUM(bytes) total_bytes 
     FROM dba_data_files 
     GROUP BY tablespace_name) df
JOIN 
    (SELECT tablespace_name, SUM(bytes) free_bytes 
     FROM dba_free_space 
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE df.tablespace_name = ‘SEGMENT_ADVISOR';

TABLESPACE_NAME      FREE        BYTES      PCT_FREE
——————————————————————————--------------------------—
SEGMENT_ADVISOR     28181930    64049842       44.01

Therefore perform the deletion of the records from the mir_test1 table.

SQL> delete from mir_test1;

SQL> commit;

Verify that space is released or not.

SQL> SELECT 
    segment_name,
    bytes/1024/1024 AS size_mb
FROM 
    dba_segments
WHERE 
    segment_name = ‘MIR_TEST1’;

The space is not reclaimed in an Oracle Database, you can run the Segment Advisor to analyze the segment and receive recommendations on how to recover unused space and optimize storage utilization.

Create a Segment Advisor Task. 

DECLARE
  v_task_name   VARCHAR2(128) := 'MY_SEG_MIR_TEST1';
  v_task_desc   VARCHAR2(128) := 'Segment Advisor for tablespace SEGMENT_ADVISOR';
  v_task_id     NUMBER;
  v_object_id   NUMBER;
BEGIN
  -- Create Segment Advisor task
  DBMS_ADVISOR.CREATE_TASK (
    advisor_name => 'Segment Advisor',
    task_id      => v_task_id,
    task_name    => v_task_name,
    task_desc    => v_task_desc
  );

  -- Add tablespace object for analysis

  DBMS_ADVISOR.CREATE_OBJECT (
    task_name   => v_task_name,
    object_type => 'TABLESPACE',
    attr1       => 'SEGMENT_ADVISOR',
    object_id   => v_object_id
  );

  -- Set parameter to get all recommendations

  DBMS_ADVISOR.SET_TASK_PARAMETER (
    task_name => v_task_name,
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE'
  );
END;
/

Execute this task 

BEGIN
  DBMS_ADVISOR.EXECUTE_TASK (
    task_name => ‘MY_SEG_MIR_TEST1'
  );
END;
/

Take the advisor from Oracle Segment Task Advisor.

SQL> SELECT description  FROM dba_advisor_tasks; 

Find the below method to resolve this space issue and released it from the database server.

Run the below query.

SQ,L> SELECT 
    o.attr1 AS object_name,
    o.attr2 AS object_type,
    f.message
FROM 
    dba_advisor_findings f
JOIN 
    dba_advisor_objects o
ON 
    f.task_name = o.task_name 
    AND f.object_id = o.object_id
WHERE 
    f.task_name = ‘MY_SEG_MIR_TEST1’;

Note: Based on this query it will recommend to shrink the table and release the space.

Enable the Row Movement

SQL> alter table mir_test1 enable row movement;

SQL> alter table mir_test1 shrink space;

Therefore finally check the space is released or not.

SQL> SELECT 
    df.tablespace_name AS tablespace,
    ROUND((fs.free_bytes / df.total_bytes) * 100, 2) AS pct_free
FROM 
    (SELECT tablespace_name, SUM(bytes) total_bytes 
     FROM dba_data_files 
     GROUP BY tablespace_name) df
JOIN 
    (SELECT tablespace_name, SUM(bytes) free_bytes 
     FROM dba_free_space 
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE df.tablespace_name = ‘SEGMENT_ADVISOR';

TABLESPACE_NAME      FREE      BYTES      PCT_FREE
———————————————————————————————------------------—-
SEGMENT_ADVISOR    63052324   64049842     98.073

Space is released from the database server as expected.

=====Hence tested and verified in our test env=====