Páginas

12 de jul. de 2019

Export demorando muito mais do que o normal, como resolver?

 

Olá pessoal,

     Há alguns meses atrás tive problemas com o tempo de geração de um dump full de um dos BDs de Produção que administro, que aumentou de 1h hora para muitas horas... e precisei investigar o que estava ocorrendo.

     O dump tinha o tamanho aproximado de 170 GB e era gerado diariamente, com uma duração média de 60 minutos. Em um determinado dia percebi que ele passou a demorar muitas horas (não sei quanto tempo ele levaria para terminar, pois o job dele era encerrado automaticamente após passar 9h de execução), e isso persistiu por alguns dias, até o momento em que consegui pegar esse problema para analisar e tentar resolvê-lo. A duração média do job que faz a geração de todos os dumps de BDs de Produção leva em média um pouco mais de 4h, mas quando o problema começou, o dump de apenas 1 dos BDs estava demorando quase o dobro desse tempo, e ele ficava na maior parte da sua execução parado no wait event "Streams AQ: Enqueue Blocked On Low Memory".

     Para resolver o problema, comecei então a pesquisar no MOS pelo referido wait event e cheguei nos docs "Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory" (Doc ID 2386566.1)" e "Bug 27634991 : EXPDP FREQUENTLY WAITS ON 'STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY", que indicavam problemas no gerenciamento da "Streams Pool" quando o gerenciamento de memória automático da SGA está habilitado. Segundo o primeiro doc, a lentidão ocorre quando há uma carga na Buffer Cache e uma parte da área de memória da "Streams Pool" está sendo movida para ela. Quando isso está ocorrendo, o retorno do SQL abaixo é igual a 1:

SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
1

     Quando após executar o SQL acima várias vezes, o retorno for consistentemente igual a 1 (por um longo período), isso é um indício de que teremos o problema relatado (lentidão na geração dos dumps), portanto, para resolvê-lo, execute o comando abaixo para forçar um shrink completo na Streams Pool:
SQL> connect / as sysdba
SQL> alter system set events 'immediate trace name mman_create_def_request level 6';
     
     Após executar este comando o meu problema foi resolvido, e o tempo de geração dos dumps voltou ao normal! No momento em que o dump full estava sendo executado não havia carga na buffer cache, como diz a nota do MOS. Me parece que no meu caso a Streams Pool estava fragmentada. O tamanho dela não parecia estar pequeno, pois antes de executar o comando acima eu aumentei manualmente o tamanho dela, diminui depois, e deixei no automático de novo, e vi que isso não fez diferença alguma em vários testes que fiz mesmo após tudo voltar ao normal.

     Antes de finalizar o artigo, é importante ressaltar que a "Streams Pool" é uma subdivisão da SGA utilizada normalmente por diversas ferramentas (Oracle GoldenGate, XStream, Oracle Streams, Oracle Advanced Queuing e Oracle Data Pump), sendo a principal delas, o Data Pump. Quando a SGA da instância está configurada com algum tipo de gerenciamento de memória automático (AMM ou ASMM), a Streams Pool (ver parâmetro STREAMS_POOL_SIZE) é criada com um valor que corresponde (no momento da sua criação) a 10% do tamanho da Shared Pool, e esse espaço alocado para ela é decrementado da Buffer Cache.
   
     Para ver o tamanho total da SGA e de cada subdivisão dela (incluindo a Streams Pool), execute o SQL abaixo:

 select             name,
                    mb as mb_total,
                    nvl(inuse,0) as mb_used,
                    round(100 - ((nvl(inuse,0) / mb) * 100),2) "perc_mb_free"                    
            from  (
                  select   name, 
                          round(sum(mb),2) mb, 
                          round(sum(inuse),2) inuse        
                  from (
                          select case when name = 'buffer_cache' then 'buffer cache'
                                       when name = 'log_buffer'   then 'log buffer'
                                      else pool                     
                                  end name,                      
                                  bytes/1024/1024 mb,
                                  case when name = 'buffer_cache'
                                        then (bytes - (select count(*) 
                                                       from v$bh where status='free') *
                                                      (select value 
                                                      from v$parameter 
                                                      where name = 'db_block_size')
                                              )/1024/1024
                                      when name <> 'free memory'
                                            then bytes/1024/1024
                                  end inuse
                          from    v$sgastat
                        )
                WHERE     NAME is not null
                group by  name
            )
            UNION ALL    
            select      'SGA',
                        round(sum(bytes)/1024/1024,2),
                        (round(sum(bytes)/1024/1024,2)) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2),
                        round((sum(decode(name,'free memory',bytes,0))/sum(bytes))*100,2)                        
            from        v$sgastat;
            

     Veja o resultado na imagem abaixo, cuja execução ocorreu em um BD de testes (com AMM habilitado), logo após fazer o startup da instância e gerar um dump do famoso schema HR. Repare que o tamanho da "Streams Pool" corresponde a exatos 10% do tamanho da Shared Pool:




Referências:
   - Bug 27634991 - Datapump Frequently Waits On 'Streams AQ: enqueue blocked on low memory' (Doc ID 27634991.8)
   - Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory" (Doc ID 2386566.1)

9 comentários:

  1. Grande Fábio sempre salvando a lavoura !!!!
    Estava com o mesmo problema, Backup passou de 35min para 5 horas.
    Valeu por compartilhar.

    Abraço.

    ResponderExcluir
  2. Muuito bom, salvou aqui também, dump de uma base vazia estava demorando mais de uma hora.

    ResponderExcluir
  3. Boa tarde Fábio,

    Esse procedimento também funciona no Oracle 11.2.0.2 ?

    ResponderExcluir
    Respostas
    1. Segundo o doc ID 2386566.1 citado nas referências, ele afeta as versões Version 11.2.0.4 e posteriores.

      Excluir
  4. eu tenho RAC 11.2.0.2 com dois nodes, que não consigo mais fazer expdp, fica parado na parte de EXPORT INDEX.

    ResponderExcluir
    Respostas
    1. Ok Henrique, veja os detalhes das mensagens sendo geradas no log do export e procure por ajuda no MOS (preferencialmente) ou google.

      []s

      Excluir
  5. Boa tarde Fabio tudo bem?

    Estou com este mesmo problema em uma instancia, porem fiz a consulta
    "select shrink_phase_knlasg from X$KNLASG;" e esta me retornando "0".
    Essa consulta deve ser feita com o exp rodando?

    A consulta da query;

    NAME MB_TOTAL MB_USED PERC_MB_FREE
    shared pool 3328 2696,04 18,99
    buffer cache 35968 35953,78 0,04
    streams pool 256 0,05 99,98
    large pool 384 216 43,75
    log buffer 71,02 71,02 0
    java pool 256 0 100
    SGA 40777,13 39465,22 3,22

    GRATO.

    ResponderExcluir