Abordagem simples da execução do Segment Advisor sobre um segmento do tipo tabela

Executar o Segment Advisor pode ser útil em situações em que há a necessidade de Tuning de segmento, conforme abaixo reportado pelo ADDM.

Recommendation 1: Segment Tuning
Estimated benefit is .24 active sessions, 24% of total activity.
----------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "BRUNO_DATA.TECH_DATABASKET" with object ID
164493.
Related Object
Database object with ID 164493.

 

Com isso, neste post estarei abordando de maneira simples a execução do mesmo.

O procedimento consiste nas seguintes etapas:

    Execução da dbms_advisor com os seguintes parâmetros create_task, create_object, set_task_parameter e execute_task;
variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='BRUNO_DATA_check';
  descr:='Segment Advisor Example';

  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_id          => :id,
    task_name        => name,
    task_desc        => descr);

  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLE',
    attr1            => 'BRUNO_DATA',
    attr2            => 'TECH_DATABASKET',
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

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

  dbms_advisor.execute_task(name);
  end;
end; 
/

*A variável name é o nome da task.



 	
Verificar a recomendação de ação na view DBA_ADVISOR_RECOMMENDATIONS.



SQL> set pages 2000
SQL> set lines 2000
SQL> set long 9999
SQL> select * from DBA_ADVISOR_RECOMMENDATIONS where TASK_NAME='BRUNO_DATA_check';

OWNER                              REC_ID    TASK_ID TASK_NAME                      EXECUTION_NAME                 FINDING_ID TYPE                                 RANK
------------------------------ ---------- ---------- ------------------------------ ------------------------------ ---------- ------------------------------ ----------
PARENT_REC_IDS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BENEFIT_TYPE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BENEFIT ANNOTATION_      FLAGS F
---------- ----------- ---------- -
SYS                                     1      40869 BRUNO_DATA_check                                                  1

Enable row movement of the table BRUNO_DATA.TECH_DATABASKET and perform shrink, estimated savings is 2983436642 bytes.
N
  • Verificar a ação a ser executada na view DBA_ADVISOR_ACTIONS.
  • SQL> set pages 2000
    SQL> set lines 2000
    SQL> select * from DBA_ADVISOR_ACTIONS where TASK_NAME='BRUNO_DATA_check';
    
    OWNER                             TASK_ID TASK_NAME                      EXECUTION_NAME                     REC_ID  ACTION_ID  OBJECT_ID COMMAND                                                          COMMAND_ID      FLAGS
    ------------------------------ ---------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------------------------------------------------------------- ---------- ----------
    ATTR1
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ATTR2
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ATTR3
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ATTR4
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ATTR5                                                                            ATTR6                                                                             NUM_ATTR1  NUM_ATTR2  NUM_ATTR3  NUM_ATTR4  NUM_ATTR5
    -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
    MESSAGE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    F
    -
    SYS                                 40869 BRUNO_DATA_check                                               1          1          1 SHRINK SPACE                                                             36
    alter table "BRUNO_DATA.TECH_DATABASKET" shrink space
    alter table "BRUNO_DATA.TECH_DATABASKET" shrink space COMPACT
    alter table "BRUNO_DATA.TECH_DATABASKET" enable row movement
    
    
    
    N
    

    Related posts

    Leave a Comment