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

brunors

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

?
1
2
3
4
5
6
7
8
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;
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
     
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.
    • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    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