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”

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

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

– IMMEDIATE NOWAIT

– BATCH WAIT

– BATCH NOWAIT

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