Muitas leituras de bloco único

O tempo de leitura é o número de leituras vezes a média de cada uma. Quando a média é normal e o tempo é alto, o problema é a quantidade: o plano visita blocos demais para as linhas que devolve.

Sintomas

No Diagnóstico, aparece o achado Muitas leituras de bloco único (db file sequential read) (MUITAS_LEITURAS), com o nome do evento entre parênteses. O texto diz quantas leituras somam que parte do tempo total, por exemplo 25.000 leituras somam 32,4% do tempo total, sugere ver se o plano lê blocos demais e termina com Ver distribuição de latência, ou Ver no perfil quando o evento não tem tabela de latência.

O achado aparece quando uma espera de leitura de um bloco por vez, como db file sequential read ou cell single block physical read, ocupa uma parte grande do tempo total, qualquer que seja a média por leitura. A severidade é sempre média. Pode aparecer junto com leitura lenta: um fala da quantidade, o outro da velocidade.

Causa

Causas comuns:

• índice pouco seletivo, que devolve muitos ROWIDs e leva a uma visita à tabela para cada um (TABLE ACCESS BY INDEX ROWID);
• NESTED LOOPS sobre muitas linhas, com uma busca no índice da tabela interna para cada linha da externa;
• índice com clustering factor alto: linhas vizinhas no índice espalhadas por muitos blocos da tabela;
• o mesmo SQL executado uma vez para cada linha de um laço (linha a linha em PL/SQL);
• buffer cache pequeno para o volume de dados, com o mesmo bloco lido do disco mais de uma vez.

Resolução

1. Ache o comando. Em Principais comandos por componente, abra o grupo do evento de leitura e veja os comandos que mais leram.
2. No detalhe do comando, em Onde o plano gasta, veja a operação que concentra o tempo, com as colunas PR (leituras físicas) e Linhas do plano. Muitas leituras para poucas linhas indicam acesso pouco seletivo.
3. Ache o objeto em Objetos com mais tempo de espera. Na console, o obj# abre a página do objeto, com consultas prontas como Tabela: índices e as colunas de cada um e Índice: detalhes, tabela e colunas.
4. Corrija o acesso: índice com as colunas do filtro, na ordem certa; HASH JOIN em vez de NESTED LOOPS quando o volume é grande; estatísticas atualizadas quando a estimativa está errada (estimativa de linhas errada).
5. Se o mesmo SQL roda uma vez por linha, troque o laço por processamento em conjunto (linha a linha em PL/SQL).
6. Se a releitura do mesmo bloco pesa (em Leituras, Primeira leitura e releitura do mesmo bloco), avalie o tamanho do buffer cache.

Como confirmar

Gere um trace novo da mesma operação depois da mudança. Na linha do evento no perfil, as Ocorrências devem cair; no comando corrigido, a coluna PIO de Comandos SQL também. O achado some quando o evento deixa de ocupar uma parte grande do tempo total.

Na primeira execução depois da mudança, parte dos blocos pode vir do disco por ainda não estar no cache. Compare medidas feitas na mesma condição: as duas com o cache frio, ou as duas com o cache já aquecido.

No relatório

No relatório de exemplo, db file sequential read soma 18,5% do tempo total em 2 leituras, sem achado. O relatório do caso 2, de um trace real, tem este achado (Diagnóstico do caso 2). Os links abaixo abrem as seções do exemplo:

• Diagnóstico: o achado e o link para a latência do evento.
• Perfil, linha db file sequential read: Ocorrências e % do total.
• Principais comandos por componente, grupo do evento: os comandos que mais leram.
• Plano do comando #1: a operação TABLE ACCESS BY INDEX ROWID em destaque.
• Objetos com mais tempo de espera e Leituras: o objeto lido e a releitura dos mesmos blocos.