Segment Advisor In Oracle 11g

STEPS FOR CREATING SEGMENT ADVISOR REPORT IN ORACLE 11g:-

SCHEMA NAME:- ORCL
SEGMENT NAME:- SALES
TABLESPACE NAME:- ORCL_TBS

1> Create a segment advisor task for the ORCL.SALES table.

DECLARE
l_object_id NUMBER;
BEGIN
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'SALES_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For SALES TABLE');
DBMS_ADVISOR.create_object (
task_name => 'SALES_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'ORCL',
attr2 => 'SALES',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'SALES_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');


DBMS_ADVISOR.execute_task(task_name => 'SALES_SEGMENT_ADVISOR');




2> Create a segment advisor task for the ORCL_TBS tablespace.



DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For ORCL_TBS');
DBMS_ADVISOR.create_object (
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
object_type => 'TABLESPACE',
attr1 => 'ORCL_TBS',
attr2 => NULL,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'ORCL_TBS_SEGMENT_ADVISOR');
END;
/





3> Display the Segment Advisor Report:-

SET LINESIZE 600
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40
set pagesize 500
SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('SALES_SEGMENT_ADVISOR', 'ORCL_TBS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;