Oracle Performance Troubleshooting , , , , ,

Row Cache Lock: causas e soluções

Durante a análise de um ambiente, notei a ocorrência do wait event row cache lock durante um período de carga de dados no banco. Neste post, estarei falando sobre a causa deste wait event, e como podemos resolvê-lo nesse cenário.

A origem do wait event

O Row Cache é uma área em memória que armazena tabelas e views que contém informações sobre o banco de dados, suas estruturas e seus usuários, e com isso, visa reduzir o número de physical reads nas tabelas e views do dicionário de dados. Também é conhecido por Data Dictionary Cache, que é o nome usado pela Oracle na sua documentação oficial.

Quando operações alteram a estrutura de objetos, como em DDLs por exemplo, locks são feitos no Row Cache para que a alteração ocorra nos registros do Data Dictionary. Nesse momento, qualquer acesso que ocorra no objeto que está sendo modificado gera um wait event de row cache lock.

Existem algumas categorias de cache dentro do Data Dictionary Cache, que podem ser vistas através da view V$ROWCACHE, dentre outras informações:

  • CACHE#: id do Row Cache;
  • PARAMETER: categorias de cache contidas no Data Dictionary Cache. Cada categoria é prefixada por “dc_”;
  • GETS: número de requisições feitas sobre dados daquele tipo de cache;
  • GETMISSES: número de requisições que não encontrou o dado em memória e precisa executar uma physical read;
  • MODIFICATIONS: número de vezes que o Data Dictionary Cache foi atualizado;

Identificando o ofensor

As informações da V$ROWCACHE são utilizadas pelo AWR, na seção “Dictionary Cache Stats”:

Inclusive essa seção foi retirada do AWR do momento em que o row cache lock estava elevado no banco. Já podemos identificar que temos um número bastante elevado de modifications para o cache dc_sequences, o que nos dá uma pista sobre o que pode estar acontecendo.

Podemos confirmar que o lock acontece devido à essa categoria, olhando para os parâmetros do wait event, que podem ser vistos na view V$SESSION, enquanto o wait acontece, ou através da view DBA_HIST_ACTIVE_SESS_HISTORY (só use essa view caso tenha o licenciamento do Diagnostics Pack):

SQL> select distinct p1text, p1, p2text, p2, p3text, p3 
from DBA_HIST_ACTIVE_SESS_HISTORY 
where event='row cache lock' and SAMPLE_TIME > sysdate - 3;

P1_TEXT   P1        P2_TEXT   P2        P3_TEXT   P3 
--------- --------- --------- --------- --------- ---------
cache_id  13        mode      0         request   5

Agora como temos o cache_id, podemos consultá-lo na V$ROWCACHE:

select parameter from v$rowcache where cache#=13;

PARAMETER         
-----------------
dc_sequences

Conforme imaginamos, o problema está relacionado a sequences.

Para identificar a query impactada pelo wait, podemos utilizar as consultas:

SQL> SELECT 
    sql_id,
    COUNT(*) * 10 AS total_wait_time_ms, 
    MIN(sample_time) AS first_occurrence,
    MAX(sample_time AS last_occurrence
FROM dba_hist_active_sess_history
WHERE event = 'row cache lock'
AND sample_time BETWEEN TO_DATE('2025-03-05 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
                    AND TO_DATE('2025-03-05 10:00:00', 'YYYY-MM-DD HH24:MI:SS') --> alterar o intervalo de acordo com o horário do impacto
GROUP BY sql_id
ORDER BY total_wait_time_ms DESC
FETCH FIRST 10 ROWS ONLY;

SQL> SELECT sql_text 
FROM dba_hist_sqltext 
WHERE sql_id = '&sql_id'; --> repassar o SQL ID identificado na consulta anterior

A query impactada também pode ser vista no AWR, na seção “Top SQL with Top Events”.

Neste caso, identifiquei que a query impactada era um comando INSERT, e analisando a tabela onde os dados eram inseridos, identifiquei que a sua coluna de chave primária tinha seu valor sendo gerado por uma sequência. Essa sequência por sua vez estava sem cache configurado, o que estava originando o problema.

Conclusão

Basicamente o row cache lock ocorria toda vez que um insert era realizado na tabela, pois devido à falta de cache, o banco precisava realizar a geração do próximo valor de sequência e atualizar a sequência no dicionário de dados. Como diversas sessões estavam realizando a inserção dentro da mesma tabela ao mesmo tempo, e dependiam do valor da sequência, um enfileiramento aconteceu, gerando o wait event citado.

Em casos como esse a implementação de um cache na sequência com um tamanho adequado resolve o problema. Uma vez que a sequência tem um cache maior, o número de vezes que o dicionário de dados é atualizado será menor, ocorrendo sempre que o intervalo de valores que será armazenado em cache for gerado.

Recomendo a leitura das Doc Ids: Resolving Issues Where ‘Row Cache Lock’ Waits are Occurring (Doc ID 1476670.1) e Troubleshooting: “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! ” (Doc ID 278316.1), além da seção Configuring the Shared Pool, da documentação Performance Tuning Guide.

Grande abraço a todos!

Tag

Publicar comentário