Segment Advisor in Oracle

Back to: Oracle DBA Tutorials

Segment Advisor in Oracle with Examples

In this article, I will explain Segment Advisor in Oracle with Examples. Please read our previous article discussing Reclaiming Wasted Space in Oracle with Examples. Oracle doesn’t recommend performing shrink operations every time. Instead, It suggests taking the Segment Advisor suggestion.

Segment Advisor in Oracle

We use the Segment Advisor to identify segments that would benefit from the online segment shrink. But we should not perform directly. Only segments in locally managed tablespaces with automatic segment space management (ASSM) are eligible.

The Segment Advisor can be automatic or manual. The manual segment advisor can be performed by running the PL/SQL block. Running the segment advisor with PL/SQL using the package DBMS_ADVISOR. This package is from Oracle, which is used to take advice from Oracle.

There are four main procedures relevant to the Segment Advisor:

• CREATE TASK
• CREATE OBJECT
• SET_TASK_PARAMETER
• EXECUTE_TASK.

There is no need to get deep into this advisor code because Oracle provides the default code, which is helpful to understand. Small changes in that code will help create a new advisor. These procedures have many features and have overloading. Overloading could have many procedures with the same name inside the package.

So, from the above image, we can see the CREATE_TASK procedure is present inside the package DBMS_ADVISOR. We have different procedures with the same package name but different parameters, and we can use the procedure according to the values.

We will understand the exercise from the next article. Please find the document link provided by Oracle below to get the details about the DBMS_ADVISOR. Link: https://docs.oracle.com/database/121/ARPLS/d_advis.htm#ARPLS350

Segment Advisor and Shrink Exercise

Now, we will be performing an exercise to make you understand the Segment Advisor and Shrink Exercise. This exercise will contain multiple PL/SQL blocks.

Step 1: Connect to the pluggable database

Command: show con_name

Step 2: Drop the tablespace tbsalert and recreate the tablespace.

We have created the tbsalert, including its contents and datafiles because we have performed previous operations in this tablespace. To make the tablespace fresh, let’s drop it and recreate it again.

Statement: drop tablespace tbsalert including contents and datafiles;

So, the tablespace tbsalert is now dropped. Let’s create the tablespace again.

CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/JAYADB/JAYAPDB/tbsalert.dbf'
SIZE 50M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

We have mentioned the logging extent management local and segment space management to auto. In this case, we can take the benefits of the ASSM and use the benefits of shrink and the benefit advisor. As the tablespace is created, let’s look at the free space for the tablespace.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

The free space available for the tablespace is 98%.

Step 3: Create a table and a million records.

In order to check the segment advisor details, let us create a table in the tablespace tbsalert.

create table test100 ( emp_id number, name varchar2(100) ) tablespace TBSALERT;

As the table TEST100 is created. Let’s now try to insert the records into the table. We will be inserting the records using the below PL/SQL block.

begin
 for i in 1..1000000
 loop
 insert into test100 values ( i, 'any dummy name' );
 end loop;
 commit;
 end;

So, the records were inserted into the table. We used the for loop to insert the 1 million records into the table. As the data is inserted into the table, let’s look at the free space in the table space.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

So, the PCT_FREE is 42. Now, if we delete the records from the table TEST100 and commit the table.

delete from test100;
Commit;

The 1 million records have been deleted. Let’s check the PCT_FREE for the tablespace now.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

We can see the PCT_FREE is 42%. This is because we are only removing the data from the blocks, but the blocks are still available for future inserts. These blocks are free and can be used for future inserts. We have a lot of empty space that is not used by any other objects. Oracle recommends we use the Segment Advisor, which helps to reclaim unused space.

Step 4: Create a Segment Advisor Task

Oracle provides a default segment advisor. First, we must create it and then execute the task.

DECLARE
 tname VARCHAR2(128) := 'my_seg_task3';
 tname_desc VARCHAR2(128) := 'Get shrink advice for segments in TBSALERT';
 task_id NUMBER;
 object_id NUMBER;
 objectname VARCHAR2(100);
 objecttype VARCHAR2(100);
 BEGIN
 dbms_advisor.create_task('Segment Advisor', task_id,tname,tname_desc,NULL);---select * from DBA_ADVISOR_DEFINITIONS
 dbms_advisor.create_object(tname,'TABLESPACE','TBSALERT',' ',' ',NULL, ' ',object_id) ;
 dbms_advisor.set_task_parameter(tname,'RECOMMEND_ALL','TRUE');
 END;

From the above PL/SQL block, we can see the tname is task name and we can mention any name. tname_Des is the description of the task. We have other parameters like task_id, object_id, objectname and objecttype.

We have the PL/SQL block with the beginning and end. In the PL/SQL block, we have three tasks. Create_task, Create_object and set_task_parameter.

There are many advisors at the database level. But in our situation, we will be using the Segment_Advisor. In the create_task, there are many parameters like ‘Segment Advisor’ with task_id, tname,tname_Des, NULL).

The Second task is to create the object with the parameters as tname, object_type as tablespace, object name as the tablespace name, and, in the end, the object_id.

The third task is to set the task parameter. The parameter is set_task_parameter. Let’s try to execute the pl/sql block.

So, the procedure is successfully completed.

Step 5: Execute the task.

As the task is now created. Let’s execute the task now.

DECLARE
tname VARCHAR2(128) := 'my_seg_task3';
BEGIN
dbms_advisor.EXECUTE_TASK(tname);
END;

So, the procedure has been successfully completed. Now, there is a select query that will help us find the advisor details of the task that we have executed. We can get it from the data dictionary dba_advisor_tasks.

SELECT DESCRIPTION FROM dba_advisor_tasks WHERE TASK_NAME=’my_seg_task3′;

We can see the tname_des for the task my_seg_task3. The purpose of the query is to check if everything is working fine or not.

Step 6: Get the Segment Recommendation.

In order to get the segment recommendation from Oracle, we have to use the two data dictionaries combined, dab_advisor_findings and dba_advisor_objects.

SELECT attr1, attr2, message
FROM dba_advisor_findings f, dba_advisor_objects o
 WHERE f.task_name = o.task_name 
 AND f.object_id = o.object_id 
 AND f.task_name = 'my_seg_task3';

We can see the advisor’s message stating, “Enable row movement of the table TEST100 and perform shrink. The estimated savings is 24140078 bytes.”

Step 7: Perform the recommendation

Let’s perform the row movement in the table test100.

alter table test100 enable row movement;

So, the table is now altered, and the row movement is now completed. Now, let’s shrink the space.

alter table TEST100 shrink space;

So, the table TEST100 is now altered. Now, let’s look at the free space of the tablespace.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

The PCT_FREE for the tablespace is back to 97%. The records that we deleted left the space back to normal. The segment advisor helps us reclaim unused space, which improves the database’s performance during I/O operations.

In the next article, I will discuss Managing Resumable Space in Oracle with Examples. In this article, I explain Segment Advisor in Oracle with Examples. I hope you enjoy this article on Segment Advisor in Oracle.