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