After Segment Advisor has been invoked to give recommendations, the findings from Segment
Advisor are available in the DBA_ADVISOR_FINDINGS data dictionary view. To show the potential
benefits of shrinking segments when Segment Advisor recommends a shrink operation, the view
DBA_ADVISOR_RECOMMENDATIONS provides the recommended shrink operation along with
the potential savings, in bytes, for the operation.
To set up Segment Advisor to analyze the HR.EMPLOYEES table, we will use an anonymous
PL/SQL block, as follows:
-- begin Segment Advisor analysis for HR.EMPLOYEES
-- rev. 1.1 RJB 07/07/2007
--
-- SQL*Plus variable to retrieve the task number from Segment Advisor
variable task_id number
-- PL/SQL block follows
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name := ''; -- unique name generated from create_task
descr := 'Check HR.EMPLOYEE table';
dbms_advisor.create_task
('Segment Advisor', :task_id, name, descr, NULL);
dbms_advisor.create_object
(name, 'TABLE', 'HR', 'EMPLOYEES', NULL, NULL, obj_id);
186 Oracle Database 11g DBA Handbook
dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.execute_task(name);
end;
PL/SQL procedure successfully completed.
SQL> print task_id
TASK_ID
----------
384
SQL>
The procedure DBMS_ADVISOR.CREATE_TASK specifies the type of advisor; in this case, it is
Segment Advisor. The procedure will return a unique task ID and an automatically generated
name to the calling program; we will assign our own description to the task.
Pages:
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339