Muitos hard parses

Hard parse é o parse completo de um SQL que o banco não achou pronto na shared pool. Feito em massa, ele gasta CPU e disputa estruturas com as outras sessões; a causa mais comum é SQL com os valores no texto, sem bind variables.

Sintomas

No Diagnóstico, aparece o achado Muitos hard parses (HARD_PARSE). O texto diz quantos PARSE foram hard parse, por exemplo 1.800 de 2.000 PARSE foram hard parse, e, quando há comandos que só diferem em literais, acrescenta Há 12 grupos de comandos que só diferem em literais: use bind variables.

O achado aparece em dois casos. No primeiro, a sessão faz muitos PARSE e boa parte deles é hard parse (coluna Misses da linha PARSE em Tipos de chamada). No segundo, os hard parses vêm de muitas variantes de SQL só com literais diferentes e o PARSE delas pesa no tempo total, mesmo que o resto da sessão faça soft parse. O percentual ao lado do título é o tempo dos PARSE sobre o tempo total: de todos eles no primeiro caso, só os das variantes no segundo. A severidade é sempre média. O link é Ver textos compartilháveis quando o relatório tem essa seção, e Ver tipos de chamada quando não tem.

A conta de grupos do texto cobre todas as formas de SQL com literais do trace, também as que ficam fora da seção Textos compartilháveis, que lista só as que mais somaram tempo. As formas que mais pesam ganham ainda o achado próprio de literais em vez de bind, com o link para o comando da forma.

Causa

No PARSE, o banco procura o SQL na shared pool. Quando acha um cursor que serve, o parse é leve (soft parse). Quando não acha, faz o hard parse: confere o texto, os objetos e as permissões e otimiza o plano. O trace marca o hard parse com mis=1 na linha PARSE. Causas comuns, da mais à menos frequente:

• SQL com literais em vez de bind variables: cada valor diferente gera um texto novo, e cada texto novo exige hard parse;
• cursores invalidados: coleta de estatísticas, DDL ou GRANT nos objetos do SQL tiram o cursor de uso, e a próxima execução refaz o parse;
• shared pool pequena ou fragmentada: cursores saem da memória e precisam ser carregados de novo;
• versões do mesmo cursor: diferenças de ambiente da sessão ou de tipo e tamanho dos binds criam cursores filhos novos.

Resolução

1. Veja se há comandos que só diferem em literais: siga o link Ver textos compartilháveis do achado (Textos compartilháveis). Se há, a correção é usar bind variables na aplicação (literais em vez de bind).
2. Para achar no banco os mesmos SQL com literais diferentes, agrupe a shared pool pela assinatura que ignora os literais:

   select force_matching_signature, count(*) as textos, min(sql_id) as exemplo
     from v$sql
    where force_matching_signature <> 0
    group by force_matching_signature
   having count(*) > 1
    order by textos desc
    fetch first 20 rows only;

3. Enquanto a aplicação não muda, CURSOR_SHARING=FORCE faz o banco trocar os literais por binds e reduz os hard parses. É paliativo e pode mudar planos: teste antes e prefira ligar só na sessão da aplicação.

   alter session set cursor_sharing = force;

4. Se não há literais, procure invalidações e recargas dos cursores:

   select sql_id, child_number, loads, invalidations, parse_calls, executions
     from v$sql
    order by invalidations desc
    fetch first 20 rows only;

5. Com muitos cursores filhos do mesmo SQL, veja o motivo de cada um em v$sql_shared_cursor.
6. Coleta de estatísticas com no_invalidate => false invalida na hora os cursores que usam o objeto: em produção, escolha o horário.

Como confirmar

Gere um trace novo da mesma operação depois da mudança. Em Tipos de chamada, na linha PARSE, Misses deve cair em relação à Quantidade, e no perfil CPU: PARSE dbcalls também. O achado some quando o hard parse deixa de ser a regra e o PARSE das variantes com literais deixa de pesar no tempo total.

A primeira execução depois de um restart ou de uma limpeza da shared pool sempre faz hard parse: meça com o banco em uso normal.

No relatório

No relatório de exemplo, o único PARSE é hard parse, mas um PARSE só não pesa na sessão: sem achado. Os links abrem as seções do exemplo:

• Diagnóstico: o achado e o link para Textos compartilháveis ou Tipos de chamada.
• Tipos de chamada: linha PARSE, Quantidade e Misses.
• Perfil, linha CPU: PARSE dbcalls: a CPU gasta em parse.
• Distribuição de latência do PARSE: a duração de cada chamada PARSE.
• Textos compartilháveis: a seção só aparece quando o trace tem comandos que só diferem em literais, por isso não está no exemplo.

Esperas na shared pool, como latch: shared pool e cursor: pin S wait on X, entram na classe Concurrency e têm página própria: disputa por estruturas internas.