Asynchronous Commit no Oracle: Principais características e diferenças

Asynchronous Commit no Oracle

Este artigo foi escrito por mim e pelo meu colega Carlos H. Y. Furushima (ACE A), e tem como objetivo fazer uma abordagem sobre o commit assíncrono do Oracle (Asynchronous Commit), de forma a tratar suas principais características e diferenças em relação ao commit síncrono. Esta funcionalidade (commit assíncrono) surgiu no Oracle 10g Release 2 com o intuito de alterar o comportamento de uma transação com o propósito de oferecer ganhos de performance.

O que é transação de banco de dados?

Uma transação é uma sequência (1 ou mais) de operações de leitura (read) e escrita (write) executadas por um programa (unidade lógica de trabalho) sobre um sistema gerenciador de banco de dados (SGDB), ou seja, trata-se de uma visão abstrata que o sistema gerenciador de banco de dados (SGDB) tem de um programa. Esta transação em um SGBD de modelo transacional clássico (banco de dados relacional) possui quatro importantes propriedades cujo objetivo é manter os dados protegidos de acesso concorrente e de falhas de sistema. Uma transação possui quatro diferentes propriedades chamadas de ACID, um acrônimo derivado da primeira letra das seguintes propriedades: Atomicidade, Consistência, Isolamento e Durabilidade.

A transação em seu ciclo de vida faz uso de três principais recursos da arquitetura computacional, tais como: área de armazenamento volátil (memoria), CPU e área de armazenamento permanente (Disco – Storage). O fato de uma transação ser interrompida no meio de seu ciclo de vida pode deixar o banco de dados em um estado vulnerável e inconsistente. O intuito das propriedades ACID é impor ao SGDB o tratamento dos efeitos de transações parciais (transações interrompidas) do banco de dados.

Atomicidade e Durabilidade de uma transação

Uma transação é completada somente após sua confirmação ou cancelamento, conforme as propriedades de atomicidade (A) e durabilidade (D). Para manter este mecanismo, um SGBD de modelo transacional clássico (banco de dados relacional) mantém um registro de log para operações de escritas no banco de dados, nomeado em contexto Oracle, como “redo logs”. Essa estrutura é responsável por garantir as propriedades de atomicidade (A) e durabilidade (D) de modo que se o banco de dados sofrer uma eventual queda antes que os dados alterados sejam escritos de forma persistente em disco, o log será utilizado para restaurar essas informações quando o sistema for normalizado. Deste modo, o SGDB garante a atomicidade, desfazendo as ações de transações que não realizaram a confirmação (operação de COMMIT) e a durabilidade, garantindo que todas as ações de transações que realizaram a confirmação (operação de COMMIT) fiquem persistentes em disco e se tornem tolerantes às falhas do sistema (banco de dados).

Atomicidade (A): A execução de toda transação deve ser considerada atômica (indivisível), ou seja, ou todas as ações são executadas ou nenhuma delas é. Em caso de não confirmação ou interrupção abrupta o banco de dados deve voltar ao mesmo estado em que estava antes do início da transação.

Durabilidade (D): Se uma transação é concluída com sucesso (através de uma operação commit bem sucedida), então seus efeitos são persistentes (duráveis), mesmo que o sistema sofra uma queda antes que esses resultados (blocos ou dados modificados) sejam persistidos no disco.

Entendendo e modificando o comportamento de um COMMIT no Oracle Database

A execução da operação de COMMIT encadeia um exercício de gravação sequencial das entradas de redo (redo entries) para uma área permanente (disco – redo log files). No Oracle, o processo background LGWR (Log Writer) é o executor desta ação que visa proteger o banco de dados em caso de queda da instância, uma vez que os dados (blocos sujos no database buffer cache) em área volátil (SGA – Memória) possivelmente não foram gravados nos datafiles. Assim, o intuito é recuperar o “trabalho não salvo” (dados que não foram para os datafiles devido à queda da instância), utilizando os arquivos de redo logs gerados. Ao iniciar uma instância que sofreu uma falha (crash recovery), o processo background SMON será o responsável por refazer todas as ações do “trabalho não salvo nos datafiles”.

No Oracle, o registro de redo online, também conhecido como grupo de redo, é um conjunto de no mínimo dois ou mais arquivos que tem como função primária registrar todas as alterações feitas no banco de dados, incluindo as alterações com e sem commit. As entradas de redo são armazenadas temporariamente nos buffers de registro de redo (Redo Log Buffer) da SGA (System Global Area) onde o processo de segundo plano log writer (LGWR) grava essas entradas sequencialmente em um arquivo de registro de redo online.

Estas gravações do buffer de redo para os arquivos ocorrem nas seguintes situações: (1) a cada 3 segundos, (2) quando 1/3 do buffer estiver cheio, (3) quando o comando commit for emitido, (4) quando as entradas de redo no buffer atingir 1 MB, (5) antes do processo de segundo plano DBWn gravar as alterações do cache de banco de dados nos datafiles. Os arquivos de redo log online são utilizados de forma cíclica, por exemplo, se dois arquivos constituem o registro de redo online, o primeiro arquivo é preenchido, o segundo arquivo é preenchido, o primeiro arquivo é reutilizado e preenchido, o segundo arquivo é reutilizado e preenchido e assim por diante. Cada vez que um arquivo é preenchido, ele recebe um número de sequência de registro para identificar o conjunto de entradas de redo. Como já mencionado anteriormente, comitar uma transação significa apenas a garantia de sua recuperação em caso de falha, e não a gravação dos dados diretamente nos datafiles como muitos imaginam.

Partindo do princípio que uma operação de COMMIT escreve as entradas de redo (redo entries) para os redo log files a fim de oferecer uma recuperabilidade para o banco de dados, é importante salientar que a existência desta sinergia não garante a durabilidade (D) da transação. Seu mecanismo (modo de funcionamento) é quem irá garantir a propriedade “D”. O comportamento default (padrão) da operação de COMMIT consiste na execução de escritas síncronas das entradas de redo (redo entries) para os redo log files. Neste modo de funcionamento o Oracle garante a durabilidade (D) da transação, porém é possível modificar este comportamento a partir da versão Oracle 10g Release 2 com uma funcionalidade chamada de “Asynchronous Commit” (Commit Assíncrono), possibilitando a execução de escritas assíncronas das entradas de redo (redo entries) para os redo log files. Utilizando o “Asynchronous Commit” (Commit Assíncrono), a propriedade durabilidade (D) não é mais garantida.

Como demonstrado na figura acima, o comportamento do processo background LGWR pode ser configurado/alterado de forma a otimizar e diminuir o tempo de gravação das informações de uma transação nos Redo Log Files. Vale a pena salientar que o próprio comando COMMIT também oferece este recurso através das cláusulas demonstradas pela figura abaixo.

dZFmXCBs7TJQDFs Ez U25N SqN28gT6FfT fJ8neIIW63TksUMzrg7ZvJpAK

Como alterar o comportamento da operação de COMMIT no Oracle Database?

A alteração do mecanismo (modo de funcionamento) da operação de COMMIT é feita modificando dois comportamentos, ou seja, “quando o LGWR escreve as entradas de redo” e “como o LGWR escreve as entradas de redo”. Estes dois comportamentos são configurados por parâmetros de instância (pfile e spfile), são eles COMMIT_WRITE (Oracle 10g), COMMIT_LOGGING e COMMIT_WAIT (Ambos Oracle 11g). É importante ressaltar que a partir da versão 11g R1 o parâmetro COMMIT_WRITE se tornou obsoleto, porém ainda funcional para a manutenção da compatibilidade com a versão 10g R2 sendo desmembrado em dois parâmetros: COMMIT_LOGGING e COMMIT_WAIT.

Oracle 10g R2

COMMIT_WRITE = “Quando o LGWR escreve as entradas de redo”, “Como o LGWR escreve as entradas de redo”.

No Oracle 10g R2, o uso da funcionalidade Asynchronous Commit é feita pelo parâmetro COMMIT_WRITE. Seu uso é feito tanto no nível de sessão (ALTER SESSION) quanto no nível de sistema (ALTER SYSTEM). O parâmetro COMMIT_WRITE pode receber até dois argumentos, onde é determinado “quando” (de forma imediata IMMEDIATE ou agrupada BACTH) e “como” (de forma síncrona WAIT ou assíncrona NOWAIT).

Importante:

Se o parâmetro não receber qualquer argumento, o Oracle assumirá seu comportamento default (IMMEDIATE e WAIT).
Se o parâmetro receber somente o argumento IMMEDIATE ou BATCH (quando), o Oracle assumirá o argumento default referente ao comportamento “como” (WAIT).
Se o parâmetro receber somente o argumento WAIT ou NOWAIT (como), o Oracle assumirá o argumento default referente ao comportamento “quando” (IMMEDIATE).

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_WRITE = '{ [IMMEDIATE] | [BATCH] },{ [NOWAIT] | [WAIT] }';

Oracle 11g R1 ou Superior

COMMIT_LOGGING = “Quando LGWR escreve as entradas de redo”

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de “quando” as entradas de redo são escritas para o redo log file: de forma imediata (IMMEDIATE) após execução da operação de COMMIT ou de forma agrupada (BACTH) onde várias operações de COMMIT são escritas em uma única requisição de I/O, ou seja, o Oracle troca intensa quantidade de I/O com baixa volumetria de blocos por uma tênue quantidade de I/O com grande volumetria de blocos. O resultado disso é a diminuição de requisições de I/O oriundas de operações de COMMIT.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_LOGGING = '{[IMMEDIATE] | [BATCH]}';

COMMIT_WAIT = “Como LGWR escreve as entradas de redo”

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de “como” as entradas de redo são escritas para o redo log file: de forma síncrona (WAIT) após execução da operação de COMMIT na qual o processo background LGWR espera pela confirmação de que as entradas de redo sejam escritas nos redo log files, e a forma assíncrona (NOWAIT) após a execução da operação de COMMIT. Com isso, o processo background LGWR não espera pela confirmação de que as entradas de redo sejam escritas nos redo log files.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_WAIT = { [NOWAIT] | [WAIT] | [FORCE_WAIT] } ;

Teste de desempenho (COMMIT)

Para medir a performance de escrita em um banco de dados Oracle no que se refere ao fechamento de uma transação com o comando COMMIT, foram realizados alguns testes de inserção (INSERTS) em uma tabela no banco de dados. O teste consistiu em medir o tempo gasto para inserção de 10, 100, 1.000 e 10.000 linhas respectivamente, de acordo com a configuração dos parâmetros COMMIT_LOGGING e COMMIT_WAIT na sessão do usuário. Para simular as inserções, foram utilizados algumas stored procedures e functions de banco de dados, bem como a chamada de um bloco PL/SQL através do SQL*Plus. Após a execução dos testes, os resultados abaixo foram obtidos.

Obs: a vertente de tempo do gráfico foi transformada em um intervalo de referência entre 0 e 10 de forma a simplificar a sua visualização, ou seja, quanto menor o valor, melhor a performance.

Simulação de inserção de 10 linhas