SQLLDR x CTAS: Qual recurso tem melhor desempenho?

agosto 3, 2017 V. J.A. Tuning Tools

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”.

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

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% |
'------------------------------------------------------------------------------'