Abordando o uso de sequências no Oracle em um ambiente RAC ...

Olá,

Para quem se recorda do artigo de Novembro/2008 - Coluna do tipo auto-incremento no Oracle? Abordando o uso de seqüências no Oracle, viu como criar e utilizar o objeto sequence no Oracle. Como informação adicional, recentemente realizei alguns testes com diferentes tamanhos de cache (em um ambiente single instance) como demonstrado abaixo:

SQL> set timing on;
SQL> begin
  2   for i in 1..10000000 loop
  3     insert into t1 select seq_teste_nocache.nextval from dual;
  4     if mod(i, 10000) = 0 then
  5        commit;
  6     end if;
  7   end loop;
  8   commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:24:10.22 (24 minutos e 10 segundos)


SQL> set timing on;
SQL> begin
  2   for i in 1..10000000 loop
  3     insert into t1 select seq_teste_cache20.nextval from dual;
  4     if mod(i, 10000) = 0 then
  5        commit;
  6     end if;
  7   end loop;
  8   commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:14.10 (5 minutos e 14 segundos)

SQL> set timing on;
SQL> begin
  2   for i in 1..10000000 loop
  3     insert into t1 select seq_teste_cache1000.nextval from dual;
  4     if mod(i, 10000) = 0 then
  5        commit;
  6     end if;
  7   end loop;
  8   commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:04:26.88 (4 minutos e 26 segundos)

SQL> set timing on;
SQL> begin
  2   for i in 1..10000000 loop
  3     insert into t1 select seq_teste_cache10000.nextval from dual;
  4     if mod(i, 10000) = 0 then
  5        commit;
  6     end if;
  7   end loop;
  8   commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:04:25.89 (4 minutos e 25 segundos)


SQL> set timing on;
SQL> begin
  2   for i in 1..10000000 loop
  3     insert into t1 select seq_teste_cache50000.nextval from dual;
  4     if mod(i, 10000) = 0 then
  5        commit;
  6     end if;
  7   end loop;
  8   commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:04:25.80 (4 minutos e 25 segundos)

Perceberam a diferença na performance das execuções? Uma sequence criada com NOCACHE teve o pior desempenho, isso porque a cada valor recuperado da sequence o Oracle precisa atualizar o dicionário de dados com o próximo valor e isso gera uma overhead de I/O absurda. No mais, segundo os testes acima, não haverão muitos benefícios se utilizarmos cache maiores que 50000. Portanto, no geral o ideal seria ter um cache entre 1000 e 10000 para efeitos de boa performance. Vale a pena avaliar!

No mais, o objetivo deste artigo é demonstrar que em um ambiente RAC, os caches das sequences são armazenados em cada nó (node) do cluster e que existe diferença se a mesma é criada com a cláusula ORDER ou NOORDER. Por padrão, as sequences são criadas como NOORDER. Existem algumas desvantagens (uma overhead a mais) em utilizar a cláusula ORDER em um ambiente RAC, pois os valores das sequências precisam ser sincronizados entre os nós do RAC através de um serviço global de cache e, neste caso, acontece um evento chamado "GLOBAL LOCK". No artigo de Christo Kutrovsky, existe uma demonstração de como os valores das sequências são retornados quando a mesma é criada com a cláusula ORDER e NOORDER:

sequência com NOORDER - cache (20)
---------------------------------
Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

sequência com ORDER - cache (20)
--------------------------------
Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106

Perceberam a diferença? Em um ambiente RAC, quando a sequence é criada com a cláusula NOORDER, os caches das mesmas são armazenados e gerenciadas em cada nó (node) do cluster. Abaixo está a demonstração de um teste prático utilizando cláusula ORDER na criação de uma sequence.

SQL> create sequence seq_teste_rac cache 50 order;

Seqüência criada.

======
NODE-A
======

SQL> select seq_teste_rac.nextval from dual;

NEXTVAL
----------
         1

SQL> /

NEXTVAL
----------
         2

SQL> /

NEXTVAL
----------
         3

SQL> /

NEXTVAL
----------
         4

======
NODE-B
======

SQL> select seq_teste_rac.nextval from dual;

NEXTVAL
----------
         5

SQL> /

NEXTVAL
----------
         6

SQL> /

NEXTVAL
----------
         7

SQL> /

NEXTVAL
----------
         8

Portanto, dependendo da aplicação em uso, vale a pena checar se os objetos sequences estão criados com o tamanho dos caches de forma apropriada e se as mesmas deverão ser ORDER ou NOORDER, de acordo com o objetivo da mesma. Em um ambiente RAC isso pode fazer uma grande diferença.

Conclusão:

1- NOCACHE é ruim para a performance.
2- Em um ambiente RAC, ORDER/NOORDER faz diferença.

No geral, para a performance, abaixo estão as combinações das cláusulas combinadas (da pior para a melhor)

* NOCACHE
* CACHE com ORDER (em um ambiente RAC)
* CACHE com NOORDER (em um ambiente RAC)

Observação: Em um ambiente single instance, uma sequence criada com ORDER ou NOORDER não fará a menor diferença, pois os números são sempre gerados ordenadamente.

"ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order."