terça-feira, 27 de dezembro de 2016

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



6 comentários:

japa disse...

Fazer alguns markhot de objetos na shared pool, nao poderia resolver parte do problema ?

Carlos Furushima

japa disse...

Outra duvida, os tais sql_id que sofrem constante purge na shared_pool, esses candidatos a purge é extraido da query do eDB360 ???

Alex Zaballa disse...

O markhot não resolveria esse caso, pois são milhoes de queries diferentes (SQL_IDs diferentes) que poderiam ser apenas uma com o uso de BIND Variables.

Alex Zaballa disse...

Sim, utilizei q query do eDB360, buscando as TOP 10.
Após isso faço FLUSH apenas desses SQLs que foram executados a mais de 60 minutos e tiveram apenas 1 execução.

Luis Santos disse...

Alex, qual parâmetro exato você usou no script sgastatx.sql, o do nosso guru Tanel Pöder?

Alex Zaballa disse...

Apenas %