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)

   - Oracle Database Online Documentation Library, 12c Release 1 (12.1.0.2)
   - Streams Pool is only for Streams? Think Again!