DBMS_ADVISOR – SHRINK SPACE e ALTER TABLE
Imagine um carro de Fórmula 1 em uma corrida de alta velocidade. Para manter o carro em seu desempenho máximo, é essencial que ele esteja livre de peso extra e com todos os seus componentes funcionando perfeitamente. Qualquer excesso de peso ou ineficiência pode custar preciosos milésimos de segundo, que podem ser a diferença entre a vitória e a derrota.
Da mesma forma, em um ambiente de banco de dados Oracle, a eficiência na utilização do espaço é crucial para garantir um desempenho ideal e uma gestão econômica dos recursos de armazenamento. A má alocação de espaço pode levar ao surgimento de áreas não utilizadas, que, se não forem geridas corretamente, podem resultar em desperdício de recursos e impacto negativo no desempenho do sistema.
Para ajudar os DBAs a manterem seus bancos de dados tão eficientes quanto um carro de Fórmula 1, o Oracle Database oferece o DBMS_ADVISOR. Este pacote PL/SQL atua como um mecânico de corrida, fornecendo uma interface para diversos conselheiros do Oracle, incluindo o Segment Advisor. Este conselheiro é projetado especificamente para analisar e recomendar ações para recuperar espaço não utilizado em segmentos de dados, garantindo que o banco de dados continue funcionando em alta velocidade sem desperdício de recursos.
Neste artigo, exploraremos como utilizar o DBMS_ADVISOR para identificar e recuperar espaço não utilizado dentro de um banco de dados Oracle. Discutiremos os passos necessários para configurar e executar o Segment Advisor, interpretar os resultados fornecidos e implementar as recomendações para otimizar o uso de espaço. Através de exemplos práticos e melhores práticas, mostraremos como esta poderosa ferramenta pode ser integrada ao seu fluxo de trabalho de DBA, mantendo seu banco de dados tão eficiente quanto um carro de Fórmula 1 em sua melhor forma.
Reforçando o conceito de desfragmentação e liberação de espaço, volto a enfatizar a importância da prática. A questão de Tuning e Performance é um tema que nunca sai de moda, esteja seu banco rodando na nuvem ou on-premises.
A vamos a prática.
CREATE TABLESPACE TBS_DBMS DATAFILE SIZE 500M;
SET LINES 400
COL FILE_NAME FOR A50
SELECT FILE_NAME, BYTES/1024/1024 "TAMANHO MB", AUTOEXTENSIBLE FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='TBS_DBMS';
CREATE USER USR_AGUA
IDENTIFIED BY senha
DEFAULT TABLESPACE TBS_DBMS
QUOTA 400M ON TBS_DBMS;
GRANT CONNECT TO USR_AGUA;
GRANT RESOURCE TO USR_AGUA;
GRANT CREATE SESSION TO USR_AGUA ;
GRANT CREATE TABLE TO USR_AGUA;
GRANT CREATE ANY TABLE TO USR_AGUA;
GRANT CREATE TABLESPACE TO USR_AGUA;
GRANT INSERT ANY TABLE TO USR_AGUA;
GRANT ALTER TABLESPACE TO USR_AGUA;
GRANT DROP TABLESPACE TO USR_AGUA;
GRANT ADVISOR TO USR_AGUA;Observem que, como usuário SYS, estou no PDB “ORCLPDB”. Criei um novo usuário com o nome de “USR_AGUA” e concedi os devidos privilégios a ele. Em seguida, conectei-me ao banco de dados com o usuário USR_AGUA.
A primeira ação do usuário foi criar uma tabela. Confirmei que essa tabela está vazia, sem nenhuma linha.
CREATE TABLE TBL_ADSISOR (
CODIGO NUMBER(2),
DESCRICAO VARCHAR2(10)
)
TABLESPACE TBS_DBMS;O próximo passo é simular o uso cotidiano do banco de dados, inserindo e deletando dados. A cada inserção e deleção, surgem fragmentos e pequenos espaços não utilizados dentro da tabela.
Como estamos inserindo e deletando grandes volumes de dados em um loop contínuo, isso cria fragmentos e espaços não utilizados, como uma marca d’água. A reorganização desses dados, desalocando-os de seus locais atuais, só pode ser feita através de uma desfragmentação. É exatamente esse ambiente que vamos criar, repetindo a imagem anterior várias vezes.
Veja que deletei dados e, na sequência, inseri uma quantidade diferente. Isso se torna uma rotina, inserindo e retirando dados, gerando espaços vagos na tabela. Pense no seguinte: quando inserimos 100.000 registros no início, foi alocado um espaço significativo na tabela. Conforme as quantidades foram mudando, com entradas e saídas de linhas, a tabela foi acumulando espaços vazios.
O que estou tentando simular aqui é um ambiente real, do dia a dia. Agora, no final, a tabela tem apenas 10 registros. E o que aconteceu com aquele espaço todo que foi inicialmente alocado? Ele foi ficando fragmentado, com pequenos espaços vazios entre inserções e deleções.
O próximo passo será eliminar esses espaços fragmentados e desfragmentar todo o espaço vazio entre um data block e outro. Todo esse esforço é para melhorar a performance: menos espaços vagos significam mais espaço livre para futuras inserções e mais agilidade para o banco de dados, que gastará menos tempo procurando onde não há nada.
O código a seguir é um bloco PL/SQL que demonstra como criar e executar uma tarefa de Segment Advisor usando o pacote DBMS_ADVISOR no Oracle Database:
VARIABLE id NUMBER;
BEGIN
DECLARE
name VARCHAR2(100);
descr VARCHAR2(500);
obj_id NUMBER;
BEGIN
name := 'EXERCICIO_DBMS_1';
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 => 'USR_AGUA',
attr2 => 'TBL_ADSISOR',
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;
/
Neste exemplo, o bloco PL/SQL realiza as seguintes operações:
1. Declaração de Variáveis: Define variáveis para armazenar o nome da tarefa, a descrição e o ID do objeto.
2. Criação da Tarefa: Utiliza o procedimento `create_task` do pacote `DBMS_ADVISOR` para criar uma nova tarefa de Segment Advisor.
3. Criação do Objeto: Usa o procedimento `create_object` para definir o objeto (uma tabela) que será analisado pela tarefa.
4. Configuração dos Parâmetros da Tarefa**: Configura os parâmetros da tarefa usando `set_task_parameter`.
5. Execução da Tarefa: Executa a tarefa com `execute_task`.
Este bloco é um exemplo prático de como utilizar o Segment Advisor para analisar e otimizar o uso de espaço de tabelas no Oracle Database.
Pense que cada carro representa uma tarefa de análise no banco de dados Oracle, e o pit stop é o Segment Advisor. A consulta SQL abaixo é como uma lista de verificação usada pelo chefe de equipe do pit stop para monitorar o status dos carros (ou seja, tarefas) que estão sob sua responsabilidade: Este código ajuda a garantir que cada tarefa de análise está sendo monitorada e executada corretamente, permitindo ajustes rápidos e eficientes conforme necessário.
select task_name, status from dba_advisor_tasks
where owner = 'USR_AGUA' and advisor_name = 'Segment Advisor';Análise e Ajustes com o Segment Advisor
- advisor_name: Este campo representa o tipo de ajuste que está sendo feito, semelhante a verificar se o carro está configurado para um tipo específico de pista. Neste caso, estamos interessados apenas nos carros que estão sendo ajustados para a estratégia de corrida do “Segment Advisor”.
A consulta SQL fornece um relatório de todas as tarefas que o usuário USR_AGUA está monitorando para ajustes específicos do “Segment Advisor”. Este relatório mostra o nome de cada tarefa e seu status atual, permitindo que a equipe do pit stop saiba exatamente quais tarefas estão prontas, quais precisam de mais ajustes e quais estão aguardando na fila.
Este comando SQL ajuda a monitorar e gerenciar o desempenho das tarefas de análise, garantindo que o banco de dados esteja “pronto para a corrida” e otimizado para as melhores condições de performance:
Seguindo com a nossa análise, devemos ter em mente que cada tarefa é como uma revisão de um carro no pit stop, e você precisa de um relatório detalhado para entender quais ajustes são necessários. A consulta SQL que você executou fornece um relatório detalhado sobre as análises realizadas pelo Segment Advisor, mostrando quais partes do banco de dados precisam de atenção.
Detalhamento das Colunas do Relatório
Vamos detalhar o que cada coluna do resultado significa, usando a analogia do pit stop:
- task_name: Similar ao nome do carro ou número de identificação. Isso ajuda a identificar qual tarefa específica está sendo analisada ou ajustada.
- status: Representa o estado atual da tarefa, como se um carro está pronto para correr, em ajustes, ou ainda na fila de espera. Status comuns incluem:
- COMPLETED: Tarefa concluída, semelhante a um carro pronto para voltar à corrida.
- IN_PROGRESS: Tarefa em andamento, como um carro que ainda está no pit stop sendo ajustado.
- FAILED: Tarefa que falhou, necessitando de revisão ou intervenção adicional.
Este relatório detalhado permite que a equipe do pit stop (ou os DBAs) saiba exatamente o que precisa ser feito para otimizar o banco de dados. Ele ajuda a identificar áreas que precisam de atenção, assegurando que todos os recursos estão sendo usados de forma eficiente.
Implementando as recomendações do Segment Advisor, você garante que seu banco de dados Oracle esteja operando com a máxima eficiência, assim como um carro de Fórmula 1 que foi ajustado para obter o melhor desempenho na pista.
SELECT af.task_name,
ao.attr2 AS segname,
ao.attr3 AS 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 ao.owner = 'USR_AGUA';Componentes do Relatório:
- TASK_NAME: EXERCICIO_DBMS_1: É como identificar qual carro está sendo revisado ou qual tarefa de ajuste está em andamento.
- SEGNAME: TBL_ADSISOR: Aqui, representa o objeto de banco de dados, similar a uma peça do carro que está sendo checada para possíveis melhorias.
- PARTITION: (em branco) Em nosso caso, não há partição específica mencionada, o que pode significar que a análise é geral para o objeto.
- TYPE: TABLE Aqui, é como identificar que o ajuste é para o “motor” do carro, que neste caso é uma tabela do banco de dados.
- MESSAGE: The free space in the object is less than 10MB. A mensagem de revisão que indica o problema encontrado. No pit stop, isso seria como a equipe informando que o nível de combustível está baixo e precisa ser reabastecido. Aqui, a mensagem indica que o espaço livre na tabela é menor que 10MB e precisa ser gerenciado para evitar problemas futuros.
Resumo, este relatório é semelhante a um check-list do pit stop, fornecendo detalhes sobre quais “carros” (ou tabelas) precisam de manutenção. Ele ajuda a identificar rapidamente quais ajustes são necessários para garantir que o banco de dados (ou os carros) esteja operando de maneira eficiente e sem problemas.
Seguindo com a nossa análise, pense na tabela TBL_ADSISOR como um carro de F1. O “espaço” na tabela é como o tanque de combustível do carro. O que queremos fazer é verificar quanto combustível (ou espaço) foi usado e quanto ainda está disponível para garantir que o carro (ou a tabela) tenha o desempenho adequado durante a corrida.
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('USR_AGUA', 'TBL_ADSISOR', 'TABLE',
v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks,
v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/Visualizando e Entendendo a Fragmentação do Banco de Dados
Para ajudar na compreensão da fragmentação no banco de dados, utilizamos uma analogia com o combustível em uma corrida de Fórmula 1. Isso facilita a visualização e o entendimento do gerenciamento do espaço na tabela.
Analogia com Combustível e Espaço
1. Combustível Não Utilizado (Blocos e Bytes Não Formatados):
- Analogia: Representa o combustível que ainda não foi utilizado no carro, ou seja, o espaço na tabela que ainda não foi formatado para uso.
- Explicação: Assim como o combustível não utilizado pode ser um recurso inexplorado no carro, o espaço não formatado em uma tabela ainda não está disponível para operações de banco de dados.
2. nos Tanques FS1 a FS4:
- Analogia: Representa o combustível que está em diferentes tanques ou reservas (FS1, FS2, FS3, FS4). Cada tanque pode ter uma quantidade diferente de combustível.
- Explicação: Semelhante aos tanques de combustível no carro, cada segmento de espaço em uma tabela pode ter diferentes quantidades de espaço alocado, variando conforme o uso e a fragmentação.
3. Combustível Completo (Blocos e Bytes):
- Analogia: Reflete o combustível totalmente preparado e pronto para uso. Isso indica o espaço que está totalmente formatado e disponível para uso.
- Explicação: Assim como o combustível completo é essencial para o desempenho ideal do carro, o espaço totalmente formatado e disponível é crucial para a eficiência do banco de dados.
Recuperando o Espaço: Prática
Com toda essa visão sobre a gestão de espaço e a analogia com o combustível, é hora de ver na prática como recuperar o espaço utilizado e otimizar o desempenho do banco de dados.
Vamos explorar os passos práticos para realizar a recuperação do espaço, começando pela análise do estado atual dos blocos e bytes, e aplicando as técnicas necessárias para desfragmentar e otimizar o espaço disponível na tabela. A prática irá demonstrar como aplicar as teorias discutidas para garantir que o banco de dados funcione com a eficiência necessária, assim como um carro de Fórmula 1 bem ajustado está pronto para a corrida.
Imagine que a tabela é um carro de Fórmula 1, e o processo de recuperar espaço é como realizar uma manutenção e ajuste no carro para melhorar seu desempenho e eficiência.
ALTER TABLE TBL_ADSISOR ENABLE ROW MOVEMENT;Antes de uma corrida, o carro precisa estar pronto para ajustes finos e movimentação das peças. Habilitar o movimento das linhas é como permitir que a equipe de pit stop ajuste o carro, permitindo que as peças se movam e sejam reorganizadas para melhorar o desempenho.
ALTER TABLE TBL_ADSISOR SHRINK SPACE COMPACT;Compactar o espaço é como fazer um ajuste fino na configuração do carro para otimizar o desempenho no circuito. A equipe ajusta o carro para que o espaço interno esteja organizado e livre de qualquer bagunça, garantindo que tudo esteja no lugar certo para uma performance mais eficiente.
ALTER TABLE TBL_ADSISOR SHRINK SPACE;Liberar o espaço e devolvê-lo ao tablespace é como fazer uma revisão completa do carro após a corrida, onde a equipe libera o peso extra e remove peças desnecessárias para devolver o carro ao seu tamanho ideal e reduzir o consumo de combustível. Isso ajuda a melhorar a eficiência e o desempenho geral do carro no próximo evento.
ALTER TABLE TBL_ADSISOR SHRINK SPACE CASCADE;Aplicar a recuperação de espaço também aos índices é como ajustar não apenas o carro, mas também todos os acessórios e componentes adicionais, como o sistema de freios e os pneus. Isso garante que todo o conjunto do carro esteja otimizado, não apenas a parte principal, mas também os acessórios que afetam o desempenho.
ALTER TABLE TBL_ADSISOR DISABLE ROW MOVEMENT;Desabilitar o movimento das linhas é como retornar o carro ao seu estado padrão após a manutenção. A equipe de pit stop garante que o carro esteja pronto para a corrida novamente, com todas as peças fixas e ajustadas conforme o padrão de fábrica, sem necessidade de ajustes adicionais.
Resumindo você está ajustando um carro para uma corrida:
1. Preparar o carro para ajustes finos (habilitar o movimento das linhas).
2. Ajustar o carro para otimizar o espaço interno (compactar o espaço).
3. Remover o peso extra e devolver o carro ao tamanho ideal (liberar espaço para o tablespace).
4. Ajustar todos os acessórios e componentes adicionais (aplicar a recuperação de espaço aos índices).
5. Retornar o carro ao seu estado padrão e pronto para a corrida (desabilitar o movimento das linhas).
Assim, você garante que o carro (ou tabela) esteja em sua forma mais eficiente e preparada para o próximo “evento” (consultas e operações de banco de dados). É só correr para o abraço.
Assim ficou demonstrado os passos para desfragmentar uma tabela
Neste artigo, exploramos como o Segment Advisor pode ser uma ferramenta valiosa para manter seu banco de dados em forma de corrida. Com as técnicas de desfragmentação, como SHRINK SPACE e ALTER TABLE, você está essencialmente ajustando e preparando seu ambiente de dados para enfrentar os desafios de uma “corrida” intensa de consultas e transações.
Ao aplicar essas práticas recomendadas, você não só melhora a performance do banco de dados, mas também assegura que o espaço é utilizado de maneira mais eficiente, reduzindo o tempo e o esforço necessário para gerenciar o armazenamento. Portanto, continue a monitorar e otimizar seu banco de dados como se estivesse ajustando um carro de Fórmula 1, e seu sistema estará sempre pronto para a próxima “corrida”. É só acelerar e aproveitar o sucesso!
