Segment Advisor via SQL

Em um database, devido inumeras operações DML aplicadas sobre tabelas estas começam a se tornar fragmentadas assim como os indices, impactando no tamanho e performance da base de dados.

Normalmente o Segment Advisor é executado via EM (Enterprise Manager), porem podemos executa-lo via SQL atraves de algumas etapas.

Primeiramente vamos entender os procedimentos e pacotes utilizados:

CREATE_TASK: Cria uma tarefa para o Segment Advisor. Podemos especificar “Segment Advisor”, como o valor do parâmetro ADVISOR_NAME.

CREATE_OBJECT: Cria a indentificação para o objeto-alvo que sera analisado pelo Segment Advisor. Os valores dos parâmetros deste processo depende do tipo de objeto. Na tabela “Entrada para DBMS_ADVISOR.CREATE_OBJECT” abaixo, estão os valores dos parâmetros para cada tipo de objeto.

SET_TASK_PARAMETER: Parâmetros de entrada do processo, estão listados na tabela “Entrada para DBMS_ADVISOR.SET_TASK_PARAMETER”.

EXECUTE_TASK: Executa a tarefa para análise do Segment Advisor. Entradas para DBMS_ADVISOR.CREATE_OBJECT

|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| OBJECT_TYPE                  | ATTR1                  | ATTR2                         | ATTR3                         | ATTR4                      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| TABLESPACE                   | tablespace name        | NULL                          | NULL                          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| TABLE                        | schema name            | table name                    | NULL                          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| INDEX                        | schema name            | index name                    | NULL                          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| TABLE PARTITION              | schema name            | table name                    | table partition name          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| INDEX PARTITION              | schema name            | index name                    | index partition name          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| TABLE SUBPARTITION           | schema name            | table name                    | table subpartition name       | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| INDEX SUBPARTITION           | schema name            | index name                    | index subpartition name       | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| LOB                          | schema name            | segment name                  | NULL                          | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| LOB PARTITION                | schema name            | segment name                  | lob partition name            | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|
| LOB SUBPARTITION             | schema name            | segment name                  | lob subpartition name         | Unused. Specify NULL.      |
|------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------|

Entrada para DBMS_ADVISOR.SET_TASK_PARAMETER

|------------------------------|----------------------------------------------|-----------------------------------------------------|----------------|
| Input Parameter              | Description                                  | Possible Values                                     | Default Value  |
|------------------------------|----------------------------------------------|-----------------------------------------------------|----------------|
| time_limit                   | The time limit for the Segment Advisor run,  | Any number of seconds                               | UNLIMITED      |
|                              | specified in seconds.                        |                                                     |                |
|------------------------------|----------------------------------------------|-----------------------------------------------------|----------------|
|                              |                                              | TRUE: Findings are generated on all segments        |                |
|                              |                                              | specified, whether or not space reclamation is      |                |
| recommend_all                | Whether the Segment Advisor should generate  | recommended.                                        | TRUE           |
|                              | findings for all segments.                   | FALSE: Findings are generated only for those        |                |
|                              |                                              | objects that generate recommendations for space     |                |
|                              |                                              | reclamation.                                        |                |
|------------------------------|----------------------------------------------|-----------------------------------------------------|----------------|

Exemplo – Segment Advisor ( TABLESPACE )

variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='tablespace_imagem';
  descr:='Segment Advisor Tablespace';
 
  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      => 'TABLESPACE',
    attr1            => 'IMAGEM',
    attr2            => NULL,
    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; 
/

Verificar o status da tarefa

select task_name, status from dba_advisor_tasks where advisor_name = 'Segment Advisor';
 
TASK_NAME                      STATUS
------------------------------ -----------
tablespace_users               COMPLETED

Resultado da análise do Segment Advisor

set lines 200
col PARTITION for a20
col SEGNAME for a20
col SEGNAME for a20
col MESSAGE for a60
col TYPE for a20
 
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id
  and af.task_name = 'tablespace_imagem';
 
TASK_NAME                SEGNAME              PARTITION      TYPE         MESSAGE
------------------------ -------------------- -------------- ------------ ------------------------------------------------------------
tablespace_users         TESTE_ATUAL                         TABLE        Ative a movimentacao de linha da tabela SYS.TESTE_ATUAL e fa
                                                                          ca uma compactacao; a economia estimada e de 43172584 bytes.
 
tablespace_users         LOGADOS                             TABLE        O espaco livre nos objetos e menor que 10MB.
tablespace_users         TESTE                               TABLE        O espaco livre nos objetos e menor que 10MB.
tablespace_users         PK_NOME                             INDEX        O espaco livre nos objetos e menor que 10MB.
tablespace_users         PK_DEPT                             INDEX        O espaco livre nos objetos e menor que 10MB.
tablespace_users         PK_EMP                              INDEX        O espaco livre nos objetos e menor que 10MB.
 
43172584/1024/1024 = 41MB

Recomendações – DBMS_SPACE.ASA_RECOMMENDATIONS

set lines 200
col TABLESPACE_NAME for a15
col SEGMENT_NAME for a15
col SEGMENT_TYPE for a15
col PARTITION_NAME for a15
col RECOMMENDATIONS for a50
 
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
 
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME  RECOMMENDATIONS                          C1
--------------- ------------ ------------ --------------  ---------------------------------------  --------------------------------------------------
USERS           TESTE_ATUAL  TABLE                        Ative a movimentacao de linha da tabela  alter table "SYS"."TESTE_ATUAL" shrink space
                                                          SYS.TESTE_ATUAL e faca uma compactacao;
                                                          a economia estimada e de 43172584 bytes.

Removendo a tarefa

exec dbms_advisor.delete_task('tablespace_users');