Como hacer mas rapida la importacion de datos.

En un post anterior mencione como migrar una base de datos de un servidor a otro pero nunca di ejemplos de como hacerlo. Debido a que algunas personas han leido mi blog y me han hecho preguntas de como se hace en ciertos casos decidi mejor documentar el proceso para ciertos casos.

Como había mencionado en ese post existen maneras sencillas y complejas, además que cada base de datos es diferente y se encuentra en diferente hardware,etc. Así que tal vez las siguientes soluciones no den el mismo resultado en su ambiente.

Primero que nada vamos a crear unas tablas de ejemplo.


SQL> ed
Wrote file afiedt.buf

1 create table t1
 2 as
 3 with generator as(
 4 select rownum id
 5 from dual
 6 connect by level <=100000
 7 )
 8 select rownum id,
 9 lpad(rownum,10,'0') small_vc,
 10 rpad('x',100) padding,
 11 lpad(rownum,5,'0') smaller_vc
 12 from
 13 generator v1,
 14 generator v2
 15* where rownum <= 500000
SQL> /

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL> create table t3 as select * from t1;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
 500000

SQL>

SQL>

Una vez creada las tablas vamos a crear un par de indices.


create index idx1t1 on t1(id);
create index idx2t1 on t1(small_vc);
create index idx3t1 on t1(smaller_vc);

create index idx1t2 on t2(id);
create index idx2t2 on t2(small_vc);
create index idx3t2 on t2(smaller_vc);

create index idx1t3 on t3(id);
create index idx2t3 on t3(small_vc);
create index idx3t3 on t3(smaller_vc);

Ahora que las tabla tienen medio millón de registros y sus respectivos indices vamos  exportar el usuario.


[oracle@localhost exports][21:25:14]$ exp file=delfino_exp.dmp userid=delfino/delfino owner=delfino

Export: Release 11.2.0.3.0 - Production on Thu Apr 12 21:25:23 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DELFINO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DELFINO
About to export DELFINO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DELFINO's tables via Conventional Path ...
. . exporting table T1 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table T2 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table T3 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@localhost exports][21:25:33]$ exp file=delfino_exp_direct.dmp userid=delfino/delfino owner=delfino direct=Y

Export: Release 11.2.0.3.0 - Production on Thu Apr 12 21:25:48 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DELFINO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DELFINO
About to export DELFINO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DELFINO's tables via Direct Path ...
. . exporting table T1 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table T2 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table T3 500000 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@localhost exports][21:26:01]$ ls -lh

total 367M
-rw-r--r-- 1 oracle oinstall 182M Apr 12 21:26 delfino_exp_direct.dmp
-rw-r--r-- 1 oracle oinstall 185M Apr 12 21:25 delfino_exp.dmp

ok ahora podemos ver que hice 2 tipos de export, uno normal y otro con direct=Y, el primero tomó 10 segundos y el segundo 13 segundos, así que la diferencia no es muy significativa en este caso.

Ahora vamos a hacer el import en otro usuario.

Primero hacemos un import normal, con todo por default.


[oracle@localhost exports][21:34:57]$ imp userid=delfino/delfino file=delfino_exp.dmp fromuser=delfino touser=delfino2

Import: Release 11.2.0.3.0 - Production on Thu Apr 12 21:36:29 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by DELFINO, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing DELFINO's objects into DELFINO2
. . importing table "T1" 500000 rows imported
. . importing table "T2" 500000 rows imported
. . importing table "T3" 500000 rows imported
Import terminated successfully without warnings.
[oracle@localhost exports][21:37:41]$

El import tomó 1 min 12 segundos (72 segundos)

El siguiente ejemplo es un import sin indices, primero creamos el archivo con los indices y luego creamos los indices aparte.


[oracle@localhost exports][22:29:04]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=Y indexfile=indices.sql rows=N fromuser=delfino touser=delfino2

Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:29:54 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
Import terminated successfully without warnings.

[oracle@localhost exports][22:29:54]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=N rows=Y fromuser=delfino touser=delfino2

Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:30:20 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing DELFINO's objects into DELFINO2
. . importing table "T1" 500000 rows imported
. . importing table "T2" 500000 rows imported
. . importing table "T3" 500000 rows imported
Import terminated successfully without warnings.
[oracle@localhost exports][22:30:59]$

SQL> connect delfino2/delfino
Connected.
SQL> set timing on;
SQL> @indices.sql
Enter password:
Connected.

Index created.

Elapsed: 00:00:00.45

Index created.

Elapsed: 00:00:04.07

Index created.

Elapsed: 00:00:00.73

Index created.

Elapsed: 00:00:00.86

Index created.

Elapsed: 00:00:02.91

Index created.

Elapsed: 00:00:03.05

Index created.

Elapsed: 00:00:02.86

Index created.

Elapsed: 00:00:00.93

Index created.

Elapsed: 00:00:00.81
SQL>

En este caso el import tomo solo 39 segundos sin tomar en cuenta lo que toma reconstruir los indices (~12 segundos). Eso fue una reducción de 33 segundos comparado con el anterior. Insignificante en este caso pero hubo una reducción.

Ahora si agregamos otros parametros podemos reducir un poco mas el tiempo.


[oracle@localhost exports][22:38:24]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=N rows=Y fromuser=delfino touser=delfino2 buffer=32000 commit=N statistics=none

Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:38:33 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing DELFINO's objects into DELFINO2
. . importing table "T1" 500000 rows imported
. . importing table "T2" 500000 rows imported
. . importing table "T3" 500000 rows imported
Import terminated successfully without warnings.
[oracle@localhost exports][22:39:04]$

En este caso el tiempo se redujo a 31 segundos, 8 segundos menos.

En resumen, en realidad se puede reducir el tiempo del import utilizando diferentes parámetros y aplicando ciertas técnicas. Este ejemplo fue muy rápido y en realidad las tablas son muy pequeñas como para representar una migración grande pero da una idea de como reducir el tiempo de importación.

Otras cosas que se pueden hacer para reducir el tiempo de importación:

  • Poner el o los archives exportados en discos diferentes a donde se encuentran los “datafiles” de la base de datos para evitar contención en los discos.
  • Remover el archiving de la base de datos para que se generen menos logs (menos I/O).
  • Utilizar data pump y si es posible utilizar procesos paralelos.
  • Utilizar imports en paralelo. Esto se logra exportando ciertas tablas en varios archives y corriendo varios imports separados por cada archivo.
  • Y debe haber otros métodos que no recuerdo por el momento…
  1. #1 by bettinaoneal31633 on 10 April, 2016 - 00:38

    Me faltan Shimokitazawa y Takatanobaba! La pru00f3xima vez seru00e1! XDD Click http://tu2s.in/searchll100830

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: