{"id":1114,"date":"2017-08-03T22:42:56","date_gmt":"2017-08-04T01:42:56","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1114"},"modified":"2019-11-11T18:50:37","modified_gmt":"2019-11-11T21:50:37","slug":"sqlldr-x-ctas-qual-recurso-tem-melhor-desempenho","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sqlldr-x-ctas-qual-recurso-tem-melhor-desempenho\/","title":{"rendered":"SQLLDR x CTAS: Qual recurso tem melhor desempenho?"},"content":{"rendered":"

Um processo muito utilizado nos bancos de dados \u00e9 a carga de dados em tabelas, esse processo pode ser feito de varias formas e utilizando in\u00fameras ferramentas, neste artigo vamos testar o desempenho de duas dessas ferramentas e verificar qual delas \u00e9 mais eficiente (SQLLDR ou CTAS), esses recursos suportam paralelismo mas em nosso teste n\u00e3o vamos utilizar a op\u00e7\u00e3o de paralelismo.<\/span><\/p>\n

Os dois m\u00e9todos v\u00e3o utilizar uma massa de dados em formato texto que vai estar dispon\u00edvel em um filesystem no servidor do banco Oracle, essa massa de dados tem 2.000.000 linhas e ocupa um espa\u00e7o de 417M.<\/span><\/p>\n

Vamos realizar a simula\u00e7\u00e3o de carga desse arquivo com o utilit\u00e1rio SQL*LOADER (SQLLDR) e com a DDL “CREATE TABLE AS SELECT” que vai ler os dados de origem de uma tabela “EXTERNAL TABLE”.<\/span><\/p>\n

Demonstra\u00e7\u00e3o pr\u00e1tica<\/span><\/h2>\n

1) Gerar a massa de dados em formato TXT no servidor de banco<\/span>
\n2) Fazer a carga no banco utilizando SQL*LOADER <\/span>
\n3) Fazer a carga no banco utilizando CTAS com EXTERNAL TABLE<\/span><\/p>\n

 <\/p>\n

1) Gerar a massa de dados em formato TXT<\/span><\/h2>\n

Para gerar a massa de dados vamos criar uma tabela normal no banco de dados com 2.000.000 linhas, criar um diret\u00f3rio 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\u00f3rio criado.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\n
\nSQL><\/span>
\nSQL><\/span> alter session set workarea_size_policy=<\/span>MANUAL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> alter session set sort_area_size=<\/span>2000000000<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span> create table tab_dbtw01
\n  2<\/span>  as<\/span>
\n  3<\/span>  select rownum                                        id,<\/span>
\n  4<\/span>         mod(<\/span>rownum,<\/span>100<\/span>)<\/span>                           codvar,<\/span>
\n  5<\/span>         trunc(<\/span>dbms_random.<\/span>value(<\/span>0<\/span>,<\/span>100000<\/span>)<\/span>)<\/span>  categoria,<\/span>
\n  6<\/span>         lpad(<\/span>rownum,<\/span>10<\/span>,<\/span>'0'<\/span>)<\/span>                  criterio,<\/span>
\n  7<\/span>         rpad(<\/span>'x'<\/span>,<\/span>60<\/span>,<\/span>'x'<\/span>)<\/span>                       texto1,<\/span>
\n  8<\/span>         rpad(<\/span>'y'<\/span>,<\/span>60<\/span>,<\/span>'y'<\/span>)<\/span>                       texto2,<\/span>
\n  9<\/span>         rpad(<\/span>'z'<\/span>,<\/span>60<\/span>,<\/span>'z'<\/span>)<\/span>                       texto3
\n 10<\/span>    from dual connect by level<=<\/span>2e6<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> conn sys\/<\/span>oracle@<\/span>lab11 as<\/span> sysdba
\nConectado.<\/span>
\nSQL><\/span>
\nSQL><\/span> create directory DBTW as<\/span> '\/oracle\/load'<\/span>;<\/span>
\n
\nDiret\u00f3rio criado.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> grant read,<\/span>write on directory DBTW to CURSO01;<\/span>
\n
\nConcess\u00e3o bem-<\/span>sucedida.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> DECLARE<\/span>
\n  2<\/span>    v_finaltxt  VARCHAR2(<\/span>4000<\/span>)<\/span>;<\/span>
\n  3<\/span>    v_v_val     VARCHAR2(<\/span>4000<\/span>)<\/span>;<\/span>
\n  4<\/span>    v_n_val     NUMBER;<\/span>
\n  5<\/span>    v_d_val     DATE<\/span><\/a>;<\/span>
\n  6<\/span>    v_ret       NUMBER;<\/span>
\n  7<\/span>    c           NUMBER;<\/span>
\n  8<\/span>    d           NUMBER;<\/span>
\n  9<\/span>    col_cnt     INTEGER;<\/span>
\n 10<\/span>    rec_tab     DBMS_SQL.<\/span>DESC_TAB;<\/span>
\n 11<\/span>    col_num     NUMBER;<\/span>
\n 12<\/span>    v_fh        UTL_FILE.<\/span>FILE_TYPE;<\/span>
\n 13<\/span>    p_data_file VARCHAR2(<\/span>20<\/span>)<\/span> :=<\/span> 'et_dbtw01.txt'<\/span>;<\/span>
\n 14<\/span>    p_dir       VARCHAR2(<\/span>20<\/span>)<\/span> :=<\/span> 'DBTW'<\/span>;<\/span>
\n 15<\/span>    p_sql       VARCHAR2(<\/span>4000<\/span>)<\/span> :=<\/span> 'SELECT * FROM TAB_DBTW01'<\/span>;<\/span>
\n 16<\/span>  BEGIN
\n 17<\/span>    c :=<\/span> DBMS_SQL.<\/span>OPEN_CURSOR;<\/span>
\n 18<\/span>    DBMS_SQL.<\/span>PARSE(<\/span>c,<\/span> p_sql,<\/span> DBMS_SQL.<\/span>NATIVE)<\/span>;<\/span>
\n 19<\/span>    d :=<\/span> DBMS_SQL.<\/span>EXECUTE(<\/span>c)<\/span>;<\/span>
\n 20<\/span>    DBMS_SQL.<\/span>DESCRIBE_COLUMNS(<\/span>c,<\/span> col_cnt,<\/span> rec_tab)<\/span>;<\/span>
\n 21<\/span>    FOR<\/span> j in 1<\/span>..<\/span>col_cnt
\n 22<\/span>    LOOP
\n 23<\/span>      CASE<\/span> rec_tab(<\/span>j)<\/span>.<\/span>col_type
\n 24<\/span>        WHEN 1<\/span> THEN DBMS_SQL.<\/span>DEFINE_COLUMN(<\/span>c,<\/span>j,<\/span>v_v_val,<\/span>2000<\/span>)<\/span>;<\/span>
\n 25<\/span>        WHEN 2<\/span> THEN DBMS_SQL.<\/span>DEFINE_COLUMN(<\/span>c,<\/span>j,<\/span>v_n_val)<\/span>;<\/span>
\n 26<\/span>        WHEN 12<\/span> THEN DBMS_SQL.<\/span>DEFINE_COLUMN(<\/span>c,<\/span>j,<\/span>v_d_val)<\/span>;<\/span>
\n 27<\/span>      ELSE<\/span>
\n 28<\/span>        DBMS_SQL.<\/span>DEFINE_COLUMN(<\/span>c,<\/span>j,<\/span>v_v_val,<\/span>2000<\/span>)<\/span>;<\/span>
\n 29<\/span>      END<\/span><\/a> CASE<\/span>;<\/span>
\n 30<\/span>    END<\/span><\/a> LOOP;<\/span>
\n 31<\/span>    --<\/span>
\n 32<\/span>    --<\/span> This part outputs the DATA
\n 33<\/span>    --<\/span>
\n 34<\/span>    v_fh :=<\/span> UTL_FILE.<\/span>FOPEN<\/span><\/a>(<\/span>upper(<\/span>p_dir)<\/span>,<\/span>p_data_file,<\/span>'w'<\/span>,<\/span>32767<\/span>)<\/span>;<\/span>
\n 35<\/span>    LOOP
\n 36<\/span>      v_ret :=<\/span> DBMS_SQL.<\/span>FETCH_ROWS(<\/span>c)<\/span>;<\/span>
\n 37<\/span>      EXIT<\/span><\/a> WHEN v_ret =<\/span> 0<\/span>;<\/span>
\n 38<\/span>      v_finaltxt :=<\/span> NULL<\/span>;<\/span>
\n 39<\/span>      FOR<\/span> j in 1<\/span>..<\/span>col_cnt
\n 40<\/span>      LOOP
\n 41<\/span>        CASE<\/span> rec_tab(<\/span>j)<\/span>.<\/span>col_type
\n 42<\/span>          WHEN 1<\/span> THEN DBMS_SQL.<\/span>COLUMN_VALUE(<\/span>c,<\/span>j,<\/span>v_v_val)<\/span>;<\/span>
\n 43<\/span>                      v_finaltxt :=<\/span> ltrim<\/span><\/a>(<\/span>v_finaltxt||<\/span>',"'<\/span>||<\/span>v_v_val||<\/span>'"'<\/span>,<\/span>','<\/span>)<\/span>;<\/span>
\n 44<\/span>          WHEN 2<\/span> THEN DBMS_SQL.<\/span>COLUMN_VALUE(<\/span>c,<\/span>j,<\/span>v_n_val)<\/span>;<\/span>
\n 45<\/span>                      v_finaltxt :=<\/span> ltrim<\/span><\/a>(<\/span>v_finaltxt||<\/span>','<\/span>||<\/span>v_n_val,<\/span>','<\/span>)<\/span>;<\/span>
\n 46<\/span>          WHEN 12<\/span> THEN DBMS_SQL.<\/span>COLUMN_VALUE(<\/span>c,<\/span>j,<\/span>v_d_val)<\/span>;<\/span>
\n 47<\/span>                      v_finaltxt :=<\/span> ltrim<\/span><\/a>(<\/span>v_finaltxt||<\/span>','<\/span>||<\/span>to_char(<\/span>v_d_val,<\/span>'DD\/MM\/YYYY HH24:MI:SS'<\/span>)<\/span>,<\/span>','<\/span>)<\/span>;<\/span>
\n 48<\/span>        ELSE<\/span>
\n 49<\/span>          v_finaltxt :=<\/span> ltrim<\/span><\/a>(<\/span>v_finaltxt||<\/span>',"'<\/span>||<\/span>v_v_val||<\/span>'"'<\/span>,<\/span>','<\/span>)<\/span>;<\/span>
\n 50<\/span>        END<\/span><\/a> CASE<\/span>;<\/span>
\n 51<\/span>      END<\/span><\/a> LOOP;<\/span>
\n 52<\/span>      UTL_FILE.<\/span>PUT_LINE(<\/span>v_fh,<\/span> v_finaltxt)<\/span>;<\/span>
\n 53<\/span>    END<\/span><\/a> LOOP;<\/span>
\n 54<\/span>    UTL_FILE.<\/span>FCLOSE<\/span><\/a>(<\/span>v_fh)<\/span>;<\/span>
\n 55<\/span>    DBMS_SQL.<\/span>CLOSE_CURSOR(<\/span>c)<\/span>;<\/span>
\n 56<\/span>  END<\/span><\/a>;<\/span>
\n 57<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Fazer a carga no banco utilizando SQL*LOADER<\/strong><\/span><\/h2>\n

Para realizar a carga atrav\u00e9s do utilit\u00e1rio SQL*LOADER, vamos criar uma tabela no banco onde ser\u00e3o 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”.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> CREATE TABLE "CURSO01"<\/span>.<\/span>"CARGA_DBTW02"<\/span>
\n  2<\/span>        (<\/span>    "ID"<\/span> NUMBER,<\/span>
\n  3<\/span>             "CODVAR"<\/span> NUMBER,<\/span>
\n  4<\/span>             "CATEGORIA"<\/span> NUMBER,<\/span>
\n  5<\/span>             "CRITERIO"<\/span> VARCHAR2(<\/span>10<\/span>)<\/span>,<\/span>
\n  6<\/span>             "TEXTO1"<\/span> VARCHAR2(<\/span>60<\/span>)<\/span>,<\/span>
\n  7<\/span>             "TEXTO2"<\/span> VARCHAR2(<\/span>60<\/span>)<\/span>,<\/span>
\n  8<\/span>             "TEXTO3"<\/span> VARCHAR2(<\/span>60<\/span>)<\/span>
\n  9<\/span>        )<\/span>
\n 10<\/span>       TABLESPACE "USERS"<\/span> ;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL><\/span>
\n
\n[<\/span>oracle@<\/span>odbsrv11 ~]<\/span>$ cd \/<\/span>oracle\/<\/span>load
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$ ll
\ntotal 426668<\/span>
\n-<\/span>rw-<\/span>r--<\/span>r--<\/span> 1<\/span> oracle oinstall       330<\/span> Jul 24<\/span> 18<\/span>:<\/span>42<\/span> carga_dbtw02.<\/span>ctl
\n-<\/span>rw-<\/span>r--<\/span>r--<\/span> 1<\/span> oracle oinstall 436466791<\/span> Jul 24<\/span> 18<\/span>:<\/span>33<\/span> et_dbtw01.<\/span>txt
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$ cat carga_dbtw02.<\/span>ctl
\noptions (<\/span>errors=<\/span>50<\/span>)<\/span>
\nload data
\n characterset WE8ISO8859P1
\n infile '\/oracle\/load\/et_dbtw01.txt'<\/span>
\n badfile '\/oracle\/load\/carga_dbtw02.bad'<\/span>
\n discardfile '\/oracle\/load\/carga_dbtw02.dsc'<\/span>
\n into table carga_dbtw02
\n fields terminated by ","<\/span>
\n optionally enclosed by '"'<\/span>
\n (<\/span> id,<\/span> codvar,<\/span> categoria,<\/span> criterio,<\/span> texto1,<\/span> texto2,<\/span> texto3 )<\/span>
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$ sqlldr CURSO01\/<\/span>oracle  control=<\/span>carga_dbtw02.<\/span>ctl direct=<\/span>true<\/span>
\n
\nSQL*<\/span>Loader:<\/span> Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production on Mon Jul 24<\/span> 18<\/span>:<\/span>49<\/span>:<\/span>40<\/span> 2017<\/span>
\n
\nCopyright (<\/span>c)<\/span> 1982<\/span>,<\/span> 2011<\/span>,<\/span> Oracle and\/<\/span>or its affiliates.<\/span>  All rights reserved.<\/span>
\n
\n
\nLoad completed -<\/span> logical record count<\/span><\/a> 2000000<\/span>.<\/span>
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$
\n[<\/span>oracle@<\/span>odbsrv11 load]<\/span>$ cat carga_dbtw02.<\/span>log<\/span><\/a>
\n
\nSQL*<\/span>Loader:<\/span> Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production on Mon Jul 24<\/span> 18<\/span>:<\/span>49<\/span>:<\/span>40<\/span> 2017<\/span>
\n
\nCopyright (<\/span>c)<\/span> 1982<\/span>,<\/span> 2011<\/span>,<\/span> Oracle and\/<\/span>or its affiliates.<\/span>  All rights reserved.<\/span>
\n
\nControl File<\/span><\/a>:<\/span>   carga_dbtw02.<\/span>ctl
\nCharacter Set WE8ISO8859P1 specified for<\/span> all input.<\/span>
\n
\nData File<\/span><\/a>:<\/span>      \/<\/span>oracle\/<\/span>load\/<\/span>et_dbtw01.<\/span>txt
\n  Bad File<\/span><\/a>:<\/span>     \/<\/span>oracle\/<\/span>load\/<\/span>carga_dbtw02.<\/span>bad
\n  Discard File<\/span><\/a>:<\/span> \/<\/span>oracle\/<\/span>load\/<\/span>carga_dbtw02.<\/span>dsc
\n (<\/span>Allow all discards)<\/span>
\n
\nNumber to load:<\/span> ALL
\nNumber to skip:<\/span> 0<\/span>
\nErrors allowed:<\/span> 50<\/span>
\nContinuation:<\/span>    none specified
\nPath used:<\/span>      Direct
\n
\nTable CARGA_DBTW02,<\/span> loaded from every logical record.<\/span>
\nInsert option in effect for<\/span> this table:<\/span> INSERT
\n
\n   Column Name                  Position   Len  Term Encl Datatype
\n------------------------------<\/span> ----------<\/span> -----<\/span> ----<\/span> ----<\/span> ---------------------<\/span>
\nID                                  FIRST     *<\/span>   ,<\/span>  O(<\/span>") CHARACTER
\nCODVAR                               NEXT     *   ,  O("<\/span>)<\/span> CHARACTER
\nCATEGORIA                            NEXT<\/span><\/a>     *<\/span>   ,<\/span>  O(<\/span>") CHARACTER
\nCRITERIO                             NEXT     *   ,  O("<\/span>)<\/span> CHARACTER
\nTEXTO1                               NEXT<\/span><\/a>     *<\/span>   ,<\/span>  O(<\/span>") CHARACTER
\nTEXTO2                               NEXT     *   ,  O("<\/span>)<\/span> CHARACTER
\nTEXTO3                               NEXT<\/span><\/a>     *<\/span>   ,<\/span>  O(<\/span>") CHARACTER
\n
\n
\nTable CARGA_DBTW02:
\n  2000000 Rows successfully loaded.
\n  0 Rows not loaded due to data errors.
\n  0 Rows not loaded because all WHEN clauses were failed.
\n  0 Rows not loaded because all fields were null.
\n
\nBind array size not used in direct path.
\nColumn array  rows :    5000
\nStream buffer bytes:  256000
\nRead   buffer bytes: 1048576
\n
\nTotal logical records skipped:          0
\nTotal logical records read:       2000000
\nTotal logical records rejected:         0
\nTotal logical records discarded:        0
\nDirect path multithreading optimization is disabled
\n
\nRun began on Mon Jul 24 18:49:40 2017
\nRun ended on Mon Jul 24 18:50:30 2017
\n
\nElapsed time was:     00:00:50.22
\nCPU time was:         00:00:03.82
\n[oracle@odbsrv11 load]$<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Para evitar que as \u00e1reas de cache do sistema operacional interferissem no resultado final, realizamos a carga duas vezes e na log acima inclu\u00edmos somente o resultado da segunda execu\u00e7\u00e3o que foi mais r\u00e1pida 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).<\/span><\/p>\n

3) Fazer a carga no banco utilizando CTAS com EXTERNAL TABLE<\/strong><\/span><\/h2>\n

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\u00eancia executar a cria\u00e7\u00e3o da tabela no banco lendo os registros da External Table.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> CREATE TABLE et_dbtw01 (<\/span>
\n  2<\/span>    id                       NUMBER,<\/span>
\n  3<\/span>    codvar                   NUMBER,<\/span>
\n  4<\/span>    categoria                NUMBER,<\/span>
\n  5<\/span>    criterio              VARCHAR2(<\/span>10<\/span>)<\/span>,<\/span>
\n  6<\/span>    texto1                   VARCHAR2(<\/span>60<\/span>)<\/span>,<\/span>
\n  7<\/span>    texto2                   VARCHAR2(<\/span>60<\/span>)<\/span>,<\/span>
\n  8<\/span>    texto3           VARCHAR2(<\/span>60<\/span>)<\/span>
\n  9<\/span>  )<\/span>
\n 10<\/span>  ORGANIZATION EXTERNAL (<\/span>
\n 11<\/span>    TYPE ORACLE_LOADER
\n 12<\/span>    DEFAULT<\/span> DIRECTORY DBTW
\n 13<\/span>    ACCESS PARAMETERS (<\/span>
\n 14<\/span>      RECORDS DELIMITED BY NEWLINE
\n 15<\/span>      FIELDS TERMINATED BY ','<\/span>
\n 16<\/span>      OPTIONALLY ENCLOSED BY '"'<\/span>
\n 17<\/span>      MISSING FIELD VALUES ARE NULL<\/span>
\n 18<\/span>      (<\/span>
\n 19<\/span>        id           CHAR(<\/span>32<\/span>)<\/span>,<\/span>
\n 20<\/span>        codvar               CHAR(<\/span>32<\/span>)<\/span>,<\/span>
\n 21<\/span>        categoria            CHAR(<\/span>32<\/span>)<\/span>,<\/span>
\n 22<\/span>        criterio          CHAR(<\/span>10<\/span>)<\/span>,<\/span>
\n 23<\/span>        texto1       CHAR(<\/span>60<\/span>)<\/span>,<\/span>
\n 24<\/span>        texto2               CHAR(<\/span>60<\/span>)<\/span>,<\/span>
\n 25<\/span>        texto3               CHAR(<\/span>60<\/span>)<\/span>
\n 26<\/span>      )<\/span>
\n 27<\/span>    )<\/span>
\n 28<\/span>    LOCATION (<\/span>'et_dbtw01.txt'<\/span>)<\/span>
\n 29<\/span>  )<\/span>
\n 30<\/span>  REJECT LIMIT UNLIMITED;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL><\/span> set timing on;<\/span>
\nSQL><\/span> create table CARGA_DBTW03 as<\/span> select *<\/span> from  ET_DBTW01;<\/span>
\n
\nTabela criada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>01<\/span>:<\/span>26.21<\/span>
\nSQL><\/span> drop table CARGA_DBTW03 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05.44<\/span>
\nSQL><\/span> create table CARGA_DBTW03 as<\/span> select *<\/span> from  ET_DBTW01;<\/span>
\n
\nTabela criada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>41.72<\/span>
\nSQL><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> from CARGA_DBTW03;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n   2000000<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>09.68<\/span>
\nSQL><\/span> select segment_name,<\/span> round<\/span><\/a>(<\/span>bytes)<\/span> from user_segments where segment_name like 'CARGA_DBTW%'<\/span>;<\/span>
\n
\nSEGMENT_NAME                                                                      ROUND<\/span><\/a>(<\/span>BYTES)<\/span>
\n---------------------------------------------------------------------------------<\/span> ------------<\/span>
\nCARGA_DBTW02                                                                         486539264<\/span>
\nCARGA_DBTW03                                                                         494927872<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.33<\/span>
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Como foi feito no SQL*LOADER executamos o CTAS duas vezes para eliminar a interfer\u00eancia das \u00e1reas de cache e vamos considerar somente a segunda carga que foi executada em 41.72 segundos (linha 49).<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Baseado nos testes acima, realizados numa m\u00e1quina virtual com recursos limitados, podemos concluir que o CTAS foi 16,9% mais r\u00e1pido 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\u00e1pido que o CTAS. Como podemos observar os recursos de memoria, configura\u00e7\u00f5es e capacidade do processador influenciaram o resultado destes testes. Convido voc\u00ea a reproduzir este teste e publicar aqui o resultados incluindo a configura\u00e7\u00e3o do servidor e do banco de dados como refer\u00eancia.<\/span><\/p>\n

Configura\u00e7\u00f5es das m\u00e1quinas testadas<\/span><\/h2>\n

MAQUINA VIRTUA<\/strong>L
\nLinux 2.6.18-164.el5 i686
\n1 processador
\n1024M Memoria RAM
\nIntel(R) Core(TM) i5-2450M CPU @ 2.50GHz<\/p>\n

BANCO ORACLE
\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
\nMEMORY_TARGET = 632M
\nSERVIDOR DELL<\/strong>
\nLinux 2.6.32-642.6.2.el6.x86_64
\n16 processadores
\n193649M Memoria RAM
\nIntel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz<\/p>\n

BANCO ORACLE
\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
\nSGA_TARGET = 2000M
\nPGA_AGGREGATE_TARGET = 800M
\n <\/p>\n

1
2
3
4
5
6
7
<\/div><\/td>
.<\/span>______________________________________________________________________________.<\/span>
\n|<\/span>  SERVIVOR        |<\/span>  TEMPO SQL*<\/span>LOADER  |<\/span>  TEMPO CTAS  |<\/span>  DIFEREN\u00c7A PERCENTUAL |<\/span>
\n|------------------|--------------------|--------------|-----------------------|<\/span>
\n|<\/span> MAQUINA VIRTUAL<\/span><\/a>  |<\/span>      50.22<\/span>         |<\/span>     41.72<\/span>    |<\/span>      16<\/span>,<\/span>9<\/span>%<\/span>            |<\/span>
\n|------------------|--------------------|--------------|-----------------------|<\/span>
\n|<\/span> SERVIDOR DELL    |<\/span>       4.86<\/span>         |<\/span>      6.18<\/span>    |<\/span>      21<\/span>,<\/span>4<\/span>%<\/span>            |<\/span>
\n'------------------------------------------------------------------------------'<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Refer\u00eancias<\/strong><\/span><\/h2>\n

http:\/\/www.oracle.com\/technetwork\/pt\/articles\/database-performance\/oracle-sql-loader-3703843-ptb.html<\/a><\/strong><\/span><\/p>\n

https:\/\/oracle-base.com\/articles\/9i\/external-tables-9i<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Um processo muito utilizado nos bancos de dados \u00e9 a carga de dados em tabelas, esse processo pode ser feito de varias formas e utilizando in\u00fameras ferramentas, neste artigo vamos testar o desempenho de duas dessas ferramentas e verificar qual delas \u00e9 mais eficiente (SQLLDR ou CTAS), esses recursos suportam paralelismo mas em nosso teste n\u00e3o vamos utilizar a op\u00e7\u00e3o<\/p>\n","protected":false},"author":2,"featured_media":1113,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[174,175,172,173],"class_list":["post-1114","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tuning-tools","tag-ctas","tag-external-table","tag-sqlloader","tag-sqlldr"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=1114"}],"version-history":[{"count":12,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1114\/revisions"}],"predecessor-version":[{"id":2083,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1114\/revisions\/2083"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1113"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1114"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}