SQLLDR x CTAS: Qual recurso tem melhor desempenho?

sqlloader ou CTAS External Table

Um processo muito utilizado nos bancos de dados é a carga de dados em tabelas, esse processo pode ser feito de varias formas e utilizando inúmeras ferramentas, neste artigo vamos testar o desempenho de duas dessas ferramentas e verificar qual delas é mais eficiente (SQLLDR ou CTAS), esses recursos suportam paralelismo mas em nosso teste não vamos utilizar a opção de paralelismo.

Os dois métodos vão utilizar uma massa de dados em formato texto que vai estar disponível em um filesystem no servidor do banco Oracle, essa massa de dados tem 2.000.000 linhas e ocupa um espaço de 417M.

Vamos realizar a simulação de carga desse arquivo com o utilitário SQL*LOADER (SQLLDR) e com a DDL “CREATE TABLE AS SELECT” que vai ler os dados de origem de uma tabela “EXTERNAL TABLE”.

Demonstração prática

1) Gerar a massa de dados em formato TXT no servidor de banco
2) Fazer a carga no banco utilizando SQL*LOADER
3) Fazer a carga no banco utilizando CTAS com EXTERNAL TABLE

 

1) Gerar a massa de dados em formato TXT

Para gerar a massa de dados vamos criar uma tabela normal no banco de dados com 2.000.000 linhas, criar um diretório apontando para um filesystem do servidor de banco e executar uma bloco PLSQL para copiar as linhas da tabela para um arquivo TXT no diretório criado.

 

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
SQL> SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

SQL>
SQL> alter session set workarea_size_policy=MANUAL;

Sessão alterada.

SQL>
SQL> alter session set sort_area_size=2000000000;

Sessão alterada.

SQL> create table tab_dbtw01
  2  as
  3  select rownum                                        id,
  4         mod(rownum,100)                           codvar,
  5         trunc(dbms_random.value(0,100000))  categoria,
  6         lpad(rownum,10,'0')                  criterio,
  7         rpad('x',60,'x')                       texto1,
  8         rpad('y',60,'y')                       texto2,
  9         rpad('z',60,'z')                       texto3
 10    from dual connect by level<=2e6;

Tabela criada.

SQL>
SQL> conn sys/oracle@lab11 as sysdba
Conectado.
SQL>
SQL> create directory DBTW as '/oracle/load';

Diretório criado.

SQL>
SQL> grant read,write on directory DBTW to CURSO01;

Concessão bem-sucedida.

SQL>
SQL> DECLARE
  2    v_finaltxt  VARCHAR2(4000);
  3    v_v_val     VARCHAR2(4000);
  4    v_n_val     NUMBER;
  5    v_d_val     DATE;
  6    v_ret       NUMBER;
  7    c           NUMBER;
  8    d           NUMBER;
  9    col_cnt     INTEGER;
 10    rec_tab     DBMS_SQL.DESC_TAB;
 11    col_num     NUMBER;
 12    v_fh        UTL_FILE.FILE_TYPE;
 13    p_data_file VARCHAR2(20) := 'et_dbtw01.txt';
 14    p_dir       VARCHAR2(20) := 'DBTW';
 15    p_sql       VARCHAR2(4000) := 'SELECT * FROM TAB_DBTW01';
 16  BEGIN
 17    c := DBMS_SQL.OPEN_CURSOR;
 18    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
 19    d := DBMS_SQL.EXECUTE(c);
 20    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
 21    FOR j in 1..col_cnt
 22    LOOP
 23      CASE rec_tab(j).col_type
 24        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
 25        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
 26        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
 27      ELSE
 28        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
 29      END CASE;
 30    END LOOP;
 31    --
 32    -- This part outputs the DATA
 33    --
 34    v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
 35    LOOP
 36      v_ret := DBMS_SQL.FETCH_ROWS(c);
 37      EXIT WHEN v_ret = 0;
 38      v_finaltxt := NULL;
 39      FOR j in 1..col_cnt
 40      LOOP
 41        CASE rec_tab(j).col_type
 42          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
 43                      v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
 44          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
 45                      v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
 46          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
 47                      v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
 48        ELSE
 49          v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
 50        END CASE;
 51      END LOOP;
 52      UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
 53    END LOOP;
 54    UTL_FILE.FCLOSE(v_fh);
 55    DBMS_SQL.CLOSE_CURSOR(c);
 56  END;
 57  /

Procedimento PL/SQL concluído com sucesso.

SQL>

 

2) Fazer a carga no banco utilizando SQL*LOADER

Para realizar a carga através do utilitário SQL*LOADER, vamos criar uma tabela no banco onde serão carregados os dados que foram armazenados no arquivo TXT gerado na primeira etapa, para executar o comando sqlldr criamos um arquivo de controle chamado “carga_dbtw02.ctl”.

 

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
SQL> CREATE TABLE "CURSO01"."CARGA_DBTW02"
  2        (    "ID" NUMBER,
  3             "CODVAR" NUMBER,
  4             "CATEGORIA" NUMBER,
  5             "CRITERIO" VARCHAR2(10),
  6             "TEXTO1" VARCHAR2(60),
  7             "TEXTO2" VARCHAR2(60),
  8             "TEXTO3" VARCHAR2(60)
  9        )
 10       TABLESPACE "USERS" ;

Tabela criada.

SQL>

[oracle@odbsrv11 ~]$ cd /oracle/load
[oracle@odbsrv11 load]$ ll
total 426668
-rw-r--r-- 1 oracle oinstall       330 Jul 24 18:42 carga_dbtw02.ctl
-rw-r--r-- 1 oracle oinstall 436466791 Jul 24 18:33 et_dbtw01.txt
[oracle@odbsrv11 load]$ cat carga_dbtw02.ctl
options (errors=50)
load data
 characterset WE8ISO8859P1
 infile '/oracle/load/et_dbtw01.txt'
 badfile '/oracle/load/carga_dbtw02.bad'
 discardfile '/oracle/load/carga_dbtw02.dsc'
 into table carga_dbtw02
 fields terminated by ","
 optionally enclosed by '"'
 ( id, codvar, categoria, criterio, texto1, texto2, texto3 )
[oracle@odbsrv11 load]$
[oracle@odbsrv11 load]$ sqlldr CURSO01/oracle  control=carga_dbtw02.ctl direct=true

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jul 24 18:49:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 2000000.
[oracle@odbsrv11 load]$
[oracle@odbsrv11 load]$ cat carga_dbtw02.log

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jul 24 18:49:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   carga_dbtw02.ctl
Character Set WE8ISO8859P1 specified for all input.

Data File:      /oracle/load/et_dbtw01.txt
  Bad File:     /oracle/load/carga_dbtw02.bad
  Discard File: /oracle/load/carga_dbtw02.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table CARGA_DBTW02, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,  O(") CHARACTER
CODVAR                               NEXT     *   ,  O("
) CHARACTER
CATEGORIA                            NEXT     *   ,  O(") CHARACTER
CRITERIO                             NEXT     *   ,  O("
) CHARACTER
TEXTO1                               NEXT     *   ,  O(") CHARACTER
TEXTO2                               NEXT     *   ,  O("
) CHARACTER
TEXTO3                               NEXT     *   ,  O(") CHARACTER


Table CARGA_DBTW02:
  2000000 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       2000000
Total logical records rejected:         0
Total logical records discarded:        0
Direct path multithreading optimization is disabled

Run began on Mon Jul 24 18:49:40 2017
Run ended on Mon Jul 24 18:50:30 2017

Elapsed time was:     00:00:50.22
CPU time was:         00:00:03.82
[oracle@odbsrv11 load]$

 

Para evitar que as áreas de cache do sistema operacional interferissem no resultado final, realizamos a carga duas vezes e na log acima incluímos somente o resultado da segunda execução que foi mais rápida que a primeira. Na log do SQL*LOADER podemos verificar que as 2.000.000 de linhas foram carregadas em 50.22 segundos (linha 96).

3) Fazer a carga no banco utilizando CTAS com EXTERNAL TABLE

Para fazer a carga utilizando CTAS (Create Table As Select) vamos criar uma External Table apontando para o aquivo TXT gerado na primeira etapa e na sequência executar a criação da tabela no banco lendo os registros da External Table.

 

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
SQL> CREATE TABLE et_dbtw01 (
  2    id                       NUMBER,
  3    codvar                   NUMBER,
  4    categoria                NUMBER,
  5    criterio              VARCHAR2(10),
  6    texto1                   VARCHAR2(60),
  7    texto2                   VARCHAR2(60),
  8    texto3           VARCHAR2(60)
  9  )
 10  ORGANIZATION EXTERNAL (
 11    TYPE ORACLE_LOADER
 12    DEFAULT DIRECTORY DBTW
 13    ACCESS PARAMETERS (
 14      RECORDS DELIMITED BY NEWLINE
 15      FIELDS TERMINATED BY ','
 16      OPTIONALLY ENCLOSED BY '"'
 17      MISSING FIELD VALUES ARE NULL
 18      (
 19        id           CHAR(32),
 20        codvar               CHAR(32),
 21        categoria            CHAR(32),
 22        criterio          CHAR(10),
 23        texto1       CHAR(60),
 24        texto2               CHAR(60),
 25        texto3               CHAR(60)
 26      )
 27    )
 28    LOCATION ('et_dbtw01.txt')
 29  )
 30  REJECT LIMIT UNLIMITED;

Tabela criada.

SQL> set timing on;
SQL> create table CARGA_DBTW03 as select * from  ET_DBTW01;

Tabela criada.

Decorrido: 00:01:26.21
SQL> drop table CARGA_DBTW03 purge;

Tabela eliminada.

Decorrido: 00:00:05.44
SQL> create table CARGA_DBTW03 as select * from  ET_DBTW01;

Tabela criada.

Decorrido: 00:00:41.72
SQL> select count(1) from CARGA_DBTW03;

  COUNT(1)
----------
   2000000

Decorrido: 00:00:09.68
SQL> select segment_name, round(bytes) from user_segments where segment_name like 'CARGA_DBTW%';

SEGMENT_NAME                                                                      ROUND(BYTES)
--------------------------------------------------------------------------------- ------------
CARGA_DBTW02                                                                         486539264
CARGA_DBTW03                                                                         494927872

Decorrido: 00:00:00.33
SQL>

 

Como foi feito no SQL*LOADER executamos o CTAS duas vezes para eliminar a interferência das áreas de cache e vamos considerar somente a segunda carga que foi executada em 41.72 segundos (linha 49).

Conclusão

Baseado nos testes acima, realizados numa máquina virtual com recursos limitados, podemos concluir que o CTAS foi 16,9% mais rápido que o SQL*LOADER, porem repetindo esse mesmo teste em outro servidor com mais recursos obtivemos outro resultado e o SQL*LOADER foi 21,4% mais rápido que o CTAS. Como podemos observar os recursos de memoria, configurações e capacidade do processador influenciaram o resultado destes testes. Convido você a reproduzir este teste e publicar aqui o resultados incluindo a configuração do servidor e do banco de dados como referência.

Configurações das máquinas testadas

MAQUINA VIRTUAL
Linux 2.6.18-164.el5 i686
1 processador
1024M Memoria RAM
Intel(R) Core(TM) i5-2450M CPU @ 2.50GHz

BANCO ORACLE
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
MEMORY_TARGET = 632M
SERVIDOR DELL
Linux 2.6.32-642.6.2.el6.x86_64
16 processadores
193649M Memoria RAM
Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz

BANCO ORACLE
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
SGA_TARGET = 2000M
PGA_AGGREGATE_TARGET = 800M
 

1
2
3
4
5
6
7
.______________________________________________________________________________.
|  SERVIVOR        |  TEMPO SQL*LOADER  |  TEMPO CTAS  |  DIFERENÇA PERCENTUAL |
|------------------|--------------------|--------------|-----------------------|
| MAQUINA VIRTUAL  |      50.22         |     41.72    |      16,9%            |
|------------------|--------------------|--------------|-----------------------|
| SERVIDOR DELL    |       4.86         |      6.18    |      21,4%            |
'------------------------------------------------------------------------------'

 

Referências

http://www.oracle.com/technetwork/pt/articles/database-performance/oracle-sql-loader-3703843-ptb.html

https://oracle-base.com/articles/9i/external-tables-9i

 

Mídia social

 

Deixe uma resposta

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