Manually Generating Segment Advisor Advice

Manually Generating Segment Advisor Advice

Problem
You have a table that experiences a large amount of updates. You have noticed that the query
performance against this table has slowed down. You suspect the table may be experiencing poor
performance due to row chaining. Therefore you want to manually confirm with the Segment Advisor
that a table has issues with row chaining.

Solution
You can manually run the Segment Advisor and tell it to specifically analyze all segments in a tablespace
or look at a specific object (such as a single table or index). You can manually generate advice for a
specific segment using the DBMS_ADVISOR package by executing the following steps:
1. Create a task.
2. Assign an object to the task.
3. Set the task parameters.
4. Execute the task.
■ Note The database user executing DBMS_ADVISOR needs the ADVISOR system privilege. This privilege is
administered via the GRANT statement.
The following example executes the DBMS_ADVISOR package from an anonymous block of PL/SQL.
The table being examined is the F_REGS table.
DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'F_REGS Advice';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- 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 => 'MV_MAINT',
attr2 => 'F_REGS',
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;
/
Now you can view Segment Advisor advice regarding this table by executing the DBMS_SPACE package
and instructing it to pull information from a manual execution of the Segment Advisor (via the input
parameters—see Table 1-6 for details)—for example:
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'));

Here is some sample output:
Segment Advice --------------------------
TABLESPACE_NAME : USERS
SEGMENT_OWNER : MV_MAINT
SEGMENT_NAME : F_REGS
ALLOCATED_SPACE : 20971520
RECLAIMABLE_SPACE: 18209960
RECOMMENDATIONS : Perform re-org on the object F_REGS, estimated savings is 182
09960 bytes.
SOLUTION 1 : Perform Reorg
SOLUTION 2 :
SOLUTION 3 :
You can also retrieve Segment Advisor advice by querying data dictionary views—for example:
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 'F_REGS 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 182
09960 bytes.
More Info : Allocated Space:20971520: Used Space:2761560: Reclaimable Spa
ce :18209960:
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 1-7
lists the object types for which Segment Advisor advice can be obtained via the
DBMS_ADVISOR.CREATE_TASK procedure.

Object Type ATTR1 ATTR2 ATTR3 ATTR4
TABLESPACE tablespace name NULL NULL NULL
TABLE user name table name NULL NULL
INDEX user name index name NULL NULL
TABLE PARTITION user name table name partition name NULL
INDEX PARTITION user name index name partition name NULL
TABLE SUBPARTITION user name table name subpartition name NULL
INDEX SUBPARTITION user name index name subpartition name NULL
LOB user name segment name NULL NULL

Object Type ATTR1 ATTR2 ATTR3 ATTR4
LOB PARTITION user name segment name partition name NULL
LOB SUBPARTITION user name segment name subpartition name NULL
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 1-8 describes valid inputs for this procedure.
Table 1-8. Input Parameters for the DBMS_ADVISOR.SET_TASK_PARAMETER Procedure
Parameter Description Valid Values
TIME_LIMIT Limit on time (in seconds) for advisor run N number of seconds or UNLIMITED
(default)
RECOMMEND_ALL Generates advice for all types of advice or
just space-related advice
TRUE (default) for all types of advice, or
FALSE to generate only space-related
advice