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