Introdução

Toda consulta executada no Oracle requer que o banco de dados realize uma tarefa crucial: montar um plano de execução. No plano de execução o Oracle usa estatísticas sobre os objetos (tabelas, índices, partições, etc) para determinar o melhor caminho de acesso aos dados solicitados pela query, visando obter o menor tempo de execução possível.

Para gerar um bom plano de execução, o Oracle precisa de boas estatísicas (quanto mais, melhor e quanto mais atualizada, melhor). Por padrão, essas estatísticas são coletadas todas as noites mas é comum ter ambientes com configurações diferentes, pelo fato dessa coleta ser custosa para o banco de dados.

Problema

Em alguns bancos de dados a coleta de estatísticas que ocorre na janela de manutenção do Oracle todas as noites podem não atender algumas cargas de trabalho. Tabelas que são tem muitos registros alterados entre uma janela e outra podem chegar a um certo ponto em que o desempenho das consultas sejam prejudicadas com planos de execução imprecisos, que usam estatísticas desatualizadas.

Em ambientes em que esse cenário é muito comum, os administradores de banco de dados podem criar estratégias customizadas para coletar estatísticas de tabelas consideradas “quentes” (muito acessada, muito alterada) com uma frequência maior.

Solução

A partir do Oracle Database 19c é possível utilizar uma feature chamada High-Frequency Automatic Optimizer Statistics Collection (também achei o nome grande) que foi concebida exatamente para atender esse cenário. Quando habilitada, o Oracle coleta estatísticas a cada 15 minutos (tempo configurável) para objetos com estatísticas desatualizadas (stale)

A Oracle garante que esse processo é leve por aplicar-se somente aos objetos com estatísticas desatualizadas, objetos totalmente sem estatísticas não são considerados. Também, não elimina estatísticas de objetos que não existentes e por fim, não invoca o Optimizer Statistics Advisor

Configuração

Por padrão essa feature vem desabilitada. Para habilitar, utilizamos a procedure SET_GLOBAL_PREFS na package DBMS_STATS para definir uma preferência chamada AUTO_TASK_STATUS informando o valor ON:

BEGIN
 DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS', 'ON');
END;

Até este ponto a feature está habilitada e coletará estatísticas em intervalo de 15 minutos. A seguir vamos definir o parâmetro AUTO_TASK_INTERVAL para alterar o intervalo para 5 minutos (configurado em segundos).

BEGIN
 DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300');
END;

Monitorando as execuções de coleta de estatísticas a cada 5 minutos na view DBA_AUTO_STAT_EXECUTIONS.

SELECT OPID, 
       ORIGIN, 
       STATUS, 
       TO_CHAR(START_TIME, 'DD/MM/YYYY HH24:MI:SS' ) AS BEGIN_TIME,
       TO_CHAR(END_TIME, 'DD/MM/YYYY HH24:MI:SS') AS END_TIME
FROM  DBA_AUTO_STAT_EXECUTIONS
ORDER BY OPID DESC
FETCH FIRST 10 ROWS ONLY;

Para desabilitar, definimos o valor OFF para a preferência AUTO_TASK_STATUS:

BEGIN
 DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS', 'OFF');
END;