Segment Advisor dump (R 10.1)

Dump of running segment advisor in Oracle 10.1

@See:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_advis.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592
http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php /

For tablespace

create or replace procedure sa_tablespace
(
  piv_tablespace in varchar2,
  piv_task         in varchar2 default null
)
  authid current_user

as

  obj_id number;
  task   varchar2(100);

begin

    if piv_task is null then
      task := 'SA TABLESPACE ' || piv_tablespace ;
    else
      task := piv_task;
    end if;

    -- remove task
    begin
        dbms_advisor.delete_task ( task_name     => task);
    exception
        when others then null ;
    end;

    -- create task
  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_name     => task);

  dbms_advisor.create_object (
    task_name        => task,
    object_type      => 'TABLESPACE',
    attr1            => piv_tablespace,
    attr2            => NULL,
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

  dbms_advisor.set_task_parameter(
    task_name        => task,
    parameter        => 'recommend_all',
    value            => 'TRUE');

  dbms_advisor.execute_task(task);

end;
/
exec sa_tablespace( 'P1TS001');
exec sa_tablespace( 'P1TS002');

For user object

create or replace procedure sa_object
(
  piv_object_owner      in varchar2,
  piv_object_name   in varchar2,
  piv_object_type       in varchar2,
  piv_task      in varchar2 default null
)
  authid current_user
as
  obj_id number;
  task   varchar2(100);
begin

    if piv_task is null then
      task := 'SA OBJECT ' || piv_object_type || '-' || piv_object_owner || '-' || piv_object_name;
    else
      task := piv_task;
    end if;

    dbms_advisor.create_task (
      advisor_name     => 'Segment Advisor',
      task_name     => task);

    dbms_advisor.create_object (
      task_name        => task,
      object_type      => piv_object_type,
      attr1            => piv_object_owner,
      attr2            => piv_object_name,
      attr3            => NULL,
      attr4            => NULL,
      attr5            => NULL,
      object_id        => obj_id);

    dbms_advisor.set_task_parameter(
      task_name        => task,
      parameter        => 'recommend_all',
      value            => 'TRUE');

     dbms_advisor.execute_task(task);
end;
/
exec sa_object( 'P1BRID', 'DEMO', 'TABLE', 'SA_1');
exec sa_object( 'P1BRID', 'DEMO', 'TABLE', 'SA 1');


-- Display the findings.
SET LINESIZE 250
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

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 ('SA_1', 'SA 1') or f.task_name like 'SA T%'
ORDER BY f.task_name, f.impact DESC;

--select * from DBA_ADVISOR_FINDINGS where task_name='SA_1'  ;
--select * from DBA_ADVISOR_FINDINGS where task_name='SA 1'  ;

Leave a Reply

Your email address will not be published. Required fields are marked *