Performance Sequence Oracle 12c - Cláusula CACHE

Pessoal, como vai?

Em uma sequence Oracle a cláusula CACHE prealoca um conjunto de números de seqüência e os mantém na memória, na Área Global do Sistema (SGA), para que os números de seqüência possam ser acessados mais rapidamente do que eles podem ser lidos do disco.

Quando o último dos números de seqüência no cache for usado, o banco de dados lê outro conjunto de números e aloca-o no cache novamente.

Se seus aplicativos usam muitas seqüências simultaneamente, seu cache de seqüência pode não ser grande o suficiente para armazenar todas as seqüências. E nesse caso, o acesso a números de seqüência pode muitas vezes exigir leituras de disco. Neste cenário precisa ou pode ser ajustado!

Quando utilizado um cache 20 por exemplo, na instrução CREATE SEQUENCE, o Oracle cria a sequência para que 20 valores da seqüência sejam armazenados no cache.

Os primeiros 20 valores da sequencia podem então ser lidos do cache. Quando o 21º valor é acessado, os próximos 20 valores serão lidos a partir do disco.

O banco de dados pode pular números de seqüência se você optar por armazenar em cache um conjunto de números de seqüência. Por exemplo, quando uma instância é anormalmente desligada (por exemplo, quando ocorre uma falha de instância ou uma instrução SHUTDOWN ABORT é emitida), os números de seqüência que foram armazenados em cache mas não utilizados são perdidos.

Também, os números de seqüência que foram usados mas não salvos também são perdidos. O banco de dados também pode ignorar os números de seqüência em cache após uma exportação e importação.

Vamos fazer um teste de performance comparando três exemplos! Primeiro criamos uma sequence com a opção NOCYCLE que é Padrão. Ela indica que a seqüência não pode gerar mais valores após atingir seu valor máximo. Já a opção CYCLE indica que a sequencia será recomeçada ao atingir o valor maximo.

Usamos também a opção NOCACHE na instrução CREATE SEQUENCE, assim, os valores da seqüência não serão armazenados no cache de seqüência do banco. Neste caso, cada acesso à seqüência requer leitura de disco.

Connected to Oracle Database 12c Standard Edition Release 12.1.0.2.0 
Connected as prod

SQL> SET serveroutput ON
SQL> 
SQL> CREATE SEQUENCE SEQ_01 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START
2    WITH 1 NOCACHE NOCYCLE;

Sequence created

SQL> 
SQL> DECLARE
2    T     NUMBER := DBMS_UTILITY.GET_TIME();
3    V_SEQ NUMBER;
4  BEGIN
5  
6    FOR I IN 1 .. 99999 LOOP
7      SELECT SEQ_01.NEXTVAL INTO V_SEQ FROM DUAL;
8    END LOOP;
9  
10    DBMS_OUTPUT.PUT_LINE('hsecs=' || TO_CHAR(DBMS_UTILITY.GET_TIME() - T));
11  END;
12  /

hsecs=2394

PL/SQL procedure successfully completed

Agora criamos uma sequence com um cache de 20 e executamos o bloco com loop novamente:

SQL> 
SQL> CREATE SEQUENCE SEQ_02 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START
2    WITH 1 CACHE 20 NOCYCLE;

Sequence created

SQL> 
SQL> DECLARE
2    T     NUMBER := DBMS_UTILITY.GET_TIME();
3    V_SEQ NUMBER;
4  BEGIN
5  
6    FOR I IN 1 .. 99999 LOOP
7      SELECT SEQ_02.NEXTVAL INTO V_SEQ FROM DUAL;
8    END LOOP;
9  
10    DBMS_OUTPUT.PUT_LINE('hsecs=' || TO_CHAR(DBMS_UTILITY.GET_TIME() - T));
11  END;
12  /

hsecs=427

PL/SQL procedure successfully completed

E por fim, criamos uma sequence com um cache de 1000 e executamos o bloco com loop novamente:

SQL> 
SQL> CREATE SEQUENCE SEQ_03 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START
2    WITH 1 CACHE 1000 NOCYCLE;

Sequence created

SQL> 
SQL> DECLARE
2    T     NUMBER := DBMS_UTILITY.GET_TIME();
3    V_SEQ NUMBER;
4  BEGIN
5  
6    FOR I IN 1 .. 99999 LOOP
7      SELECT SEQ_03.NEXTVAL INTO V_SEQ FROM DUAL;
8    END LOOP;
9  
10    DBMS_OUTPUT.PUT_LINE('hsecs=' || TO_CHAR(DBMS_UTILITY.GET_TIME() - T));
11  END;
12  /

hsecs=310

PL/SQL procedure successfully completed

SQL> 
SQL> DROP SEQUENCE SEQ_01;

Sequence dropped
SQL> DROP SEQUENCE SEQ_02;

Sequence dropped
SQL> DROP SEQUENCE SEQ_03;

Sequence dropped

Resultado: Se compararmos o tempo de processamento dos três testes, os testes que utilizaram um cache de 20 e 1000 foram os mais rápidos. Porém, a diferença de tempo de processamento utilizando cache 20 e 1000 não é muito. Neste cenário um cache padrão talvez seja o suficiente.

Abraço,

Ronaldo

fonte: https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm