Operação do plano que concentra o trabalho

O plano de um comando pesado costuma gastar quase tudo numa ou duas operações. O achado aponta qual, traz uma pista do que costuma estar por trás e leva direto à linha do plano.

Sintomas

No Diagnóstico, aparece o achado Comando #n: a operação id do plano concentra p% do tempo (PLANO_PONTO_QUENTE). Quando a parte da operação nas leituras lógicas é maior que no tempo, o título termina em das leituras lógicas. O número do comando é o da lista de comandos SQL, o da operação é o Id dela no plano, e o percentual é a parte própria da operação no plano. No relatório de exemplo, o achado é Comando #1: a operação 1 do plano concentra 98,0% do tempo.

Ao lado do título, o percentual do tempo total é o peso do comando, pelo tempo inclusivo (todas as chamadas dele, em qualquer profundidade). O texto começa com o nome da operação, traz a pista, lista as outras operações que também pesam (Também pesam) e termina com Ver no plano do comando #n.

O achado aparece para os comandos que pesam no tempo total, pelo tempo inclusivo, quando o plano, montado das linhas STAT do trace, tem mais de uma operação e uma delas concentra o tempo ou as leituras lógicas. Plano de uma operação só não tem onde apontar. Sai um achado por comando, do primeiro plano dele com operação em destaque, a começar pelos comandos que mais pesam.

A severidade é baixa e passa a média quando o comando pesa mais no tempo total.

Causa

O valor próprio de uma operação desconta o das operações abaixo dela, que a alimentam. As operações em destaque são as que mais pesam no plano por esse valor próprio. A pista do achado depende do tipo da operação:

A pista pode juntar a do tipo da operação com as de gravação no TEMP, de muitas execuções e de estimativa errada.

Resolução

1. Clique em Ver no plano do comando #n.

   Resultado: o detalhe do comando abre na linha da operação. Em Onde o plano gasta, as operações em destaque aparecem numeradas, da mais pesada para a menos pesada, com a parte de cada uma no tempo e nas leituras lógicas.

2. Clique no nome da operação para abrir o cartão com o que ela faz, quando é boa, quando é ruim e que colunas do plano conferir.
3. Compare a coluna Linhas da operação com a da operação logo abaixo dela. Se a tabela devolve bem menos linhas do que o índice trouxe, o filtro está na tabela e falta no índice a coluna dele.
4. Veja os predicados de acesso e de filtro, que as linhas STAT do trace não trazem. Com o cursor ainda na shared pool, use o SQL ID do topo do detalhe do comando:

   select *
     from table(dbms_xplan.display_cursor('&sql_id', null, 'TYPICAL'));

5. Aplique a correção do tipo da operação:
   • acesso pelo ROWID com filtro na tabela: índice com as colunas do filtro, as de igualdade primeiro;
   • leitura FULL com filtro seletivo: índice para o filtro;
   • skip scan: índice que comece pela coluna filtrada;
   • faixa larga do índice: coluna de igualdade antes da coluna de faixa;
   • gravação no TEMP: mais memória para a workarea (pga_aggregate_target) ou menos linhas chegando à ordenação ou ao hash join;
   • muitas execuções: a operação é o lado interno de um NESTED LOOPS. Acerte a estimativa do lado externo ou troque a junção por HASH JOIN.
6. Para ver as colunas e os índices da tabela, clique no número da coluna obj do plano. A página do objeto, com consultas prontas, existe só na console; no HTML baixado, o número abre o detalhe do objeto, sem as consultas.

Se o Diagnóstico também mostra estimativa de linhas errada para o mesmo comando, comece por ela: com a estimativa certa, o otimizador pode escolher outro plano sozinho.

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 gravações das variantes somadas e as linhas da execução gravada mais demorada entre elas: é nesse plano que o achado aponta a operação.

Como confirmar

1. Aplique uma mudança por vez.
2. Ligue o trace da mesma operação. Com o padrão do DBMS_MONITOR, o trace grava o plano da primeira execução de cada cursor; para gravar o de todas, use plan_stat => 'ALL_EXECUTIONS':

   begin
     dbms_monitor.session_trace_enable(session_id => &sid, serial_num => &serial,
                                       waits => true, binds => false,
                                       plan_stat => 'ALL_EXECUTIONS');
   end;
   /

3. Envie o arquivo novo e abra o detalhe do mesmo comando.
4. Compare Tempo próprio (s) e LIO próprio da operação e o Inclusivo do comando, no topo do detalhe.

Resultado: a operação deixa de concentrar o plano, ou o plano muda (outro número em Plano de execução). Quando o comando deixa de pesar no tempo total, ele sai do achado.

No relatório

O relatório de exemplo tem este achado, feito com o trace sintético; os links abrem cada parte dele.

• Diagnóstico: o achado Comando #1: a operação 1 do plano concentra 98,0% do tempo, com a pista do TABLE ACCESS BY INDEX ROWID e a operação 2 em Também pesam.
• Detalhe do comando #1: Peso no tempo total, Inclusivo, perfil do comando e Onde o plano gasta (detalhe do comando).
• Operação 1 do plano: a linha em destaque, com Tempo próprio (s), % tempo, LIO próprio e % LIO (plano de execução).
• Comandos SQL por contribuição: o número do comando e o peso dele (comandos SQL).

No relatório da console, o link do achado abre a página do comando; no HTML baixado, abre o detalhe do comando no lugar do relatório, já na operação do plano.

Os relatórios de traces reais também têm este achado: veja o Diagnóstico do caso 1 e o do caso 2.