Muitos FETCH com poucas linhas

Cada FETCH é uma chamada da aplicação ao banco. Com poucas linhas por FETCH, a aplicação paga uma ida e volta a cada punhado de linhas para ler o que caberia em bem menos chamadas.

Sintomas

No Diagnóstico, aparece o achado Muitos FETCH com poucas linhas (FETCH_PEQUENO). O texto diz quantos FETCH houve e quantas linhas cada um trouxe em média, por exemplo 5.000 FETCH trouxeram 1,0 linhas cada, em média, sugere aumentar o array size (fetch size) do cliente e termina com Ver tipos de chamada.

O achado conta só os FETCH de nível 0, os que a aplicação pediu: aparece quando eles são muitos e trazem, em média, poucas linhas cada. FETCH de SQL recursivo, como os de PL/SQL, não entram na conta. A severidade é sempre média, e o percentual ao lado é a duração desses FETCH sobre o tempo total.

Causa

O número de linhas por FETCH é o array size (ou fetch size) do cliente. Com array size 1, ler 10.000 linhas custa 10.000 FETCH. Em geral, o custo maior não está no FETCH em si, e sim na espera SQL*Net message from client entre um FETCH e o próximo, que o percentual do achado não inclui. Duas situações levam ao achado:

• Cursor lido em lotes pequenos: poucos EXEC e muitos FETCH. O array size do cliente está baixo.
• Muitas consultas de uma linha: o número de FETCH fica perto do número de EXEC, e cada execução devolve uma linha só. Aumentar o array size não ajuda: a aplicação faz uma consulta por item.

Resolução

1. Separe as duas situações. Em Comandos SQL, compare Exec, Fetch e Linhas dos comandos com Prof. 0.
2. Se há poucos EXEC e muitos FETCH, aumente o array size no cliente. No SQL*Plus:

   set arraysize 500

   No JDBC, por statement:

   stmt.setFetchSize(500);

   No python-oracledb:

   cursor.arraysize = 500

3. Teste alguns valores: o ganho é grande nos primeiros aumentos e depois se estabiliza, e valores muito altos gastam memória no cliente.
4. Se FETCH e EXEC são parecidos, troque as consultas de uma linha por uma consulta que traga tudo de uma vez, com junção ou com a lista de valores.
5. Para DML, o equivalente é mandar as linhas em lote: array DML, ou addBatch e executeBatch no JDBC.

Como confirmar

Gere um trace novo da mesma operação depois da mudança. No comando, Fetch deve cair e Linhas ficar igual; no perfil, as Ocorrências de SQL*Net message from client caem junto, e o Tempo total com elas. O achado some quando os FETCH da aplicação passam a trazer muitas linhas cada.

No relatório

O relatório de exemplo tem só 2 FETCH, sem este achado. Os links abrem as seções do exemplo:

• Diagnóstico: o achado e o link Ver tipos de chamada.
• Tipos de chamada: linha FETCH, Quantidade e Linhas. Essa tabela soma todas as profundidades, inclusive o SQL recursivo, e o achado usa só o nível 0: no exemplo, um dos 2 FETCH é do comando recursivo #2.
• Comandos SQL por contribuição: Prof., Exec, Fetch e Linhas de cada comando.
• Perfil, linha SQL*Net message from client: as idas e voltas entre os FETCH.
• Distribuição de latência do FETCH: a duração de cada chamada FETCH.

Este achado costuma aparecer junto com o da sessão esperando a aplicação.