Performance em operações de INSERT: Quando a ordem dos fatores altera o produto

Olá,

No artigo de Dezembro/2013 eu abordei alguns conceitos sobre algumas operações no Oracle que podem ser aplicadas a este artigo. Irei demonstrar e compartilhar uma experiência na qual tive que fazer uma carga de dados (Direct INSERT) em uma tabela particionada, onde a fonte dos dados era uma uma view de banco de dados (140 milhões de linhas). Sabemos que a performance de um INSERT em uma tabela que possui índices é mais lento do que se a mesma não tivesse índices. O que me chamou a atenção além do tempo para realização do INSERT, foi o uso considerável das tablespaces de UNDO e TEMP. Abaixo irei mostrar um cenário onde irei criar uma tabela (NOLOGGING) particionada com os respectivos índices (NOLOGGING) e fazer uma operação de INSERT a partir de uma view.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01  number (10) not null,
  4     code02  number (15) not null,
  5     code03  number (10) not null,
  6     code04  number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:00.11

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.73
SQL> create index idx_code01code03 on t1 (code01,code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03_code02_code01 on t1 (code03,code02,code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01

Após a criação da tabela e dos respectivos índices, irei fazer o INSERT conforme demonstrado abaixo. Pode-se perceber que após 10 horas e 41 minutos da execução, o INSERT foi abortado por falta de espaço na tablespace de UNDO.

SQL> insert /*+ APPEND */ into t1 select * from vw1;
insert /*+ append */ into t1 select * from vw1
*
ERRO na linha 1:
ORA-30036: não foi possível estender o segmento em 8 no tablespace de undo 'UNDOTBS1'

Decorrido: 10:41:45.31

Vale a pena salientar que tanto a tablespace de UNDO quanto a tablespace TEMP foram definidas inicialmente com tamanho de 1 GB e AUTOEXTED ON. Ao final, é possível perceber que a tablespace de UNDO se estendeu até o máximo de 32 GB e que a tablespace TEMP se estendeu até 24 GB, conforme demonstrado abaixo.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                       31,9999847

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                   24

1 linha selecionada.

Enfim, percebendo que esta não seria a melhor opção para carregar uma tabela em um ambiente de produção, a alternativa abaixo foi a que melhor se encaixou, ou seja, primeiro realiza a criação da tabela particionada(NOLOGGING), depois a execução do INSERT utilizando a carga direta, e por fim a criação dos índices (NOLOGGING), conforme demonstrado abaixo.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01   number (10) not null,
  4     code02   number (15) not null,
  5     code03   number (10) not null,
  6     code04   number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:01.10

SQL> insert /*+ APPEND */ into t1 select * from vw1;

140000000 linhas criadas.

Decorrido: 00:04:35.04

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:10:25.73
SQL> create index idx_code01code03 on t1 (code01, code03) nologging noparallel local;

Índice criado.

Decorrido: 00:09:56.37
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:09:16.56
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:08:59.35
SQL> create index idx_code03_code02_code01 on t1 (code03, code02, code01) nologging noparallel local;

Índice criado.

Decorrido: 00:11:38.65

No mais é possível perceber que no total foram gastos cerca de 54 minutos para realizar toda operação, que a tablespace de UNDO não chegou a ser estendida acima de 1 GB e que a tablespace TEMP se estendeu no máximo até 6 GB.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                                1

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                    6

1 linha selecionada.