Histogramas no Oracle: o parâmetro que mais afeta seus planos de execução – Parte 1

Olá DBAs, a vida tá boa?

O post de hoje é o pagamento de um dívida!!! huahuahuahuahua
Minha apresentação no GUOB Tech Day 2026 acabou tendo um tempo curto demais para tudo que gostaríamos de abordar, afinal, acredito eu que subestimei o tempo necessário para “dissecar” parâmetros.

Assim como eu, o otimizador do Oracle também pode se enganar ao construir planos de execução, e subestimar ou superestimar um determinado tipo de valor em uma coluna, devido a coletas estatísticas não acuradas e/ou deficientes.

Quando o Oracle, apesar de toda sua automação, agora também apesar de toda sua IA, de todas as previsões algorítmicas, quando esse software maravilhoso (não vou cuspir no prato que como e põe comida à mesa há anos…srsrsrs) que anuncia que o DBA não será mais necessário desde a versão 10g, comete algum engano, aí entramos nós, os DBAs. Prontos para fazer ajustes finos, e trazer resoluções que por enquanto, frizo o “por enquanto”, o próprio Oracle não consegue resolver sozinho.

Esse artigo vai ser um pouco mais longo que o normal, e enquanto estou escrevendo, imagino até que possa ser dividido em mais de uma parte, afinal, não quero novamente subestimar o significado de “dissecar”. Enfim, saberemos ao final do artigo.

Vamos nessa povo!!!

Se você já viu um plano de execução com ‘E-Rows: 200’ e ‘A-Rows: 100.000.000’, sabe exatamente o que eu quero dizer. O otimizador estimou 200 linhas. Na prática, eram cem milhões. Esse tipo de erro não é bug – é falta de informação. E o nome dessa informação é “histograma”.

Aqui veremos o que são histogramas, como controlar a coleta deles pelo parâmetro ‘method_opt’ da ‘DBMS_STATS.GATHER_TABLE_STATS’, quais os tipos existentes, e – mais importante – o que acontece quando você não os tem?

Para quem estava no GUOB, já viu nossa dissecação sobre o parametro estimate_percent, mas para quem não estava aqui vai uma rápida recapitulação:

Quando o Oracle vai montar um plano de execução, ele precisa estimar quantas linhas uma query vai retornar. Para isso, ele usa estatísticas – e a fórmula padrão, na ausência de histogramas, é simples:

Onde “1” é o número de filtros (ex.: WHERE STATUS IN ‘ATIVO’), e NDV é o número de valores distintos (Number of Distinct Values).

O problema é que essa fórmula assume distribuição uniforme, ou seja, o Oracle pressupõe que todos os valores aparecem com a mesma frequência. Se você tem 1.000.000 valores distintos numa coluna, o otimizador assume que cada valor representa ‘1 / 1.000.000 = 0,0001%’ das linhas.

Então por exemplo, se tivermos uma caixa de bomboms com 1.000 bomboms, sendo 500 amarelos e 500 vermelhos, sem histograma, independente do estimate_percent que eu use, após a coleta o Oracle sabe que o NDV nessa caixa de bombons é 2 (amarelos e vermelhos) então essa fórmula se aplicaria da seguinte maneira:

Isso funciona muito bem quando a distribuição é realmente uniforme, mas convenhamos que colunas com distribuição uniforme são um alinhamento planetário que só ocorre em um arquitetura a cada 2 milhões de anos, e, tem que ser em uma noite de lua de sangue…huahuauhauhauha

Mas quando ela é skewed – quando alguns valores dominam a tabela e outros aparecem raramente – a estimativa fica completamente errada, e o plano gerado pode ser catastrófico.

Agora, imaginemos uma caixa de bomboms com 1.000 bomboms, sendo 250 amarelos e 750 vermelhos, sem histograma, independente do estimate_percent que eu use, após a coleta o Oracle sabe que o NDV nessa caixa de bombons é 2 (amarelos e vermelhos) então essa fórmula se aplicaria da seguinte maneira:

Peraí, tem algo errado!!! A fórmula me trouxe exatamente o mesmo resultado. O que aconteceu? A amostra não representou perfeitamente a população. Este é o ponto do ESTIMATE_PERCENT sozinho SEM histogramas. Não está errado, é exatamente a fórmula usada, então, sem histogramas, o Oracle considera sempre a média, e a média é onde as piores degradações podem acontecer.

É exatamente para isso que existem os histogramas. Eles respondem à pergunta: “Quais valores são comuns e quais são raros?”

O controle da coleta de histogramas fica no parâmetro ‘method_opt’ da ‘DBMS_STATS.GATHER_TABLE_STATS’. Ele é, na minha opinião, o parâmetro mais crítico para a qualidade dos planos de execução.

A sintaxe geral é:

DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SCOTT',
    tabname    => 'ORDERS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

Exemplos:
FOR ALL COLUMNS SIZE AUTO
FOR ALL COLUMNS SIZE SKEWONLY
FOR ALL COLUMNS SIZE 1
FOR ALL COLUMNS SIZE 254

Onde size, pode ser definido com alguns parametros de controle:

*** SIZE AUTO E O COLUMN USAGE TRACKING ***

Utilizar o SIZE AUTO o mais recomendado pela Oracle, mas, ele vai então criar histogramas em todas as minhas colunas? O que ele vai fazer no automático?
O Oracle não simplesmente olha para todas as colunas e cria histogramas onde encontrar skew.

Ele considera a distribuição dos dados + o uso das colunas pelo workload para decidir quais colunas merecem histograma e quantos buckets usar.

O mecanismo de usage tracking registra que determinadas colunas foram utilizadas em predicados. Essa informação é registrada em estruturas internas como SYS.COL_USAGE$. Na próxima coleta de estatísticas, o DBMS_STATS utiliza essa informação para decidir sobre histogramas.

SELECT *
FROM SYS.COL_USAGE$
WHERE OBJ# = (SELECT object_id FROM dba_objects WHERE object_name = 'ORDERS' AND owner = 'SCOTT');

Se eu estiver consultando a tabela bombons, e ninguém estiver filtrando os dados pela coluna “COR”, O Oracle conhece a distribuição dos dados, mas nessa coleta não há nenhuma evidencia em workload que indique que essa coluna precisa de um histograma, ou seja, não será coletado.

Depois alguém começa a executar:

SELECT *
FROM BOMBONS
WHERE COR = 'VERMELHO';

E depois:

SELECT *
FROM BOMBONS
WHERE COR = 'AMARELO';

O Oracle registra que COR está sendo usado em predicados.

Na próxima coleta com SIZE AUTO, essa informação de workload pode contribuir para a decisão de criar um histograma para COR.

Mas… se o SIZE AUTO é tão bom e tão bem definido pela Oracle, porque eu escolheria outro método?

Na grande maioria dos ambientes Oracle, você não deveria escolher outro método sem uma razão específica. O SIZE AUTO é o default e a recomendação da Oracle para METHOD_OPT. Mas o SIZE AUTO é o melhor ponto de partida, não necessariamente a melhor escolha para todos os cenários.

Método	Quando faria sentido?
SIZE AUTO	Caso geral / recomendado. Oracle decide quais colunas precisam de histogram e quantos buckets.
SIZE SKEWONLY	Quando você quer que a decisão seja baseada na distribuição dos dados, independentemente do workload.
SIZE REPEAT	Quando você quer preservar os histogramas existentes e evitar que o conjunto de histogramas mude durante os gathers.
SIZE 1	Quando você quer explicitamente não criar histogramas.
SIZE 254/2048	Quando você quer forçar histogramas em determinadas colunas e controlar o número máximo de buckets.
FOR COLUMNS ...	Quando você quer controle cirúrgico sobre quais colunas terão histogramas.

Imagine uma tabela:

CUSTOMER
--------
CUSTOMER_ID
STATUS
COUNTRY
SEGMENT

E você tem:

METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'

O Oracle pode decidir:

CUSTOMER_ID  → sem histogram
STATUS       → histogram
COUNTRY      → histogram
SEGMENT      → sem histogram

Isso é ótimo porque você não precisa ficar decidindo manualmente coluna por coluna.

Mas imagine que você tenha identificado um problema específico:

WHERE STATUS = 'VIP'

e voce sabe que STATUS tem distribuição extremamente assimétrica, mas COUNTRY por sua vez apesar de constar bastante no Workload, é balanceado, mas não ao ponto do Oracle achar que ele não precisa de um histograma.

Você pode querer dizer explicitamente:

FOR ALL COLUMNS SIZE 1
FOR COLUMNS SIZE 254 STATUS

Ou seja:

“Não crie histogramas automaticamente em tudo. Eu sei que STATUS precisa de um.”

*** TIPOS DE HISTOGRAMA ***

Algo muito importante também para se lembrar, é que O Oracle não usa um único tipo de histograma para tudo. Ele escolhe o tipo mais adequado de acordo com a distribuição dos dados. Entender cada tipo te ajuda a interpretar o que está acontecendo no seu ambiente.

Buckets: o que são e o que guardam

Toda vez que alguém menciona histogramas, o assunto de buckets vem junto. Mas o que é um bucket, afinal?

Um bucket é uma divisão da distribuição dos dados de uma coluna. Cada bucket guarda um “resumo estatístico” de parte dos dados – quais valores se encaixam naquele intervalo e quantas linhas eles representam.

O ponto importante é: um bucket não representa uma linha, e o histograma não armazena todos os dados.

Imagine uma tabela com 200 milhões de linhas. O Oracle não vai guardar uma linha por valor. Ele vai comprimir essa distribuição em até N buckets, onde cada bucket é um resumo comprimido de um pedaço dos dados.

Abaixo um exemplo da utilização de buckets:

Com base nessa distribuição, o Oracle vai provavelmente decidir por um “Top-Frequency Histogram”. Lembre-se que a decisão do tipo de histograma é do Otimizador, baseado na análise de distribuição, então uma coleta erronea, também pode causar a escolha de um tipo erroneo, que pode ser prejudicial às decisões do otimizador.

Ps.: o limite histórico era 254 buckets (até Oracle 11g). O Oracle 12.2+ elevou esse limite para 2048. Sempre qualifique com a versão do seu ambiente.

Vamos agora sair do âmbito teórico, e vamos ver alguns casos na prática?

Exemplo 1

Cada um dos outros valores possui aproximadamente: 40.000.000 / 999.998 ≈ 40 linhas. Então temos uma distribuição absurdamente skewed.

Exemplo 1 – Cenário “A” – SEM HISTOGRAMA

Para um filtro na coluna STATUS, seguindo as fórmulas matemáticas (calculos heurísticos) que o Oracle usaria para estimar e definir o plano.

Existe um índice em STATUS. Com 200 linhas estimadas, o plano usa index range scan.

**E-Rows: 200. A-Rows: 100.000.000. Erro de 500.000x.**

O Oracle foi buscar 100 milhões de linhas por index range scan, fazendo 100 milhões de random I/Os. Um full table scan teria sido dramaticamente mais eficiente.

Exemplo 1 – Cenário “B” – COM HISTOGRAMA

Estimate_percent = 100%
Selectivity = linhas de ‘ATIVO545’ / total
Selectivity = 100.000.000 / 200.000.000 = 0,5
E-Rows = 200.000.000 × 0,5 = 100.000.000 linhas

“Espera aí… não são 200 linhas. São 100 milhões.”

Nesse caso, quanto menor a amostra, maior a chance da variação estatística distorcer a frequência capturada, e o E-Rows derivado do histograma erra proporcional a isso. Mas mesmo assim, as variações não são tão grandes ao ponto de voce perder o “fio da meada” com o otimizador.

Seguem os cenário com as maiores probabilidade matemáticas e como é feita a extrapolação dos dados pela fórmula para definir o set de valores:

ESTIMATE_PERCENT = 10%
Linhas amostradas = 200.000.000 × 10% = 20.000.000
ATIVO545 na amostra ≈ 9.600.000 (variação estatística)
Selectivity estimada = 9.600.000 / 20.000.000 = 0,48
E-Rows = 0,48 × 200.000.000 = 96.000.000
A-Rows = 100.000.000
Desvio = 4.000.000 linhas (4%)

ESTIMATE_PERCENT = 30%
Linhas amostradas    = 200.000.000 × 30% = 60.000.000
ATIVO545 na amostra  ≈ 29.700.000           (variação estatística)
Selectivity estimada = 29.700.000 / 60.000.000 = 0,495
E-Rows               = 0,495 × 200.000.000 = 99.000.000
A-Rows               = 100.000.000
Desvio               = 1.000.000 linhas (1%)

ESTIMATE_PERCENT = 50%
Linhas amostradas    = 200.000.000 × 50% = 100.000.000
ATIVO545 na amostra  ≈ 49.900.000           (variação estatística)
Selectivity estimada = 49.900.000 / 100.000.000 = 0,499
E-Rows               = 0,499 × 200.000.000 = 99.800.000
A-Rows               = 100.000.000
Desvio               = 200.000 linhas (0,2%)

ESTIMATE_PERCENT = 100%
Linhas amostradas    = 200.000.000 × 100% = 200.000.000
ATIVO545 na amostra  = 100.000.000           (exato)
Selectivity estimada = 100.000.000 / 200.000.000 = 0,5
E-Rows               = 0,5 × 200.000.000 = 100.000.000
A-Rows               = 100.000.000
Desvio               = 0 linhas (0%)

Exemplo 2

Você pode estar pensando: “NDV de 1 milhão é um caso extremo. E quando o NDV é baixo, daí tudo bem, certo?”

Bom, nem sempre.

Exemplo 2 – Cenário “A” – SEM HISTOGRAMA

O Oracle subestima “SP” e pode escolher um index range scan que vai ser devastador para uma query que retorna 80% da tabela.

Exemplo 2 – Cenário “B” – COM HISTOGRAMA

Filtrando SP

Filtrando PR

O otimizador agora escolhe o plano certo dependendo do filtro – full scan para SP, index scan para PR. Esse é o poder do histograma.

Variações Estatísticas

As variações de coleta estatísticas podem acontecer, dependendo a quantidade de amostra, e da distribuição de dados. Falar de todas as variações, nos levaria a mundos matemáticos bem complexos a serem explorados. Sendo assim, vou mostrar somente as probabilidades, baseadas em alguns dos nossos exemplos:

As chances matemáticas em percentual, da combinação de valores para nossa caixa de bombons com somente 2 cores, com 75% de vermelhos e 25% de amarelos.

Matematicamente, as chances da amostra trazer 80/20 são as maiores.

Agora, considerando a quantidade de amostragem coletada:

Quanto maior a amostra, menor a chance de confusão de dados na variação.
Uma amostra maior, reduz a probabilidade da magnitude de erro na variação.

Então, O melhor dos mundos: 100% de amostragem, com histogramas coletados para skewed columns? Pensando a grosso modo sim, mas existem particularidade nesse cenário (sim, teremos uma parte 2) e também lembre-se que tudo que é bom, pode custar CARO!

O que você paga?

• I/O para ler os blocos da tabela;
• CPU para processar os dados;
• Tempo de execução da coleta;
• Eventualmente impacto nos recursos compartilhados do banco.
• Possível engano no tipo de histograma criado.

Para uma tabela de 200 milhões de linhas, por exemplo, uma coleta de 100% pode representar uma quantidade significativa de I/O.

O ‘method_opt’ é o parâmetro que mais influencia a qualidade dos seus planos de execução, se combinado com a porcentagem de amostragem correta.. Não porque seja complicado – mas porque, quando ignorado, o otimizador trabalha às cegas.

O fluxo é simples:

Quando você combina os dois bem, o otimizador para de cheirar e começa a enxergar.

---

EM BREVE!!!!

Agora com todas essas análises e informações em mãos, a coisa fica um pouco mais interessante, e podemos nos aprofundar ainda mais…

ESTIMATE_PERCENT não influencia apenas quanto o Oracle amostra. Ele também pode influenciar como o Oracle constrói os Histogramas.

Mas por quê?
O que acontece quando você abandona o AUTO_SAMPLE_SIZE e define manualmente um percentual?
E por que isso pode levar ao uso do legado Height-Balanced Histogram?

Além disso, vou explicar com mais detalhes cada um dos principais tipos de Histogramas do Oracle – Frequency, Top-Frequency, Height-Balanced e Hybrid – mostrando como eles representam a distribuição dos dados, quando são utilizados e como podem impactar as estimativas de cardinalidade e, consequentemente, os planos de execução.

Porque sampling é apenas parte da história 😉

Esse será o assunto da Parte 2.

---

Bom, é isso, promessa é dívida. A parte mais legal da apresentação que eu queria muito falar eram os histogramas, mas o estimate_percent foi rendendo e tomando uma proporção maior, o que na minha opinião indica que o assunto estava bom e sendo útil.

Fiquem a vontade para fazer quaisquer pontuações e, falaí, o que voce achou? Já te salvou?

Essa é a dica 0800 pra voces desta vez.

“Saber das coisas vale ouro. Compartilhar esse conhecimento não tem preço.”