A triste batalha entre um EXADATA X5 e a SHARED POOL

By Alex Zaballa

Olá pessoal,

Esta semana investiguei um problema em um cliente, onde ocorreu a queda de um dos bancos do Exadata por 2 vezes.

O Exadata em questão é um X5-2 quarter rack.

Essa foi a imagem que recebi da área de monitoramento com o ocorrido:

Apenas uma suspeita gerado no alert.log de uma das instances:

Analisando os principais eventos de contenção dos ultimos dias, encontrei:

Apesar de existirem diversos WAITS de ROW LOCK CONTENTION, resolvi partir direto para o problema de library cache lock, seguindo a pista do erro no alert.log.

Algo que me chamou a atenção, foi o banco ter 65GB como limite mínimo de SHARED POOL, apesar de ser uma base pequena de 700GB:

Usei esse seguinte script para verificar o uso da shared pool.

Para minha surpresa, 98% dos 65GB estavam em uso:

Para confirmar qual sub-pool da shared estava sendo mais utilizado, utilizei este script do Tanel.

Confirmado que é a área responsável por guardar os SQLs.

Dentro do eDB360 existe um script que coleta os SQLs que não estão usando BIND:

WITH
lit AS (
SELECT /*+ MATERIALIZE NO_MERGE */ /* 2a.152 */
       force_matching_signature, COUNT(*) cnt, MIN(sql_id) min_sql_id, MAX(SQL_ID) max_sql_id
  FROM gv$sql
 WHERE force_matching_signature > 0
   AND UPPER(sql_text) NOT LIKE '%EDB360%'
 GROUP BY
       force_matching_signature
HAVING COUNT(*) > 99
)
SELECT /*+ NO_MERGE */ /* 2a.152 */
       DISTINCT lit.cnt, s.force_matching_signature, s.parsing_schema_name owner,
       CASE WHEN o.object_name IS NOT NULL THEN o.object_name||'('||s.program_line#||')' END source,
       s.sql_text
  FROM lit, gv$sql s, dba_objects o
 WHERE s.force_matching_signature = lit.force_matching_signature
   AND s.sql_id = lit.min_sql_id
   AND o.object_id(+) = s.program_id
 ORDER BY
       1 DESC, 2;

Confirmadas minhas primeiras suspeitas, a aplicação não utliza variáveis BIND:

Nesse caso temos 2 alternativas:

1 – Corrigir a aplicação 🙂
2 – Utilizar o cursor_sharing=force (sim, podemos ter problemas de performance)

Como a solução 1 não é rápida, optei pela 2 para resolver emergencialmente o problema.
Para não afetar todo banco, criei uma trigger de logon para alterar o parâmetro em 2 usuários específicos:

–>

Para o USUARIO_1, o problema foi resolvido. Porém para o USUARIO_2, começaram a ocorrer problemas de performance em queries devido a mudança nos planos de execução.

Possíveis soluções:

1 – Utilizar SQL PLAN BASELINES –> Demorado

2 – Fazer FLUSH da SHARED POOL de tempos em tempos –> Fora de cogitação

3 – Criar uma rotina para fazer o FLUSH de SQLs específicos –> Essa opção que eu utilizei

Utilizei como base esse script do Carlos Sierra:

Problema resolvido e o cliente feliz 🙂

P.S. Lembrei de um artigo que publiquei no GPO 🙂

Fonte: Alex Zaballa