Pular para o conteúdo

IMP lento no Oracle

Sabia que o tamanho e quantidade dos REDO LOGs influem no DESEMPENHO de gravação do Oracle?

Tudo bem, vejo muito DBA experiente por aí que também não sabe, e até duvida disso.
Esta dica serve tanto para um IMP quanto para qualquer gravação no Oracle.

Isto ocorre porque o LGWR (que grava nos REDO LOGs) grava mais rápido do que o DBRW (que grava os dados nos DATAFILEs), porque o REDO LOG é sagrado para o Oracle.
O Oracle se comporta assim porque se a Instância cair, não há problemas, pois se os dados estiverem nos REDO LOGs, podem ser recuperados automaticamente.
Já a gravação nos DATAFILEs pode esperar, e deve esperar, pois pode ser que estes mesmos dados sejam alterados novamente, e então a alteração ocorreria apenas no CACHE.

Mas se todos os REDO LOGs, que são gravados de maneira circular (primeiro o grupo 1, depois o 2, etc, e volta novamente para o 1) já forem utilizados, e o DBWR ainda está em grande descompasso com o LGWR (ou seja, os dados dos DATAFILEs não estão iguais com o que já foi gravado no primeiro grupo de REDO LOG), o Oracle não poderá voltar a utilizar o 1o grupo, pois os dados que estão nele ainda são necessários caso o banco caia.

Esta situação pode ocorrer em uma grande gravação contínua (sem dar tempo para o LGWR respirar), ou mesmo com grande número de gravações menores.
E esta situação ocorre igualmente em ARCHIVELOG ou NOARCHIVELOG. O teste abaixo foi feitos nos dois tipos de configuração.

Mas, vamos aos fatos. Vamos testar. Fiz este teste em um banco Oracle 11gR1, sem mais nenhum usuário no Oracle.
Vejamos primeiramente o tamanho dos REDO LOGs existentes:

SQL> set pages 1000
SQL> set lines 170
SQL> col member format a50
QL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TI
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------
1 1 10 52428800 1 NO CURRENT 1043889 14/01/09
2 1 8 52428800 1 YES INACTIVE 987882 02/01/09
3 1 9 52428800 1 YES INACTIVE 1022404 05/01/09

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO03.LOG NO
2 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO02.LOG NO
1 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO01.LOG NO

Cada REDO LOG tem 50 MB, o tamanho padrão do Oracle 10gR1/R2 e 11gR1.
Agora vamos criar uma tabela maior que o tamanho dos três grupos de REDO LOGs juntos. Ou seja, precisamos de uma tabela maior que 150 MB.

SQL> create table t as select * from all_objects;

Tabela criada.

SQL> select to_char(sum(bytes)) from dba_segments where segment_name = 'T' and segment_type = 'TABLE' and owner = 'SYS';

TO_CHAR(SUM(BYTES))
----------------------------------------
8388608

Ainda está pequena, ~8MB. Vamos aumentar essa tabela…

SQL> insert into t (select * from t);

68194 linhas criadas.

SQL> insert into t (select * from t);

136388 linhas criadas.

SQL> insert into t (select * from t);

272776 linhas criadas.

SQL> insert into t (select * from t);

545552 linhas criadas.

SQL> insert into t (select * from t);

1091104 linhas criadas.

SQL> commit;

Commit concluído.

SQL> select to_char(sum(bytes)) from dba_segments where segment_name = 'T' and segment_type = 'TABLE' and owner = 'SYS';

TO_CHAR(SUM(BYTES))
----------------------------------------
267386880

Agora está com ~250 MB, suficinete para o teste. Vamos criar uma cópia desta tabela e medir o tempo.
Desta forma, faremos uma gravação maior que todos os grup´so de REDO LOGs juntos.

SQL> set timing on;
SQL> create table t2 as select * from t;

Tabela criada.

Decorrido: 00:02:45.86

Levou 2 minutos e 45 segundos. Vamos esvaziar o BUFFER_CACHE para que ele não acelere a próxima operação artificialmente.

SQL> alter system flush buffer_cache;

Sistema alterado.

OK, agora vamos criar um REDO LOG maior que a gravação que pretendemos fazer.

SQL> alter database add logfile 'C:\ORACLE\ORADATA\TESTE11GR1\REDO04.LOG' size 1G;

Banco de dados alterado.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO03.LOG NO
2 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO02.LOG NO
1 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO01.LOG NO
4 ONLINE C:\ORACLE\ORADATA\TESTE11GR1\REDO04.LOG NO

Decorrido: 00:00:00.07
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TI
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------
1 1 19 52428800 1 YES INACTIVE 1045621 14/01/09
2 1 20 52428800 1 NO CURRENT 1045706 14/01/09
3 1 18 52428800 1 YES INACTIVE 1045521 14/01/09
4 1 0 1073741824 1 YES UNUSED 0

SQL> drop table t2;

Tabela eliminada.

SQL> create table t2 as select * from t;

Tabela criada.

Decorrido: 00:01:07.67
SQL> spool off;

Levou 1 minutos e 7 segundos, ou seja, menos da metade do tempo.

Quer saber se um Banco de Dados Oracle está sofrendo deste problema?
Procure no alert.log se a mensagem abaixo está aparecendo.
Se estiver, aumente seus REDO LOGs em tamanho e quantidade, até que esta mensagem não apareça mais no alert.log.

Wed Jan 14 20:59:35 2009
Thread 1 cannot allocate new log, sequence 18
Checkpoint not complete
Current log# 2 seq# 17 mem# 0: C:\ORACLE\ORADATA\TESTE11GR1\REDO02.LOG

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 0 / 5. Contagem de votos: 0

Sem votos ! Seja o primeiro a classificar !

Marcações:

11 comentários em “IMP lento no Oracle”

  1. Avatar de Bruno Murassaki

    Ola Ricardo blz, muito bom o seu artigo Parabéns.

    Estou com um problema em um Banco de Dados em 2 eventos de espera conforme analise do ADDM e alert log gerando checkpoint not complete:
    – log file parallel write
    – log file sync

    O tamanho atual do redo log files é de 50MB default. O Banco de Dados é Oracle 10g 10.2.0.1 Standard Edtion então o Parametro FAST_START_RECOVERY_TARGET não funciona, com isso o advisor não funciona.

    Eu sei que tenho que aumentar o Tamanho do Redolog File. Mas existe algum metodo que eu possa saber qual o tamanho ideal considerando a limitação do Standard Edition? Ou tem que ir no “chutometro” mesmo?

    Desde já Obrigado

    Atenciosamente

    Bruno Murassaki

  2. Avatar de Ricardo Portilho Proni

    Fala Bruno.

    O tamanho default do Oracle é inviável para qualquer banco sério.
    Redo grande não te faz mal.

    Coloque REDOs de 500MB e veja se essas Waits somem.

  3. Avatar de Roberto Dias

    ola . estou com uma duvida,
    tenho uma base oracle de 70 gygas
    a area de redo;log está com 128 megas só .
    para processamento diaria parece não estar afetando o desempenho
    mas qdo precisei incluir 4 milhoes de registros numa tabela com 30 milhões
    ai houve muita degradação. alguma dica sobre o tamanho do redo.log
    ou outro parametro ?

  4. Avatar de Ricardo Portilho Proni

    Oi Roberto !
    Sugiro que aumente os REDO LOGs sim. Redo Log maior do que o necessário não é prejuízo.
    Você deve aumenta-los até que a mensagem “Checkpoint not complete” não apareça mais no Alert Log, ou os Wait Events de Switch não apareçam mais. Aí sim eles não serão mais um gargalo.

    Abraço !

  5. Avatar de Igor Marinho

    Bom dia Ricardo,
    Fiz esse teste na empresa e no começo pareceu apresentar bons resultados passando de 21 segundos para 9 secs…. (criei 2 REDO de 500M e a tabela estava com ~370M) mas ao ir repetindo processo o tempo estava alternando muito entre 21 secs, 16 secs , 9 secs e chegou a rodar em até 24 secs…
    Tem alguma dica?

    Obrigado.

  6. Avatar de Jorge Luiz Vaz

    Não tenho problemas de “Checkpoint not complete”, e os eventos de Switch não aparecem, mas o maldito “log file sync”, aparece constantemente no TOP 5.
    As aplicações que temos são terceirizadas e portanto o problema dos commits, não tenho como resolver, mesmo tendo mantido diálogo com o fornecedor para diminuir a quantidade de commits.
    Ele não deu a mínima.
    Lá vai uma pergunta : Se eu utilizar commits assíncronos, o LGWR não me garantiria o “Durability” da transação em caso de crash, mas o “log file sync” waits diminuiria ?

    Sei que o risco é grande, mas existiria uma outra forma de resolver estes log file sync ?

  7. Avatar de Ricardo Portilho Proni

    Oi Jorge. Blz?

    Fora os Commits, o que pode causar o log file sync é o I/O onde estão os REDOs. Se for possível, coloque eles em um device mais rapido.
    O log file sync pode ser reduzido também deixando apenas um membro em cada grupo.

    Abraço !

Deixe um comentário para Emerson Martins Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress