Social Icons

21 de set. de 2010

Otimizando SQL com cache em Oracle Sequences


ATUALIZADO EM 29/06/2015

Olá pessoal,

     Este artigo tem como objetivo demonstrar a performance do recurso de cache em SEQUENCES utilizando um Banco de Dados Oracle 10G. Para isso, criaremos uma sequence de 3 formas diferentes (sem cache, com cache de 20 e com cache de 1000) e iremos comparar o tempo de recuperação de valores da sequence em cada uma delas. A comparação será feita através da execução de um bloco PL/SQL, chamado "TESTA SEQUENCE", que irá recuperar 99999 valores da sequence e exibir a data/hora do início e fim de sua execução.

-- início bloco PL/SQL "TESTA SEQUENCE"
DECLARE
  V_TIME_A VARCHAR2(20);
  V_TIME_D VARCHAR2(20);
  V_COUNT NUMBER;
BEGIN
    DBMS_OUTPUT.ENABLE(NULL);
   
    SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_TIME_A
    FROM DUAL;
 
    FOR I IN 1..99999
    LOOP
        SELECT SEQ_TESTE.NEXTVAL INTO V_COUNT
        FROM DUAL;
        --DBMS_OUTPUT.PUT_LINE('VALOR ATUAL DA SEQUENCE ''SEQ_TESTE'' É : ' || V_COUNT);               
    END LOOP;
   
    SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_TIME_D
    FROM DUAL;  
   
    DBMS_OUTPUT.PUT_LINE('HORA INÍCIO DO PROCESSO: ' || V_TIME_A);               
    DBMS_OUTPUT.PUT_LINE('HORA FIM DO PROCESSO: ' || V_TIME_D);    
end;
-- fim bloco PL/SQL "TESTA SEQUENCE"

    Antes de iniciar os testes dos procedimentos abaixo habilite o recurso para exibir mensagens de DBMS_OUTPUT na ferramenta (SQL Plus, SQL Developer etc.) que você for utilizar e apague a sequence criada no procedimento anterior (DROP SEQUENCE SEQ_TESTE;).

     
1- SEQUENCE SEM CACHE
   a) Crie a sequence:
     CREATE SEQUENCE SEQ_TESTE MINVALUE 1 MAXVALUE 9999999999999999999999999999
          INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE;
 
   b) Execute o bloco PL/SQL "TESTA SEQUENCE";
  
   c) Calcule o tempo de duração subtraindo hora fim e hora início das mensagens exibidas pelos comandos DBMS_OUTPUT.PUT_LINE;
  
  
2- SEQUENCE COM CACHE DE 20 (valor padrão)
   
    a) Crie a sequence:
     CREATE SEQUENCE SEQ_TESTE MINVALUE 1 MAXVALUE 9999999999999999999999999999
          INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;
 
   b) Execute o bloco PL/SQL "TESTA SEQUENCE";
  
   c) Calcule o tempo de duração subtraindo hora fim e hora início das mensagens exibidas pelos comandos DBMS_OUTPUT.PUT_LINE;
  
  
3- SEQUENCE COM CACHE DE 1000
   
    a) Crie a sequence:
     CREATE SEQUENCE SEQ_TESTE MINVALUE 1 MAXVALUE 9999999999999999999999999999
          INCREMENT BY 1 START WITH 1 CACHE 1000 NOCYCLE;
 
   b) Execute o bloco PL/SQL "TESTA SEQUENCE";
  
   c) Calcule o tempo de duração subtraindo hora fim e hora início das mensagens exibidas pelos comandos DBMS_OUTPUT.PUT_LINE;
  

    Em testes que eu fiz, eu obtive os seguintes resultados:       
        - Teste 1 (sem cache):                demorou 20 segundos;
        - Teste 2 (com cache de 20):      demorou 3 segundos;
        - Teste 3 (com cache de 1000):  demorou 2 segundos.


CONCLUSÃO:
     O uso de CACHE otimiza o desempenho de recuperação de valores em SEQUENCES. Quanto mais valores em cache normalmente maior será o ganho de performance, porém a quantidade de valores em cache não é proporcional ao percentual de ganho de desempenho.
   
     
OBSERVAÇÕES:
     Devemos tomar cuidado com a qtde. de valores em cache, pois uma valor muito alto pode não proporcionar um ganho de desempenho tão relevante comparando-se com outros valores menores, além do risco de se perder números sequenciais que estavam em cache no caso de ocorrer um "crash" no BD ou quando os valores em cache são "limpados" da Shared Pool (pelo algoritmo LRU etc).

      De acordo com os testes que eu fiz nos exemplos deste artigo, eu criaria uma sequence com cache de 20, pois com cache de 1000 o ganho não foi tão relevante a ponto de se correr os riscos que citei no parágrafo anterior. 

    Para minimizar a perda de valores das sequences ao utilizar cache, fixe-as ("pinando-as") na Large Pool executando o comando abaixo, substituindo o nome do schema e sequence desejados:
          DBMS_SHARED_POOL.KEEP('SCHEMA_NAME.SEQUENCE_NAME','Q');


     Para mais detalhes e sobre essa e muitas outras dicas sobre otimização de instruções SQL, participe dos treinamentos de SQL Tuning: presenciais ou videoaulas.


Por hoje é só!

[]s
  

16 comments:

  1. Bom dia.

    Como eu faço, dentro do plsql, para saber qual o valor do cache da sequence?

    Obrigado
    Lesser

    ResponderExcluir
  2. Lesser, consulte a visão ALL_SEQUENCES. Nela aparecerão todas as sequences (e seus detalhes) que o usuário que vc se conectou no BD possuiu ou tem privilégios de SELECT.

    []s

    ResponderExcluir
  3. Estou com um problema de perda de valores usando o cache como 20, de tal forma que existem 800.000 registros na base e a sequence chegou a 2.000.000, tem um forma de utilizar o cache e n ão se perder valores?

    ResponderExcluir
    Respostas
    1. Não há como garantir que vc não perca valores usando sequences. Em toda sequence vc corre o risco de perder valores sequencial, principalmente em aplicações em que ocorrem muitos ROLLBACKS. Toda vez que vc usar o número de uma sequence e fizer um ROLLBACK em seguida vc vai queimar o número daquela sequence , ok?

      Para apenas minimizar a perda de valores, principalmente se na instância do BD ocorrem muitos shutdowns, faça o que está escrito no último parágrafo do artigo!

      []s

      []s

      Excluir
  4. Olá Fábio,

    Parabéns pelo artigo, muito bom mesmo. Mas fiquei com um dúvida, se nesse caso pode ocorrer perda de sequencia, como posso identificar essa perda, pois em uma sequencia muito grande não tenho como olhar linha a linha, existe alguma forma de localizar isso?

    ResponderExcluir
    Respostas
    1. Anônimo, não somente neste caso, mas em qq caso há risco de perder o número de sequences. Não existe nada automatizado que faça essa identificação de perda dos números não utilizados para vc. Para identificá-los sugiro criar um bloco PL/SQL utilizando um cursor que varra a tabela inteira sequencialmente pelo número da PK, compare os valores das PKs da linha atual e anterior e te mostre onde existem os gaps, ok?

      []s

      Excluir
    2. Olá Fábio,

      Muito obrigado, entendi, porém fazer esse bloco PL/SQL já é outro problema, não tenho muita experiência, até entendi como deve ser feito mas fazer, rsrsrsr.

      Excluir
  5. Se puder me ajudar com isso.

    ResponderExcluir
    Respostas
    1. Como foge um pouco do escopo do artigo, deixe por favor sua questão no fórum do site http://www.profissionaloracle.com.br/gpo/ e me avise q depois eu respondo por lá, ok?

      Excluir
    2. certo, mais uma vez obrigado

      Excluir
  6. Sem cache perco uma sequence por vez, agora com cache 20, perco ate 19 sequences por vez, pelo que percebi quando um usuario da um novo na aplicação usando o cache,são reservadas a quantidade de cache em sequencias para este usuario ate ele fechar a seção, se outro usuario da um novo ele pega a sequencia a partir da sequencia final reservada pelo usuario anterior, se o usuario anterior fechar a seção apos a primeira inserção perco 19 sequencias e por ai vai.

    ResponderExcluir
    Respostas
    1. Para não perder os valores do cache veja o que escrevi no último parágrafo.

      Quanto ao que você disse sobre o 2o usuário pegar o valor final reservado, isso está errado! O valor do cache é compartilhado na SGA, e isso garante que ele seja sempre sequencial. Um comportamento um pouco diferente pode ocorrer em ambientes com RAC... mas aí é uma longa história.. e mesmo em RAC também dá para garantir que os valores sejam sempre sequenciais com a cláusula ORDER (http://www.dba-oracle.com/t_rac_tuning_sequence_order_parameter.htm), mas isso pode prejudicar a performance do ambiente.

      Excluir
  7. Fabio Prado, obrigada pelo excelente artigo. Me esclareceu sobre o problema de perder sequence.... Outra coisa que ocorre no meu cliente, é de um sequence inferior ( dentro dos 20 ) aparecer depois de um sequence superior... Estranho né.. mas foi uma observação minha, o cliente não relatou problema algum.

    ResponderExcluir
    Respostas
    1. Um número inferior ser utilizado ocorre em ambientes RAC. Para evitar isso (com o custo de piorar um pouco desempenho) deve-se criar as sequences nesses ambientes (quando realmente houver necessidade) com a cláusula ORDER.

      []s

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)