Disputa por estruturas internas do banco

Latches, mutexes e buffers protegem estruturas da memória do banco: enquanto uma sessão mexe numa delas, as outras que precisam dela esperam a vez. A causa costuma ser parse em excesso, bloco quente ou objeto alterado enquanto está em uso.

Sintomas

No Diagnóstico, aparece o achado Disputa por estruturas internas do banco (CONCORRENCIA). O texto diz quanto do tempo total ficou na classe de espera Concurrency, em percentual e em segundos, lista os eventos da classe que pesaram e termina com Ver perfil por classe.

O achado aparece quando a classe Concurrency pesa no tempo total. A severidade é média e passa a alta quando a classe pesa mais. Entram nessa classe estes eventos, pela wait_class do Oracle 19c:

read by other session fica em User I/O e latch free em Other: não contam aqui. Evento que o relatório não classifica aparece no perfil como Não classificado.

Causa

A sessão do trace disputou uma estrutura com outras sessões que rodavam ao mesmo tempo. O trace mostra só a espera desta sessão; quem disputava com ela fica fora dele. Causas comuns, da mais comum à menos comum:

• Parse em excesso: SQL com literais em vez de bind variables, ou cursor aberto e fechado a cada execução. Pesa nos latches da shared pool e da library cache e nos mutexes de cursor.
• Bloco quente por leitura lógica: muitas sessões lendo os mesmos blocos, em geral por um plano que relê os mesmos blocos (latch: cache buffers chains).
• Inserção concorrente no mesmo bloco: índice com chave crescente (sequence, data) recebendo inserções de várias sessões (buffer busy waits, enq: TX - index contention).
• Sequence sem cache ou com cache pequeno (row cache lock).
• DDL, compilação ou coleta de estatísticas em objeto em uso, e cursores invalidados com frequência (library cache lock, library cache pin, cursor: pin S wait on X).
• Consulta em paralelo iniciando os processos de execução paralela (os thread startup).

Resolução

Comece pelo evento que o achado lista primeiro. Os casos vão do mais comum ao menos comum.

Parse em excesso

Confira a linha PARSE em Tipos de chamada: Misses perto de Quantidade quer dizer hard parse em quase todo PARSE. Se o Diagnóstico também mostra um destes achados, comece por ele:

• Muitos hard parses.
• Literais em vez de bind variables.
• Um PARSE a cada execução.

Bloco quente por leitura lógica

Com latch: cache buffers chains, reduza as leituras lógicas dos comandos que esperaram. Ache esses comandos no grupo Concurrency de Principais comandos por componente e por classe e, no plano de cada um, a operação com LIO próprio alto (operação que concentra o plano). O caso típico é um NESTED LOOPS que relê os mesmos blocos milhares de vezes.

Inserção concorrente no mesmo bloco

Em Objetos com mais tempo de espera, ache o índice ou a tabela das esperas. Para índice com chave crescente, as saídas usuais são o índice reverse key e o índice global particionado por hash:

alter index exemplo.pedido_pk rebuild reverse;

create index exemplo.pedido_data_ix on exemplo.pedido (data_pedido)
  global partition by hash (data_pedido) partitions 16;

O índice reverse key deixa de servir para busca por faixa na chave (between, >), e o índice particionado exige a opção Partitioning. Para tabela com buffer busy waits em inserção, confira se o segmento está numa tablespace com gerenciamento automático de espaço (ASSM).

Sequence sem cache

Para row cache lock em comandos que usam NEXTVAL, veja o cache das sequences do esquema e aumente o das mais usadas:

select sequence_owner, sequence_name, cache_size, order_flag
  from dba_sequences
 where sequence_owner = 'EXEMPLO'
 order by cache_size;

alter sequence exemplo.pedido_seq cache 1000;

Com ou sem cache, sequence não garante número sem buraco: rollback também pula números. O cache só aumenta o buraco quando a instância cai ou a sequence sai da shared pool.

Objeto alterado enquanto está em uso

DDL, compilação de pacote e coleta de estatísticas invalidam os cursores que usam o objeto, e as sessões param para refazer o parse. Leve essas tarefas para fora do horário de uso. Se o comando tem versões demais do cursor, v$sql_shared_cursor diz o motivo de cada versão:

select sql_id, version_count, executions, parse_calls
  from v$sqlarea
 order by version_count desc
 fetch first 20 rows only;

Processos de consulta em paralelo

os thread startup é o tempo de iniciar processos, em geral os de execução paralela. Confira o grau de paralelismo dos comandos que esperaram: em comando curto e muito executado, o paralelismo custa mais do que ganha.

Como confirmar

1. Aplique uma correção por vez.
2. Ligue o trace da mesma operação com esperas (ligar o trace com o DBMS_MONITOR).
3. Envie o arquivo novo e abra o relatório.
4. Compare a linha Concurrency em Por classe de espera e as linhas dos eventos no perfil.

Resultado: a classe Concurrency deixa de pesar no tempo total e o achado some do Diagnóstico. Quando a causa era parse, a coluna Misses da linha PARSE em Tipos de chamada também cai.

No relatório

O relatório de exemplo não tem espera da classe Concurrency; os links abrem as mesmas seções nele.

• Diagnóstico: o achado, com o link Ver perfil por classe.
• Por classe de espera: a linha Concurrency, com os eventos em Principais componentes (classes de espera).
• Perfil do tempo de resposta: cada evento, com Ocorrências e Média (s). Clique no nome do evento para abrir o cartão com a causa típica e onde olhar (perfil).
• Principais comandos por componente e por classe: os comandos que disputaram (comandos por componente).
• Tipos de chamada: Quantidade e Misses da linha PARSE (tipos de chamada).
• Objetos com mais tempo de espera: o índice ou a tabela disputada (objetos).
• Textos compartilháveis: comandos que só diferem em literais. A seção só aparece quando o trace tem esses comandos (textos compartilháveis).