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);
