Segment Advisor dump (R 10.1)

  • Published on May 17 2011
  • # 83
  • oracle

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

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>