Oracle Automatic Indexing: criação e gerenciamento automático de índices

O Oracle Automatic Indexing é um recurso do Oracle Database que automatiza parte do gerenciamento de índices com base no comportamento do workload. O mecanismo analisa as instruções SQL executadas no ambiente, identifica oportunidades de criação de índices, testa os candidatos e pode tornar disponíveis aqueles que demonstram benefício de desempenho.

Em ambientes corporativos, o Automatic Indexing representa uma evolução importante na administração de performance, pois permite que o banco acompanhe mudanças no workload sem depender exclusivamente da criação e manutenção manual de todos os índices pelo DBA.

Entretanto, o recurso não elimina a necessidade de conhecimento especializado. A configuração, o acompanhamento, a análise de impacto e a validação dos resultados continuam sendo importantes, especialmente em bancos de dados críticos.

Documentação oficial da Oracle: Managing Automatic Indexes.

O que é Oracle Automatic Indexing?

O Automatic Indexing automatiza tarefas de gerenciamento de índices no Oracle Database.

O recurso analisa o workload da aplicação e pode criar novos índices ou remover índices automáticos que não apresentam utilização ou benefício suficiente.

Os índices administrados automaticamente são chamados de auto indexes.

O objetivo é permitir que a estrutura de índices acompanhe alterações no comportamento das aplicações, reduzindo a dependência de processos exclusivamente manuais.

A Oracle documenta o Automatic Indexing como uma funcionalidade que pode criar, remover e reconstruir índices automaticamente de acordo com o workload.

Oracle Database 26ai — Managing Automatic Indexes

Por que o Automatic Indexing é importante?

Índices são componentes fundamentais para o desempenho de aplicações Oracle.

Uma aplicação pode mudar ao longo do tempo. Novas consultas podem ser introduzidas, filtros podem mudar, volumes de dados podem crescer e padrões de acesso podem ser alterados.

Um índice que era adequado anteriormente pode deixar de ser utilizado. Da mesma forma, uma nova consulta pode apresentar uma oportunidade de otimização que não existia quando o banco foi originalmente projetado.

O Automatic Indexing busca acompanhar essas mudanças de maneira dinâmica.

Como funciona o Oracle Automatic Indexing?

O processo de Automatic Indexing ocorre em segundo plano e analisa o workload do banco.

De forma simplificada, o processo envolve:

Identificação de candidatos a índices;
Criação de índices candidatos como invisíveis;
Validação do impacto desses índices nas instruções SQL;
Disponibilização dos índices que demonstram benefício;
Manutenção dos índices;
Remoção de índices automáticos que permanecem sem utilização por períodos determinados.

A Oracle informa que o processo automático é executado periodicamente em segundo plano e utiliza o desempenho das instruções SQL para determinar se um índice candidato é benéfico.

Oracle — How Automatic Indexing Works

Identificação dos candidatos a índices

O Oracle Automatic Indexing analisa a utilização das colunas pelas instruções SQL para identificar possíveis candidatos.

As estatísticas das tabelas são importantes nesse processo. A documentação da Oracle destaca que tabelas sem estatísticas não são consideradas para Automatic Indexing e que estatísticas obsoletas podem impedir a consideração quando não houver estatísticas em tempo real disponíveis.

Por isso, a estratégia de Automatic Indexing deve estar associada a uma política adequada de estatísticas do Oracle Database.

Veja também:

Índices invisíveis no Automatic Indexing

Uma característica importante do Automatic Indexing é a utilização de índices invisíveis durante a fase de validação.

Quando um índice candidato é criado como invisível, ele não é utilizado normalmente pelas instruções SQL.

O Oracle pode então avaliar o comportamento das instruções considerando o índice candidato e comparar os resultados.

Se o índice demonstrar benefício suficiente, ele pode ser disponibilizado para utilização pelas instruções SQL.

Esse mecanismo reduz o risco de simplesmente criar um índice em produção e alterar imediatamente o comportamento de todas as consultas.

Oracle — Automatic Indexing and Invisible Auto Indexes

Fluxo de Automatic Indexing em Oracle Database com identificação de SQLs de alto consumo, criação de índices invisíveis, validação de desempenho e aprovação dos índices pela Dominus Tech. — Fluxo técnico da Dominus Tech mostrando a identificação de SQLs críticos, criação de índices candidatos invisíveis, validação de desempenho e disponibilização dos índices aprovados.

Oracle Automatic Indexing e desempenho de SQL

O objetivo principal do Automatic Indexing é melhorar o desempenho das instruções SQL que podem se beneficiar de índices.

O mecanismo utiliza informações de desempenho para determinar se o índice candidato realmente melhora a execução das instruções.

Isso é importante porque a simples criação de um índice não garante melhoria.

Um índice pode aumentar o custo de operações DML, consumir espaço de armazenamento e até ser prejudicial quando utilizado em excesso.

Oracle Automatic Indexing e custo de DML

Índices precisam ser mantidos quando os dados das tabelas são alterados.

Operações como:

INSERT;
UPDATE;
DELETE;

podem gerar trabalho adicional para manutenção dos índices.

Por isso, o Automatic Indexing possui configuração relacionada ao custo de DML.

O parâmetro AUTO_INDEX_INCLUDE_DML_COST permite considerar o impacto das operações DML na decisão sobre a criação de índices.

A Oracle informa que essa configuração ajuda o mecanismo a avaliar se o benefício do índice compensa o overhead adicional causado por atividades intensivas de escrita.

Oracle — DBMS_AUTO_INDEX

Oracle Automatic Indexing e tabelas particionadas

Auto indexes podem ser criados para tabelas particionadas e não particionadas.

O recurso pode trabalhar com índices locais B-tree e considerar colunas utilizadas pelo workload.

Em ambientes com particionamento, a estratégia deve considerar também a arquitetura das tabelas, as consultas, os predicados utilizados e o comportamento do workload.

O Automatic Indexing não substitui uma análise arquitetural completa de particionamento e indexação.

Oracle Automatic Indexing e colunas virtuais

O Automatic Indexing pode considerar colunas de tabelas, incluindo determinadas colunas virtuais, e expressões selecionadas utilizadas pelas consultas.

Isso permite que a análise seja aplicada a determinados padrões de acesso além de simples colunas físicas.

O DBA deve, entretanto, avaliar o impacto dessas estruturas no modelo de dados e no workload da aplicação.

Oracle Automatic Indexing e índices não utilizados

Um dos recursos importantes do Automatic Indexing é a capacidade de remover índices automáticos que permanecem sem utilização por um período configurado.

Na configuração padrão documentada pela Oracle, índices automáticos não utilizados podem ser removidos após 373 dias.

Esse período pode ser configurado por meio do pacote DBMS_AUTO_INDEX.

Oracle — Configuring Automatic Index Retention

Configuração do Oracle Automatic Indexing

A configuração do Automatic Indexing é realizada por meio do pacote DBMS_AUTO_INDEX.

Uma das principais configurações é AUTO_INDEX_MODE.

Os principais modos são:

IMPLEMENT: novos auto indexes podem ser criados como visíveis e utilizados pelas instruções SQL;
REPORT ONLY: novos auto indexes são criados como invisíveis e não são utilizados normalmente pelas instruções;
OFF: impede a criação de novos auto indexes, embora índices automáticos existentes permaneçam habilitados.

A documentação oficial da Oracle apresenta essas configurações e suas respectivas funções.

Oracle — DBMS_AUTO_INDEX Configuration

Modo REPORT ONLY

O modo REPORT ONLY é especialmente útil para uma estratégia controlada de adoção.

Nesse modo, o Oracle pode criar índices automáticos como invisíveis e avaliar seu impacto sem disponibilizá-los normalmente para as instruções SQL.

Esse comportamento permite observar recomendações e resultados antes de permitir que os novos índices participem efetivamente da execução das aplicações.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE',
  'REPORT ONLY'
);

Consulte a documentação oficial antes de alterar a configuração em um ambiente de produção.

Oracle — Configuring Automatic Indexing

Modo IMPLEMENT

O modo IMPLEMENT permite que novos auto indexes sejam criados como índices visíveis e utilizados pelas instruções SQL.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE',
  'IMPLEMENT'
);

A utilização desse modo deve ser precedida por avaliação do ambiente, políticas de mudança e acompanhamento do comportamento das aplicações.

Como desabilitar o Automatic Indexing

O Automatic Indexing pode ser colocado em modo OFF por meio do DBMS_AUTO_INDEX.CONFIGURE.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE',
  'OFF'
);

De acordo com a documentação da Oracle, colocar o modo como OFF impede novos auto indexes de serem considerados e criados, mas não desabilita os índices automáticos existentes.

Oracle — DBMS_AUTO_INDEX

Controle por schema

O Automatic Indexing pode ser configurado para controlar quais schemas podem utilizar auto indexes.

A configuração AUTO_INDEX_SCHEMA permite incluir ou excluir schemas.

Esse controle é importante em ambientes corporativos nos quais diferentes aplicações possuem níveis distintos de criticidade.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_SCHEMA',
  'HR',
  FALSE
);

O exemplo acima demonstra a exclusão de um schema da utilização de auto indexes.

Oracle — Specifying Schemas That Can Use Auto Indexes

Controle por tabela

Além do controle por schema, é possível especificar tabelas que podem ou não utilizar auto indexes.

A configuração AUTO_INDEX_TABLE permite controlar esse comportamento.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_TABLE',
  'SH.PRODUCTS',
  TRUE
);

Esse nível de controle pode ser útil em ambientes corporativos com tabelas críticas, cargas específicas ou aplicações que exigem maior governança sobre estruturas de acesso.

Tablespace para Automatic Indexing

Os auto indexes precisam ser armazenados em um tablespace.

O parâmetro AUTO_INDEX_DEFAULT_TABLESPACE permite especificar o tablespace utilizado para armazenar os auto indexes.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_DEFAULT_TABLESPACE',
  'TBS_AUTO'
);

A Oracle informa que não deve ser utilizado um tablespace pertencente à Oracle, como SYSAUX, como tablespace padrão para os auto indexes.

Oracle — Specifying a Tablespace for Auto Indexes

Controle do espaço utilizado pelos auto indexes

O Automatic Indexing também permite configurar um orçamento de espaço para os auto indexes.

O parâmetro AUTO_INDEX_SPACE_BUDGET pode ser utilizado para especificar uma porcentagem do tablespace disponível para os índices automáticos em determinadas configurações.

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_SPACE_BUDGET',
  '5'
);

O controle de espaço é especialmente importante em ambientes de grande porte, nos quais a criação de índices deve ser acompanhada também sob a perspectiva de capacidade de armazenamento.

Oracle Automatic Indexing e compressão

O Automatic Indexing possui configuração relacionada à compressão avançada dos índices.

O parâmetro AUTO_INDEX_COMPRESSION pode ser utilizado para controlar essa característica.

A utilização de Advanced Index Compression está relacionada ao licenciamento correspondente da Oracle.

Oracle — Configuring Advanced Index Compression for Auto Indexes

DBMS_AUTO_INDEX

O pacote DBMS_AUTO_INDEX fornece a interface para configurar o Automatic Indexing e gerar relatórios relacionados às operações de indexação automática.

Entre as funcionalidades estão:

Configuração do Automatic Indexing;
Geração de relatórios;
Gerenciamento de auto indexes;
Exclusão manual de auto indexes;
Configuração de schemas;
Configuração de tabelas;
Configuração de retenção;
Configuração de tablespace.

Documentação oficial — DBMS_AUTO_INDEX

Monitoramento do Automatic Indexing

O Automatic Indexing deve ser monitorado continuamente em ambientes corporativos.

O DBA deve acompanhar:

Índices criados;
Índices utilizados;
Índices não utilizados;
Impacto sobre SQLs;
Impacto sobre DML;
Espaço consumido;
Recomendações;
Relatórios de atividade;
Alterações no workload.

A Oracle disponibiliza informações e APIs específicas para geração de relatórios do Automatic Indexing.

DBA_AUTO_INDEX_CONFIG

A view DBA_AUTO_INDEX_CONFIG apresenta as configurações atuais do Automatic Indexing.

Ela permite consultar parâmetros como nome, valor, data da última alteração e usuário responsável pela alteração.

SELECT
    parameter_name,
    parameter_value,
    last_modified,
    modified_by
FROM dba_auto_index_config
ORDER BY parameter_name;

Oracle — DBA_AUTO_INDEX_CONFIG

Oracle Automatic Indexing e Oracle AWR

O Oracle AWR é importante para complementar a análise de performance relacionada ao Automatic Indexing.

Mesmo com a automação da criação de índices, o DBA deve compreender quais SQLs estão consumindo recursos e como o workload está evoluindo.

AWR permite analisar o comportamento histórico do banco e verificar se as alterações produziram resultados sustentáveis.

Oracle Automatic Indexing e Oracle ASH

O Oracle ASH pode ser utilizado para aprofundar a análise de sessões e SQLs durante períodos de maior atividade.

Essa informação ajuda o DBA a relacionar problemas de performance com SQLs específicos e verificar se alterações de indexação estão produzindo os resultados esperados.

Oracle Automatic Indexing e Oracle SQL Tuning Advisor

O Oracle SQL Tuning Advisor e o Automatic Indexing possuem objetivos complementares.

O SQL Tuning Advisor analisa instruções SQL e pode apresentar recomendações de tuning. O Automatic Indexing automatiza parte do gerenciamento de índices com base no workload.

Os dois recursos podem fazer parte de uma estratégia maior de otimização do Oracle Database.

Oracle Automatic Indexing e Oracle Performance Tuning

O Automatic Indexing deve ser considerado como uma ferramenta dentro de uma estratégia abrangente de Oracle Performance Tuning.

Performance tuning corporativo envolve muito mais do que índices. CPU, memória, I/O, SQL, planos de execução, estatísticas, arquitetura, armazenamento e comportamento das aplicações também precisam ser considerados.

Por isso, a automação deve complementar e não substituir a análise especializada do DBA.

Ambiente corporativo Oracle com Automatic Indexing integrado ao monitoramento de SQL, AWR, ASH e ferramentas de performance da Dominus Tech. — Ambiente corporativo da Dominus Tech demonstrando Automatic Indexing integrado à análise de SQL, AWR, ASH e monitoramento contínuo de performance.

Oracle Automatic Indexing em ambientes de produção

Antes de habilitar o Automatic Indexing em produção, é importante avaliar o perfil do ambiente.

Entre os pontos que devem ser analisados estão:

Volume de dados;
Perfil de leitura;
Perfil de escrita;
Quantidade de SQLs;
Criticidade das aplicações;
Espaço disponível;
Políticas de mudança;
Licenciamento;
Estratégia de rollback;
Monitoramento pós-implementação.

Ambientes com grande volume de DML exigem atenção especial porque cada índice adicional pode gerar overhead de manutenção.

Oracle Automatic Indexing e governança

A automação de índices não deve eliminar os processos de governança de TI.

Em ambientes corporativos, é recomendável estabelecer políticas para:

Quais schemas podem utilizar auto indexes;
Quais tabelas devem ser excluídas;
Qual espaço pode ser utilizado;
Como as alterações serão monitoradas;
Como resultados serão validados;
Como incidentes serão tratados;
Como alterações serão documentadas.

Essa abordagem permite aproveitar os benefícios da automação mantendo controle operacional sobre o ambiente.

Oracle Automatic Indexing e licenciamento

A utilização do Automatic Indexing possui requisitos de licenciamento que devem ser avaliados antes de sua implementação.

A documentação atual da Oracle informa que o uso do Automatic Indexing requer a opção Real Application Testing.

Também existem requisitos específicos relacionados a recursos como Advanced Index Compression.

Antes de habilitar o recurso em um ambiente corporativo, o modelo de licenciamento deve ser validado de acordo com a edição, versão e configuração do Oracle Database.

Oracle — Automatic Indexing licensing requirements

Benefícios do Oracle Automatic Indexing

Automação do gerenciamento de índices;
Adaptação às mudanças do workload;
Identificação automática de oportunidades;
Validação de índices candidatos;
Redução de atividades manuais repetitivas;
Possibilidade de remover índices automáticos sem utilização;
Controle por schema e tabela;
Relatórios e monitoramento do processo.

Cuidados com Oracle Automatic Indexing

Validar licenciamento antes da utilização;
Monitorar espaço consumido;
Avaliar impacto sobre DML;
Manter estatísticas adequadas;
Monitorar SQLs críticos;
Controlar schemas e tabelas quando necessário;
Acompanhar índices criados automaticamente;
Validar o impacto no workload;
Manter uma estratégia de governança.

Serviços relacionados a Oracle Automatic Indexing

A adoção do Automatic Indexing pode fazer parte de projetos de administração, monitoramento e otimização do Oracle Database.

Entre os serviços relacionados estão:

Oracle Performance Tuning;
SQL Tuning;
Consultoria Oracle;
DBA Oracle;
Monitoramento de Banco de Dados;
Diagnóstico de performance;
Otimização de SQL;
Capacity Planning;
Revisão de arquitetura Oracle.

Conheça também:

Conteúdos relacionados do cluster Oracle

Performance e tuning

Administração e monitoramento

Arquitetura Oracle

Conclusão

O Oracle Automatic Indexing representa uma evolução importante na administração de índices do Oracle Database ao permitir que o banco analise o workload e automatize parte do processo de identificação, criação, validação e remoção de índices.

O recurso pode contribuir para melhorar o desempenho de aplicações que apresentam mudanças frequentes no padrão de acesso aos dados, mas sua utilização deve ser acompanhada por uma estratégia de governança, monitoramento e avaliação de impacto.

Em ambientes corporativos, o Automatic Indexing funciona melhor quando integrado a uma metodologia mais ampla de performance que envolva Oracle AWR, Oracle ASH, Oracle ADDM, SQL Tuning Advisor e análise especializada de DBA.

Antes da implementação, devem ser avaliados especialmente o licenciamento, o impacto sobre DML, o espaço de armazenamento, o comportamento das aplicações e as políticas de mudança da organização.

Documentação oficial Oracle

Dashboard corporativo da Dominus Tech para Oracle Database, destacando Performance Tuning, otimização de licenciamento, análise de desempenho, observabilidade Full Stack, monitoramento da infraestrutura, alta disponibilidade e indicadores em tempo real para ambientes críticos. — A Dominus Tech ajuda empresas a reduzir custos de licenciamento Oracle Database por meio de Performance Tuning, análise de utilização, otimização de recursos, observabilidade Full Stack e monitoramento inteligente da infraestrutura de TI.

A proteção dos dados corporativos exige planejamento, experiência e conhecimento das melhores práticas da Oracle.

A Dominus Tech oferece serviços especializados para implantação, revisão e evolução da segurança em ambientes Oracle Database, incluindo assessment, hardening, criptografia, auditoria, monitoramento, alta disponibilidade e recuperação de desastres.

👉Entre em contato com nossa equipe para descobrir como fortalecer a segurança do seu ambiente Oracle Database e reduzir os riscos para o seu negócio.