O método do perfil do tempo de resposta

Uma lentidão só tem causa raiz quando cada segundo da espera tem explicação. Por isso o método começa pelo tempo que alguém espera, e não por indicadores do banco inteiro: mede esse tempo numa operação concreta, divide o tempo em partes e ataca a maior primeiro.

O que é

O tempo de resposta (R) é quanto a operação demorou para quem espera por ela. O WaitScope lê o trace 10046 da sessão e divide R em componentes: a CPU de cada tipo de chamada, cada evento de espera e o tempo que o trace não explica, dentro das chamadas e entre elas. A soma dos componentes é exatamente R, então todo o tempo tem dono.

O método foi descrito por Cary Millsap e Jeff Holt no livro Optimizing Oracle Performance (O'Reilly, 2003), e o nome Method R é deles. O perfil do relatório segue o mesmo princípio: o tempo total dividido em partes que somam R.

Os passos

1. Escolha a operação que importa para o negócio. Uma tarefa com alguém esperando por ela: fechar um pedido, emitir uma nota, um lote que passou do horário. A média do banco inteiro mistura essa operação com todas as outras e esconde a causa.
2. Meça R com o trace dessa operação. Ligue o trace 10046 com esperas só na sessão que executa a operação, logo antes de ela começar, e desligue logo depois do fim (ligar o trace com o DBMS_MONITOR, níveis do 10046). No relatório, R é o Tempo total do Resumo, e o Período mostra se o trace cobre a operação.
3. Ataque o maior componente. O perfil do tempo de resposta vem em ordem de duração. Nenhum ajuste economiza mais do que o componente que ele reduz: zerar um componente de 5% de R tira no máximo 5% do tempo.
4. Antes de mexer em SQL, olhe a espera pela aplicação e o tempo não contabilizado. SQL*Net message from client é tempo fora do banco, que ajuste de SQL não reduz (sessão esperando a aplicação). Tempo não contabilizado alto aponta para fila de CPU, trace sem esperas ou trace incompleto (dentro das chamadas, entre chamadas). Corrija a medida antes de tirar conclusões.
5. Desça a comando, plano e objeto. Em Principais comandos por componente, veja quais comandos gastaram o tempo do maior componente. No detalhe do comando, o plano de execução mostra a operação que concentra o trabalho, e Objetos com mais tempo de espera mostra a tabela ou o índice.
6. Mude uma coisa por vez. Com duas mudanças juntas, não dá para saber qual delas mudou o tempo, nem se uma piorou o que a outra melhorou.
7. Meça de novo com um trace novo. Repita a mesma operação, nas mesmas condições e com o trace ligado do mesmo jeito, e compare o Tempo total e o componente que você atacou. Envie o arquivo novo: o mesmo arquivo de antes abre o relatório que já existe (envio repetido). No Histórico da console, a coluna Tempo total mostra o R de cada análise.

O papel do diagnóstico

A seção Diagnóstico do relatório aponta padrões que costumam explicar o perfil, em ordem de severidade e, em geral, com um link para o dado que sustenta cada um. São pistas, não conclusões: confirme no perfil e no comando antes de agir (como ler o Diagnóstico).

Cada achado tem uma página nesta documentação, com causa, resolução e como confirmar; a lista está em por onde seguir. A API devolve o código de cada achado no campo code, por exemplo TEMPO_NA_APLICACAO.

No relatório de exemplo

O relatório de exemplo, feito com o trace sintético, tem Tempo total de 0,007 s. No perfil, o maior componente é SQL*Net message from client, com 71,3%, e o Diagnóstico abre com A sessão passou a maior parte do tempo esperando a aplicação.

Pelo passo 4, a conclusão vem antes de olhar SQL: mesmo que todos os outros componentes fossem a zero, R cairia no máximo 28,7%. O próximo componente, db file sequential read, pesa 18,5%. Só então vale descer ao comando #1, cujo plano concentra o tempo na operação TABLE ACCESS BY INDEX ROWID (operação que concentra o plano).

O que o método não faz

• Não explica o sistema inteiro. O trace mostra uma sessão: quem segura o lock que ela espera, ou o que mais rodava no servidor, fica fora dele.
• Não mede por dentro o tempo fora do banco. O tempo da aplicação aparece como espera, sem o detalhe do que ela fazia.
• Não funciona com tarefas misturadas. O trace de uma sessão que faz várias tarefas dá um perfil que não descreve nenhuma delas: trace só a operação escolhida.
• Não substitui o teste. Um ajuste que acelera uma operação pode atrasar outra: meça também as outras operações que importam.

Os limites do produto estão em o que o WaitScope não faz.

Por onde seguir

Cada página trata de um achado do Diagnóstico. Agrupadas pelo passo do método em que costumam aparecer:

• Tempo fora do banco: sessão esperando a aplicação e muitos FETCH com poucas linhas.
• Tempo que o trace não explica: dentro das chamadas, lacunas entre chamadas e CPU acima do tempo decorrido.
• Um componente domina: componente dominante, CPU dominante, leitura lenta e muitas leituras.
• Espera por outras sessões ou pelo banco: bloqueio, concorrência, commit, configuração, RAC e Resource Manager.
• Parse e bind: muitos hard parses, um PARSE a cada execução e literais em vez de bind.
• Plano e PL/SQL: operação que concentra o plano, estimativa de linhas errada e linha a linha em PL/SQL.
• Outros registros: chamadas lentas, erros Oracle, fim de dados, rollback, deadlock e trace vazio.

Para ler o relatório seção por seção, comece pelo perfil e pelo Diagnóstico.