Undo Advisor

SQL> select * from dba_advisor_Definitions;

ADVISOR_ID ADVISOR_NAME PROPERTY
---------- ------------------------------ ----------
1 ADDM 1
2 SQL Access Advisor 15
3 Undo Advisor 1
4 SQL Tuning Advisor 7
5 Segment Advisor 3
6 SQL Workload Manager 0
7 Tune MView 31

Undo Advisor:-

Provides the Recommendation for undo configurations.

Steps
1. Create Task
2. Create Object
3. Set Task Parameters 
4. Execute Task
5. Report Task.

SQL> begin
dbms_advisor.create_task
(
advisor_name => 'Undo Advisor',
task_name => 'Get_undo_advice'
);
end;
/

SQL> declare
task_id number;
begin
dbms_advisor.create_object
(
TASK_NAME => 'Get_undo_advice',
OBJECT_TYPE => 'UNDO_TBS',
ATTR1 => NULL,
ATTR2 => NULL,
ATTR3 => NULL,
ATTR4 => 'null',
ATTR5 => NULL,
OBJECT_ID => task_id
);
dbms_advisor.set_task_parameter
(
TASK_NAME => 'Get_undo_advice',
PARAMETER => 'TARGET_OBJECTS',
VALUE => task_id
);
dbms_advisor.set_Task_parameter
(
task_name => 'Get_undo_advice',
parameter => 'START_SNAPSHOT',
VALUE => 70
);
dbms_advisor.set_Task_parameter
(
task_name => 'Get_undo_advice',
parameter => 'END_SNAPSHOT',
VALUE => 74
);
end;
/

SQL> exec dbms_advisor.execute_task('Get_undo_advice');

PL/SQL procedure successfully completed.


Get the Results from dba_advisor_findings, dba_advisor_action , dba_advisor_recommendations.

SQl>SELECT * FROM DBA_ADVISOR_FINDINGS WHERE TASK_NAME = 'Get_undo_advice';