CPU domina o tempo de resposta

Quando a CPU domina, a sessão trabalhou de fato no banco, e o trabalho é que precisa diminuir. Quase sempre o excesso vem de leitura lógica, de parse ou de PL/SQL pesado.

Sintomas

No Diagnóstico, aparece o achado CPU domina o tempo de resposta (CPU_DOMINANTE). O texto diz quanto do tempo total é CPU, em percentual e em segundos, lista os componentes de CPU somados, por exemplo CPU: FETCH dbcalls, CPU: EXEC dbcalls, sugere começar pelos comandos com mais LIO e termina com Ver perfil por classe.

O achado aparece quando a classe CPU, a soma da CPU de todos os tipos de chamada, concentra a maior parte do tempo total. A severidade é sempre média. Com ele, o achado de componente dominante não se repete para a CPU.

Causa

A CPU do perfil é a CPU própria de cada chamada, como PARSE, EXEC e FETCH: o c= dela menos o das chamadas filhas. É tempo em que a sessão rodou na CPU. As causas mais comuns:

• Leitura lógica em excesso (LIO). O plano lê muito mais blocos do buffer cache do que as linhas devolvidas pedem: índice pouco seletivo, NESTED LOOPS sobre muitas linhas, a mesma tabela revisitada a cada linha. Aparece como CPU de FETCH ou de EXEC.
• Parse. Hard parse por SQL sem bind, ou PARSE repetido do mesmo cursor. Aparece como CPU de PARSE.
• PL/SQL pesado. Lógica em laço e funções chamadas para cada linha de um SQL. Aparece como CPU de EXEC do bloco PL/SQL.

Fila de CPU no servidor não entra aqui: o tempo em que a sessão esperou por CPU livre cai em tempo não contabilizado dentro das chamadas.

Resolução

1. Veja qual tipo de chamada pesa. No perfil, compare CPU: FETCH dbcalls, CPU: EXEC dbcalls e CPU: PARSE dbcalls.
2. Se é PARSE, siga muitos hard parses, um PARSE a cada execução e literais em vez de bind.
3. Se é FETCH ou EXEC, ache o comando. Em Principais comandos por componente e por classe, abra o grupo da classe CPU; em Comandos SQL, compare a coluna LIO com Linhas.
4. No plano do comando, ache em Onde o plano gasta a operação com mais LIO próprio e corrija o acesso: índice mais seletivo, outra ordem ou outro método de junção, estatísticas corretas (plano de execução, estimativa de linhas errada).
5. Se o comando é um bloco PL/SQL, abra SQL recursivo executado por este comando no detalhe dele. Se os filhos são poucos e baratos, a CPU é do próprio código PL/SQL: para achar a linha que gasta, use o profiler hierárquico do PL/SQL (DBMS_HPROF).

Como confirmar

Gere um trace novo da mesma operação depois da mudança. A Duração (s) da classe CPU e o LIO do comando corrigido devem cair, e o Tempo total com eles. O achado some quando a CPU deixa de concentrar a maior parte do tempo total.

Compare o tempo total, não só a CPU: um plano novo pode ler menos blocos do cache e mais do disco, trocando CPU por espera de leitura.

No relatório

No relatório de exemplo, a CPU soma 3,7% do tempo total, sem este achado. Os links abrem as seções do exemplo:

• Diagnóstico: o achado e o link Ver perfil por classe.
• Por classe de espera, linha CPU: a soma da CPU e, em Principais componentes, os tipos de chamada.
• Perfil, linhas de CPU: uma por tipo de chamada.
• Principais comandos por classe, grupo CPU: os comandos que mais gastaram CPU.
• Comandos SQL por contribuição: colunas LIO e Linhas.
• Tipos de chamada: CPU (s), Consistente (CR) e Corrente (CU) de cada chamada.