Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


terça-feira, 3 de dezembro de 2019

Oracle - Wait event Library cache: mutex X

Por Eduardo Legatti

Olá,

Recentemente eu estava investigando um incidente na qual várias sessões no banco de dados simplesmente paravam em um wait específico e praticamente todo o banco de dados congelava e precisava ser reiniciado com "shutdown abort". Esse sintoma vinha ocorrendo com uma certa frequência em um ambiente específico na qual muitos sistemas compartilhavam os mesmos SQLs através de schemas distintos no banco de dados. Pelos gráficos abaixo identifiquei que os eventos estavam associados a wait class Concurrency, especificamente de waits Latch/mutex.






Para verificar qual objeto estava causando o evento de mutex (Tabela, índice, package, procure, SQL Cursor), identifiquei que sempre estava relacionado ao SQL_ID fnuugzjw1014k conforme demonstrado abaixo:

SQL> select a.cursors, a.sql_id,b.sql_text
  2  from
  3  (
  4    select count(*) as cursors, ssc.sql_id
  5    from v$sql_shared_cursor ssc
  6    group by ssc.sql_id
  7    order by cursors desc
  8  ) a,
  9  (select sa.sql_id, sa.sql_text, sa.parsing_schema_name from v$sqlarea sa) b
 10  where a.sql_id=b.sql_id
 11  and rownum<=5;

             CURSORS SQL_ID        SQL_TEXT
-------------------- ------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
               88022 fnuugzjw1014k select * from (select D0.ain_code from t1 D0 where D0.ain_id = :1 and D0.ain_astcode = :2) where rownum <= :3
                6961 dsdny6fnhwjj7 select * from (select D0.gei_code from t2 D0, t3 D1 where D0.gei_gehcode = D1.geh_code and D1.geh_gelcode = :1) where rownum <= :3
                5134 5s3p206y7uxjp select count(1) as n from t2 D0, t3 D1 where D0.gei_gehcode = D1.geh_code and D1.geh_gelcode = :1 and D0.gei_pincode = :2
                3455 3am62hr7b3u75 select D0.elg_code from t0 D0 where D0.elg_eobcode = :1 and D0.elg_objpkvalue = :2 order by D0.elg_date
                3412 33f16tj5ukzzt select * from (select rownum sysRowNum, r.* from (select D0.gei_code from t2 D0, t3 D1 where D0.gei_gehcode = D1.geh_code) r) where sysRowNum between :3 and :4

O que me chamou a atenção é que as instruções SQL utilizam bind variables. Se tivessem usando literais e até entenderia o problema. Pelo que entendi, toda vez que o Oracle tenta utilizar o cursor na library cache para analisar o plano, o Oracle cria um lock (latch) no mesmo até terminar a análise da instrução. Como essa mesma instrução estava sendo chamada várias vezes de forma concorrente, criou-se uma fila muito grande. Talvez se o servidor tivesse mais memória (SGA), o problema não ocorreria com tanta frequência, mas é apenas uma suposição.

De qualquer forma estudando o assunto, descobri que a partir da versão Oracle 11g (11.2.0.2) foi criar uma procedure dbms_shared_pool.markhot que consegue marcar o objeto que está sofrendo latch/mutex de forma que o mesmo seja clonado várias vezes com SQL_ID diferentes de forma a diminuir a contenção em um único cursor. Desta forma executei o comando abaixo para achar o FULL_HASH_VALUE do SQL_ID fnuugzjw1014k.


SQL> SELECT hash_value,
  2         full_hash_value,
  3         namespace
  4    FROM v$db_object_cache
  5   WHERE name LIKE '%select * from (select D0.ain_code from t1 D0 where D0.ain_id = :1 and D0.ain_astcode = :2) where rownum <= :3%'
  6     AND rownum<=1;

          HASH_VALUE FULL_HASH_VALUE                  NAMESPACE
-------------------- -------------------------------- ---------
          2014315666 35e89608ac8d712eea6b4ffc78100492 SQL AREA

A partir do FULL_HASH_VALUE será possível marcar este objeto como "hot" através da procedure dbms_shared_pool.markhot de forma que o Oracle possa criar várias cópias desta instrução SQL na library cache com SQL_ID distintos a fim de diminuir essa contenção. Para isso, será necessário apenas executar o comando abaixo. No mais, vale a pena salientar que esse procedimento deverá ser executado toda vez que a instância for reiniciada.

SQL> exec dbms_shared_pool.markhot(hash =>'35e89608ac8d712eea6b4ffc78100492', namespace =>0);



3 comentários:

Tremilioso disse...

Parabéns pelo artigo.
Me ajudou num problema semelhante que estou enfrenando.
Gostaria de saber qual a ferramento que vc usou para obter esses gráficos de fundo preto?

Eduardo Legatti disse...

Olá,

Eu utilizo o Zabbix e crio os plugins de monitoramento.

Abraços,

Legatti

Time Lord disse...

Olá foi bem útil.

Apenas um adendo:
"Talvez se o servidor tivesse mais memória (SGA)"
Tenho um banco com 250Gb na SGA, que no momento não estava usando nem metade disso e mesmo assim o enfileiramento ocorria.

No meu caso é um banco que suporta o SAP, que também utiliza bind variables, e o plano de execução está com um hint de index. O problema é que o usuário soltou a execução com alguns LIKES, e isto invalidou o plano de execução forçado via baseline.

Acredito, que desta forma o oracle teve que refazer o plano para este caso.
Não contente o usuário soltou diversas execuções da consulta.

Postagens populares