A triste batalha entre um EXADATA X5 e a SHARED POOL

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:

CREATE OR REPLACE TRIGGER SYSTEM.LOGIN_SHARED

AFTER LOGON ON DATABASE

BEGIN

  IF USER in ('USUARIO_1','USUARIO_2')  

  THEN

    EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');

  END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

-->

/

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