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

[RSS iMasters 5]

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

 

 

 

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