Este artigo tem o objetivo de como podemos melhorar a memória Oracle “Redo Log Buffer”.
Redo Log Buffer
O redo log buffer é um buffer circular na SGA (System Global Area) que mantem informações sobre as mudanças feitas no banco de dados. Estas informações são armazenadas nas entradas de redo.
Entradas de Redo possui a informação necessária para reconstruir, ou refazer mudanças feitas no banco de dados pelas operaçãos INSERT, UPDATE, DELETE, CREATE, ALTER, ou DROP.
Entradas de Redo se necessário são usados para recuperação do banco de dados.
Entradas de Redo são copiadas pelo processo do banco de dados Oracle do espaço de memória do usuário para o redo log buffer na SGA. A entrada de redo faz continuos espaços sequencias no buffer. O processo de fundo LGWR (Log Writer) escreve o redo do log buffer para o ativo grupo de arquivo de redo (redo log file) ou grupo de arquivos no disco.
Log Writer Process (LGWR)
O log writer process (LGWR) é responsável por gerenciamento e escrita do redo log buffer para o redo log file (arquivo de redo) no disco.
LGWR escreve todas entradas de redo que tem sido copiado dentro do buffer desde a última escrita.
LGWR escreve uma contigua porção do buffer para o disco. LGWR escreve:
– Uma gravação efetuada quando o processo do usuário efetua uma transação;
– Redo log buffers;
– Cada 3 segundos;
– Quando o redo log buffer é 1/3 do total;
– Quando um processo DBWn (Database Writer) escreve os buffers modificados para o disco se necessário;
Vamos utilizar o Script em anexo para realização de melhoria na memória Log Buffer.
Vamos executar o procedimento do Script. O procedimento do script a cada INSERT na tabela é efetuado a transação.
SQL> exec master.p_product; Procedimento PL/SQL concluido com sucesso. SQL>
Abaixo segue o tamanho atual do Redo Log Buffer.
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 1048576
SQL>
set lines 80
set pages 500
select
event "Evento" ,
sum(total_waits) "Numero de Esperas" ,
avg(average_wait)/100 "Media de Espera (Segundos)"
from
v$session_event
where
average_wait > 0
and event in ('log buffer space','log file sync','log file parallel write','log file sequential read','log file single write')
group by event
order by 3 desc
;
Evento Numero de Esperas MEDIA de ESPERA (Segundos)
------------------------------ ----------------- --------------------------
log buffer space 2 .56
log file sync 1 .01
log file sequential read 77 .01
log file parallel write 146,154 .00
log file single write 16 .00
SQL>
Podemos perceber que o evento “log buffer space” está tendo maior espera em segundos.
Sobre este evento veja o artigo do Rafael Stoever.
“Log Buffer Space”
O evento “log file sync” está tendo também um estado de espera.
Sobre este evento veja o documento Oracle.
Oracle Documentation.
Vamos aumentar a memória do “redo log buffer” e executar novamente o procedimento.
SQL> alter system set log_buffer=4194304 scope=spfile; Sistema alterado. SQL> startup force Instancia ORACLE iniciada. Total System Global Area 544051200 bytes Fixed Size 1337720 bytes Variable Size 306185864 bytes Database Buffers 230686720 bytes Redo Buffers 5840896 bytes Banco de dados montado. Banco de dados aberto. SQL> show parameter log_buffer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer integer 4194304 SQL> exec master.p_product; Procedimento PL/SQL concluido com sucesso. SQL>
Vamos verificar os eventos após o aumento do redo log buffer executando o mesmo procedimento.
SQL>
set lines 80
set pages 500
col Evento for a30
select
event "Evento" ,
sum(total_waits) "Numero de Esperas" ,
avg(average_wait)/100 "Media de Espera (Segundos)"
from
v$session_event
where
average_wait > 0
and event in
(
'Log archive I/O','log buffer space','log file sync','log file parallel write','log file sequential read','log file single write'
,'Log file init write','log file switch (archiving needed)','log file switch (checkpoint incomplete)','log file switch (clearing log file)'
,'log file switch completion','log file switch (private strand flush incomplete)','log switch/archive','log write(even)','log write(odd)'
)
group by event
order by 3 desc
;
Evento Numero de Esperas Media de Espera (Segundos)
------------------------------ ----------------- --------------------------
log file switch completion 1 1,3847
log file sync 1 ,0321
log file sequential read 86 ,015
Log archive I/O 184 ,00575
log file parallel write 243780 ,001
log file single write 22 ,0009
SQL>
Podemos perceber que o evento “log buffer space” não ocorreu após o aumento do redo log buffer, porém o log file sync está ainda ocorrendo.
O log file parallel write aumentou de 146,154 para 243,780.
Vamos alterar o procedimento efetuando a transação após o INSERT.
SQL> create or replace procedure master.p_product as c number; begin c:=0; for c in 1..100000 loop insert into product values (prd_id_seq.nextval,'Product A'||c, to_number(to_char(sysdate,'SS'),'9999D99')/3); end loop; commit; end; / Procedimento criado. SQL>
Vamos executar o procedimento novamente e verificar os eventos.
SQL> exec master.p_product;
Procedimento PL/SQL concluido com sucesso.
SQL>
set lines 80
set pages 500
col Evento for a30
select
event "Evento" ,
sum(total_waits) "Numero de Esperas" ,
avg(average_wait)/100 "Media de Espera (Segundos)"
from
v$session_event
where
average_wait > 0
and event in
(
'Log archive I/O','log buffer space','log file sync','log file parallel write','log file sequential read','log file single write'
,'Log file init write','log file switch (archiving needed)','log file switch (checkpoint incomplete)','log file switch (clearing log file)'
,'log file switch completion','log file switch (private strand flush incomplete)','log switch/archive','log write(even)','log write(odd)'
)
group by event
order by 3 desc
;
Evento Numero de Esperas Media de Espera (Segundos)
------------------------------ ----------------- --------------------------
log file switch completion 1 1,3847
log file sync 1 ,0321
log file sequential read 115 ,01375
Log archive I/O 269 ,0059
log file parallel write 244094 ,001
log file single write 26 ,00085
SQL>
Podemos perceber que não houve aumento do evento “log file sync” e houve um pequeno aumento no número de esperas porém em segundos não houve alteração.
O log file parallel write aumentou de 243,780 para 244,094.
Com a efetuação da transação após as inserções na tabela, houve menos contenção de I/O.
Vamos voltar o “commit” do procedimento para cada inserção na tabela e verificar os eventos novamente.
SQL> create or replace procedure master.p_product as c number; begin c:=0; for c in 1..100000 loop insert into product values (prd_id_seq.nextval,'Product A'||c, to_number(to_char(sysdate,'SS'),'9999D99')/3); commit; end loop; end; / Procedimento criado. SQL> exec master.p_product; Procedimento PL/SQL concluido com sucesso. SQL>
Vamos verificar os eventos do “redo log buffer”.
SQL>
set lines 80
set pages 500
col Evento for a30
select
event "Evento" ,
sum(total_waits) "Numero de Esperas" ,
avg(average_wait)/100 "Media de Espera (Segundos)"
from
v$session_event
where
average_wait > 0
and event in
(
'Log archive I/O','log buffer space','log file sync','log file parallel write','log file sequential read','log file single write'
,'Log file init write','log file switch (archiving needed)','log file switch (checkpoint incomplete)','log file switch (clearing log file)'
,'log file switch completion','log file switch (private strand flush incomplete)','log switch/archive','log write(even)','log write(odd)'
)
group by event
order by 3 desc
;
Evento Numero de Esperas Media de Espera (Segundos)
------------------------------ ----------------- --------------------------
log file switch completion 1 1,3847
log file sync 2 ,0712
log file sequential read 144 ,013725
Log archive I/O 354 ,005933333
log file parallel write 394412 ,0009
log file single write 30 ,00085
Podemos perceber que houve aumento do evento “log file sync” e houve um aumento significativo no número de esperas do evento “log file parallel write” porém em segundos houve um pequeno aumento.
O log file sync aumento de ,0321 para ,0712
O log file parallel write aumentou de 244,094 para 394,412.
Portanto para melhorar basicamente o “redo log buffer” deve-se melhorar a forma da efetuação das transações, os arquivos de redo devem permanecer em discos separados dos arquivos de dados para evitar contenção de I/O e ter um tamanho ideal para esta memória Oracle.
Autor: Maycon Tomiasi
Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.
