Tuning Redo Log Buffer

Tuning Redo Log Buffer

Postado em Janeiro 8th, 2012 sobre Oracle Database,Performance por Maycon Tomiasi

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.

Script para Teste

Vamos executar o procedimento do Script. O procedimento do script a cada INSERT na tabela é efetuado a transação.

1
2
3
4
5
SQL> exec master.p_product;
Procedimento PL/SQL concluido com sucesso.
SQL>

Abaixo segue o tamanho atual do Redo Log Buffer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> show parameter log_buffer
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     1048576
SQL>
set lines 80
set pages <a title="Powered by Text-Enhance" id="_GPLITA_0" style="text-decoration:underline" href="#" in_rurl="http://www.textsrv.com/click?v=QlI6MjA2NDk6MzcwOjUwMDphZjRmOTllMjJhNjBmMzA1NzFhMTQ4NjlkYjZkYzJhNzp6LTEwMzItMTAzNjg6d3d3Lm9yYWNsZWhvbWUuY29tLmJy">500</a>
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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