Estimativa de linhas errada no plano

O otimizador escolhe a ordem e o método das junções pelo número de linhas que espera de cada operação. Quando essa estimativa erra muito, o plano costuma ter sido escolhido para um volume que não existe.

Sintomas

No Diagnóstico, aparece o achado Comando #n: estimativa de linhas errada na operação id do plano (ESTIMATIVA_ERRADA). O texto começa com a operação e o tamanho do erro (o otimizador estimou e linhas por execução e vieram r, com quantas vezes mais ou menos), explica o efeito na escolha do plano, lista as causas comuns e termina com Ver no plano do comando #n.

O achado aparece nos comandos que pesam no tempo total, pelo tempo inclusivo. Ele precisa das linhas STAT com quantas vezes cada operação rodou (str=) e a estimativa do otimizador (card=), e de uma operação com as linhas reais por execução (Linhas dividido por Exec.) muito longe das estimadas, num ramo do plano que trabalha de verdade: erro num ramo que quase não trabalha não muda o tempo.

Entre essas operações, o achado aponta aquela em que o erro começa, a mais funda do plano: o erro de baixo se propaga para as operações de cima, e corrigir o de baixo costuma acertar os outros. A severidade é média quando a operação é uma das marcadas em Onde o plano gasta e baixa nos outros casos. Sai um achado por comando, a começar pelos que mais pesam.

No plano, Linhas/exec e Estimado ficam em vermelho em toda operação em que o otimizador errou a quantidade de linhas por 10 vezes ou mais, como diz a nota abaixo do plano, mesmo nas que não viraram achado. Erro numa operação de poucas linhas não fica vermelho.

Causa

O otimizador estima as linhas pelas estatísticas de tabelas, colunas e índices, e pelo valor dos binds na primeira execução. Com a estimativa errada:

• estimou poucas e vieram muitas: NESTED LOOPS que roda o lado interno milhares de vezes, ou índice usado para ler boa parte da tabela;
• estimou muitas e vieram poucas: leitura completa e HASH JOIN onde um acesso por índice bastaria.

Causas comuns, da mais comum à menos comum:

• estatísticas desatualizadas ou ausentes, por exemplo depois de uma carga grande;
• colunas correlacionadas: o otimizador multiplica as seletividades como se as colunas fossem independentes, como cidade e estado;
• dados concentrados em poucos valores, sem histograma;
• bind peeking: o plano foi montado para o valor do bind da primeira execução e serve mal aos outros;
• função ou expressão sobre a coluna no filtro (upper(nome), trunc(data)), que o otimizador estima por um palpite padrão.

Resolução

Abra Ver no plano do comando #n e ache as tabelas da operação e das que estão abaixo dela pela coluna obj. Depois, siga do caso mais comum ao menos comum.

Estatísticas desatualizadas

Veja quando as estatísticas foram coletadas e se o banco as considera vencidas:

select owner, table_name, num_rows, last_analyzed, stale_stats, stattype_locked
  from dba_tab_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

Na console, o número da coluna obj abre a página do objeto. Em Estatísticas, o bloco Renovar as estatísticas da tabela guarda as estatísticas atuais em SYSTEM.JB_BACKUP_STATS antes de coletar, e o bloco Voltar as estatísticas da tabela ao backup desfaz a coleta. A documentação do DBMS_STATS traz as opções de coleta.

Colunas correlacionadas

Crie uma estatística estendida para o grupo de colunas que o filtro usa junto e colete as estatísticas da tabela de novo:

select dbms_stats.create_extended_stats('EXEMPLO', 'PEDIDO', '(UF, CIDADE)')
  from dual;

A documentação da Oracle sobre estatísticas estendidas mostra também a estatística sobre expressão, para o caso de função sobre a coluna.

Dados concentrados em poucos valores

select column_name, num_distinct, num_nulls, histogram, last_analyzed
  from dba_tab_col_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

Coluna do filtro com poucos valores e histogram igual a NONE pede histograma. Colete com method_opt => 'FOR ALL COLUMNS SIZE AUTO', que cria histograma nas colunas usadas em filtro que têm dados concentrados; o bloco de renovação da console já usa essa opção.

Bind peeking

Veja os cursores filhos do comando e se o banco já trata o plano como sensível ao valor do bind. O SQL ID está no topo do detalhe do comando:

select child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
  from v$sql
 where sql_id = '&sql_id';

Com is_bind_aware igual a Y, o adaptive cursor sharing já escolhe o plano pela faixa do valor. Se um plano serve bem a todos os valores, aceite só esse plano com uma SQL plan baseline.

Numa linha com o selo N variantes, que soma SQL só com literais diferentes, o peso do comando é a soma das variantes. No detalhe, cada plano diferente aparece uma vez, com as linhas da execução gravada mais demorada entre as variantes: é nesse plano que a estimativa é conferida.

Como confirmar

1. Aplique uma correção por vez. Depois da coleta de estatísticas, a próxima execução monta o plano de novo.
2. Ligue o trace da mesma operação. Para gravar o plano de todas as execuções, e não só o da primeira, use plan_stat => 'ALL_EXECUTIONS' (ligar o trace com o DBMS_MONITOR).
3. Envie o arquivo novo e abra o plano do mesmo comando.
4. Compare Linhas/exec e Estimado da operação.

Resultado: as duas colunas ficam próximas, sem vermelho, e o achado some. O plano pode mudar: outro número em Plano de execução, com outras operações em destaque.

No relatório

• Diagnóstico: o achado e o link Ver no plano do comando #n (Diagnóstico).
• Plano de execução: as colunas Exec., Linhas, Linhas/exec e Estimado, e as operações marcadas em Onde o plano gasta (plano de execução). O trace sintético do exemplo não traz str=, então as colunas Exec. e Linhas/exec ficam vazias e este achado não aparece nele.
• Detalhe do comando: o SQL ID, o texto e o perfil do comando (detalhe do comando).
• Página do objeto, pela coluna obj: colunas com num_distinct e histogram, índices e os blocos de estatísticas. Só existe na console.

Se a mesma operação também concentra o tempo do plano, veja operação que concentra o plano.