Using Segment Space Advisor for segment space management and tuning

Create an Adisor Task

DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'USER_LOG Advice';
my_task_desc := 'Manual Segment Advisor Run For User Log Table';
---------
-- Step 1
---------
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object (
task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'FARHAT',
attr2 => 'USER_LOG',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/

PL/SQL procedure successfully completed.

**************************************************************************************

SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

*************************************************************************************

SELECT
'Task Name : ' || f.task_name || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info TASK_ADVICE
FROM dba_advisor_findings f
,dba_advisor_objects o
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_name like 'USER_LOG Advice'
ORDER BY f.task_name;

------------------------------------------------------------------------------------------
If the table has a potential issue with row chaining, then the advice output will indicate it as follows:
TASK_ADVICE
--------------------------------------------------------------------------------
Task Name : F_REGS Advice
Segment Name : F_REGS
Segment Type : TABLE
Partition Name :
Message : Perform re-org on the object F_REGS, estimated savings is 18209960 bytes.
More Info : Allocated Space:20971520: Used Space:2761560: Reclaimable Space :18209960:

---------------------------------------------------------------------------------------

SQL> ALTER TABLE FARHAT.USER_LOG ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE FARHAT.USER_LOG SHRINK SPACE ;

Table altered.

SQL>

***************************************************************************************
You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via the COMPACT clause

SQL> ALTER TABLE FARHAT.USER_LOG SHRINK SPACE COMPACT ;

How It Works

The DBMS_ADVISOR package is used to manually instruct the Segment Advisor to generate advice for
specific tables. This package contains several procedures that perform operations such as creating and
executing a task. Table 1-6 lists the procedures relevant to the Segment Advisor.

Procedure Name Description
CREATE_TASK Creates the Segment Advisor task; specify “Segment Advisor” for the
ADVISOR_NAME parameter of CREATE_TASK. Query DBA_ADVISOR_DEFINITIONS for a list of all valid advisors.
CREATE_OBJECT Identifies the target object for the segment advice; Table 1-7 lists valid object types and parameters.
SET_TASK_PARAMETER Specifies the type of advice you want to receive; Table 1-8 lists valid parameters and values.
EXECUTE_TASK Executes the Segment Advisor task
DELETE_TASK Deletes a task
CANCEL_TASK Cancels a currently running task

The Segment Advisor can be invoked with various degrees of granularity. For example, you can generate advice for all objects in a tablespace or advice for a specific table, index, or partition. Table below lists the object types for which Segment Advisor advice can be obtained via the
DBMS_ADVISOR.CREATE_TASK procedure.

You can also specify a maximum amount of time that you want the Segment Advisor to run. This is controlled via the SET_TASK_PARAMETER procedure. This procedure also controls the type of advice that is generated. Table below describes valid inputs for this procedure.

Input Parameters for the DBMS_ADVISOR.SET_TASK_PARAMETER Procedure