[Resolvido] Alterando comportamento de transações no Oracle Datab

Um dos conceitos mais comuns no Oracle Database é que uma transação não está definitivamente concluída enquanto estiver gravada nos Online Redo Logs. Se um COMMIT é executado, então pode-se dizer que a transação foi efetivamente concluída e todas as alterações foram escritas nos Online Redo Logs garantindo assim que os dados possam ser recuperados em caso de desastre ou recover.

A verdade é que este comportamento pode ser alterado desde a versão 10gR2.

Com o recurso "Asynchronous Commit" pode-se alterar a velocidade de uma transação, assim como a "confiança" em dados alterados pela mesma. O recurso "Asynchronous Commit" oferece um throughput maior para transações com muitas execuções simultâneas ou frequentes. Este recurso permite que as transações sejam concluídas de maneira rápida, otimizando tempo de execução e ainda eventos de Wait como "Log File Sync e Log File Parallel Write"

Entendendo o comportamento de um COMMIT

Uma transação qualquer normalmente é iniciada por uma aplicação ou usuário. Durante a execução de uma transação, são geradas alterações de dados e consequentemente alterações em buffer (memória). Esta área de memória é definida através do parâmetro log_buffer. Quando um usuário ou aplicação executa um COMMIT, o Oracle imediatamente grava os dados armazenados em buffer (memória) para disco (redo log files) juntamente com os dados de redo para o commit. Enquanto este processo não for completamente concluído, (todos os dados estejam gravados nos Online Redo Log Files) o Oracle não irá "liberar" a sessão.

Para maiores informações, veja o artigo: Entendendo os Eventos de Espera "Log File Sync e Log File Parallel Write"/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47339.jpg&key=193538a81fbb174527627ccea1e10daa4be67fd750b0b1225cd5340d2fe5e432" alt="47339.jpg" />

Como demonstrado na figura acima, é possível configurar o comportamento do processo Log Writer, permitindo assim o Oracle Database otimizar e diminuir o tempo de gravação das informações de uma transação nos Online Redo Log Files.

Alterando o comportamento de um COMMIT

Pode-se alterar o comportamento de uma instrucão COMMIT de 2 Formas:

1. Opções através do próprio comando COMMIT

Basta neste caso, apontar a opção desejada.

As opções possíveis são:

• IMMEDIATE WAIT
• IMMEDIATE NOWAIT
• BATCH WAIT
• BATCH NOWAIT

/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47340.png&key=45da5e2572aab24f4efa9b4bd7f7d7d7249254bfda5243fcc8f37a558c1cd11e" alt="47340.png" />

2. Alterando a sessão ou o Ambiente

Via sessão, basta utilizar o comando ALTER SESSION.

No Oracle 10gR2:

As opções possíveis são:

• IMMEDIATE WAIT
• IMMEDIATE NOWAIT
• BATCH WAIT
• ATCH NOWAIT

ALTER SESSION SET COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'A partir do Oracle 11.1, este parâmetro ficou obsoleto, sendo dividido em 2 novos parâmetros:

• COMMIT_WAIT
• COMMIT_LOGGING

ALTER SESSION SET COMMIT_WAIT = { NOWAIT | WAIT | FORCE_WAIT }

ALTER SESSION SET COMMIT_LOGGING = '{IMMEDIATE | BATCH}'Via sistema, basta utilizar o comando ALTER SYSTEM.

No Oracle 10gR2:

As opções possíveis são:

• IMMEDIATE WAIT
• IMMEDIATE NOWAIT
• BATCH WAIT
• BATCH NOWAIT

ALTER SYSTEM SET COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'A partir do Oracle 11.1, este parâmetro ficou obsoleto, sendo dividido em 2 novos parâmetros:

• COMMIT_WAIT
• COMMIT_LOGGING

ALTER SYSTEM SET COMMIT_WAIT = { NOWAIT | WAIT | FORCE_WAIT }

ALTER SYSTEM SET COMMIT_LOGGING = '{IMMEDIATE | BATCH}'

Considerações importantes no Oracle 11g (R1 e/ou R2)

• Se o parâmetro COMMIT_WAIT for definido como FORCE_WAIT, então o padrão (WAIT) será utilizado. Se este for definido como parâmetro de sistema (ALTER SYSTEM) ou parâmetro de sessão (ALTER SESSION) então então as opções via comando (Transação) serão desconsideradas.
• O parâmetro COMMIT_WRITE foi mantido apenas para compatibilidade.
• Os parâmetro COMMIT_WAIT e COMMIT_LOGGING têm precedência sobre o parâmetro COMMIT_WRITE.
• Se o parâmetro COMMIT_LOGGING for alterado após definido o parâmetro COMMIT_WAIT para FORCE_WAIT, então a opção FORCE_WAIT será desconsiderada.

Demonstração de performance

Para exemplificar os benefícios, segue abaixo um exemplo (teste) de utilização.

1 - Criar Tabelas de controle

create table commit_write_to (

 

 col_1 number,

 

 col_2 varchar2(500)

 

);

 

create table commit_write_from (

 

 col_1 number,

 

 col_2 varchar2(500)

 

);

2 - Inserir Valores Iniciais

begin

for r in 1 .. 5000 loop

 insert into commit_write_from values (

 

 r,

 

 dbms_random.string('a', 500)

 

 );

 

 end loop;

 

end;

/

3 - Criar tabelas auxiliares para Eventos

create table event_pre as select * from v$session_event where rownum = 0;

 

create table event_post as select * from v$session_event where rownum = 0;

4 - Criar procedure para executar commits

create or replace procedure many_commits as

 start_time number;

 

 end_time number;

 

 my_sid number;

 

 

 

 num_rows number := 5;

begin

 select sid into my_sid

 

 from sys.v_$session

 

 where audsid = sys_context('userenv', 'sessionid');

 

 

 

 delete event_pre;

 

 delete event_post;

 

 

 

 insert into event_pre select * from v$session_event where sid = my_sid;

 

 

 

 start_time := dbms_utility.get_time;

 

 

 

 for r in (select * from commit_write_from) loop

 

 

 

 insert into commit_write_to values (

 

 r.col_1,

 

 r.col_2

 

 );

 

 commit;

 

 end loop;

 

 

 

 end_time := dbms_utility.get_time;

 

 

 

 insert into event_post select * from v$session_event where sid = my_sid;

 

 

 

 dbms_output.new_line;

 

 dbms_output.put_line('time: ' || (end_time - start_time) / 100 || ' seconds');

 

 

 

 for r in (

 

 select post.event,

 

 (post.time_waited - nvl(pre.time_waited,0))/100 time_waited,

 

 post.total_waits - nvl(pre.total_waits,0) total_waits

 

 from event_pre pre right join

 

 event_post post using (event_id)

 

 order by post.time_waited - nvl(pre.time_waited, 0) desc

 

 

 

 ) loop

 

 num_rows := num_rows - 1;

 

 exit when num_rows = 0;

 

 

 

 dbms_output.put_line(

 

 rpad(r.event, 40) ||

 

 to_char(r.time_waited, '9999.99') || ' ' ||

 

 to_char(r.total_waits, '9999999')

 

 );

 

 end loop;

 

end many_commits;

/

5 - Executar inserts

set feedback off

set serveroutput on size 1000000 format wrapped

alter session set commit_write=immediate,wait;

 

truncate table commit_write_to;

 

exec many_commits;

Resultados

• IMMEDIATE WAIT

/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47335.png&key=d375d915ec5e00ab065740d95f7b7d6ebf93f8c864caec4894a7ec7a63c416e8" alt="47335.png" />

• IMMEDIATE NOWAIT

/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47337.png&key=fc4ecc2f260ba1438cfd03900127d37ea838dae75b5caea6d3257d75e4c84de0" alt="47337.png" />

• BATCH WAIT

/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47338.png&key=fb51f0e33a1ab28fb7bd83eb19bd0b550aee4ddfa6d9f00720a1a7921976dac9" alt="47338.png" />

• BATCH NOWAIT

/applications/core/interface/imageproxy/imageproxy.php?img=http://conteudo.imasters.com.br/24548/47336.png&key=1c6bbec0d2de60d7204d8c483bc7b417bebfcb3307e231ed488dcbcc05f5f359" alt="47336.png" />

Como resultado, podemos observar que o maior ganho (em tempo) está relacionado ao parâmetro COMMIT_WAIT, que controla quando as informações de uma transação são "descarregadas" nos Online Redo Log Files. Como o Oracle não aguarda pela confirmação que os dados foram escritos nos Redo Logs, o tempo é resposta é próximo de 0s.

Benefícios da utilização do Asynchronous Commit

• O Oracle não aguarda pela confirmação que o COMMIT foi concluído com sucesso. Ganha-se Tempo em cada transação.
• Se a aplicação executa diversos COMMITs pequenos, pode-se obter benefícios utilizando a opção BATCH para agrupar vários COMMITs em uma requisição de I/O.
• Otimização no tempo de Resposta.

Observações na utilização do Asynchronous Commit

• O processo de COMMIT retorna com sucesso antes mesmo da confirmação de que os dados foram gravados nos Online Redo Log Files, então se o Banco de Dados sofrer algum tipo de "crash" antes dos dados em memória (LOG_BUFFER) serem descarregados nos Redo Log Files, ou ocorrer algum tipo de problema de I/O, a instrução COMMIT será perdida juntamente com os dados.
• Os Parâmetros COMMIT_WRITE (Oracle 10g), COMMIT_LOGGING e COMMIT_WAIT (Ambos Oracle 11g) são parâmetros a nível de INSTANCE podem possuir diferentes valores e comportamentos. Em um ambiente RAC, recomenda-se mantê-los com os mesmos valores, evitando problemas de consistência em caso de "crash".
• Não existe um parâmetro para desabilitar a utilização deste recurso.
• Não existe uma maneira de evitar com que usuários do Banco de Dados utilizem o Asynchronous Commit. Qualquer usuário pode utilizar este recurso em sua sessão, o que significa que pode ocorrer perda de dados em caso de falha.

Fontes

• http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams031.htm
• http://sysdba.wordpress.com/2006/04/20/asynchronous-commit-a-new-feature-in-oracle-10gr2/
• http://www.techrepublic.com/article/trade-risk-for-speed-with-oracle-10gs-asynchronous-commit/6158695
• http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams027.htm
• http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams029.htm
• How to Minimise Waits for 'Log File Sync'? [MOS ID 857576.1]

Abs

Victor DBA

http://imasters.com.br/artigo/24548/oracle/alterando-comportamento-de-transacoes-no-oracle-database-com-asynchronous-commit