Using DBMS_ADVISOR to advice about UNDO tablespace from AWR

declare
task_id number;
name varchar2(100);
descr varchar2(100);
obj_id number;
-- get the min and max snap id from AWR
CURSOR l_cur IS
SELECT min_snap_id, begin_interval_time, max_snap_id, end_interval_time
FROM dba_hist_snapshot d,
(select min(snap_id) as min_snap_id, max(snap_id) max_snap_id FROM dba_hist_snapshot) minmaxsnap
WHERE d.snap_id = minmaxsnap.min_snap_id
or d.snap_id = minmaxsnap.max_snap_id
order by begin_interval_time;
l_min_snap l_cur%ROWTYPE;
l_max_snap l_cur%ROWTYPE;
-- Get advisor information
CURSOR l_undo_advisor(p_task_id IN dba_advisor_findings.task_id%TYPE) IS
select type, message, more_info from dba_advisor_findings where task_id = p_task_id;
l_rec_advisor l_undo_advisor%ROWTYPE;
begin
open l_cur;
fetch l_cur INTO l_min_snap;
fetch l_cur INTO l_max_snap;
close l_cur;
-- name of advisor task
name := 'UNDO_TABLESPACE_' || l_min_snap.min_snap_id || '_' || l_max_snap.max_snap_id;
descr := 'Check Undo Tablespace';
dbms_advisor.create_task('Undo Advisor', task_id, name, descr);
dbms_advisor.create_object(name, 'UNDO_TBS', NULL, NULL, NULL, 'null', obj_id);
-- Set parameters
dbms_advisor.set_task_parameter(name, 'TARGET_OBJECTS', obj_id);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 106);
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 423);
dbms_advisor.set_task_parameter(name, 'INSTANCE', 1);
dbms_output.put_line('Task Name: ' || name);
dbms_output.put_line('Task Id: ' || task_id);
dbms_advisor.execute_task(name);
open l_undo_advisor(task_id);
fetch l_undo_advisor INTO l_rec_advisor;
IF l_undo_advisor%FOUND THEN
dbms_output.put_line('Type: ' || l_rec_advisor.type);
dbms_output.put_line('Message: ' || l_rec_advisor.message);
dbms_output.put_line('More Info: ' || l_rec_advisor.more_info);
ELSE
dbms_output.put_line('No Information found');
END IF;
close l_undo_advisor;
dbms_advisor.delete_task(name);
end;
/

Output:
Task Name: UNDO_TABLESPACE_106_426
Task Id: 466
Type: PROBLEM
Message: The undo tablespace is OK.
More Info:

One thought on “Using DBMS_ADVISOR to advice about UNDO tablespace from AWR

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s