Costo super alto!

En Febrero del 2008, publiqué un post llamado "¿Tunear en base al COSTO del plan de ejecución?". En ese post, les decía que NO hay que tunear en base al costo del plan de ejecución, sino al trabajo que realiza la consulta en la base de datos.

Voy a mostrarles una consulta en el que el costo es super alto pero el trabajo que realiza en la base de datos no lo es:

SQL_10gR2> explain plan for
2  SELECT (t5.column_value).getstringval() t5
3  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
4       TABLE(xmlsequence(t1.column_value))t2,
5       TABLE(xmlsequence(t2.column_value))t3,
6       TABLE(xmlsequence(t3.column_value))t4,
7       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   1 |  NESTED LOOPS                         |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   2 |   NESTED LOOPS                        |                        |  4451T|    31P|    12T  (2)|999:59:59 |
|   3 |    NESTED LOOPS                       |                        |   544G|  3045G|  1481M  (2)|999:59:59 |
|   4 |     NESTED LOOPS                      |                        |    66M|   254M|   181K  (2)| 00:36:18 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Wow!!! Y esos números??? Extremadamente altos no??? Bueno, según el plan de ejecución, esa consulta retorna 18 cuatrillones (18E) de registros, 15 exabytes (15E), tiene un costo de 1.8E19 (98P) y el tiempo de ejecución es de aproximadamente 1 mes (999:59:59)!!!

Veamos qué sucede cuando ejecutamos la consulta y obtenemos las estadísticas de la misma:

SQL_10gR2> SELECT (t5.column_value).getstringval() t5
2  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
3       TABLE(xmlsequence(t1.column_value))t2,
4       TABLE(xmlsequence(t2.column_value))t3,
5       TABLE(xmlsequence(t3.column_value))t4,
6       TABLE(xmlsequence(t4.column_value))t5;

T5
----------------------------------------------------------------
< x/ >

1 fila seleccionada.

Transcurrido: 00:00:00.04

Estadísticas
----------------------------------------------------------
    0  recursive calls
    0  db block gets
     3  consistent gets
    0  physical reads
    0  redo size
  425  bytes sent via SQL*Net to client
  381  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
    1  rows processed

Como podemos observar, la consulta retorna un sólo registro y se ejecuta en tan solo 4 milisegundos, consumiendo sólo 3 bloques de datos.

Este ejemplo es otra prueba de cómo el optimizador puede calcular valores totalmente erróneos en el plan de ejecución. Es por eso, que hay que tener especial cuidado al ver esos números cuando obtenemos un plan de ejecución y al tratar de optimizar la consulta en base a esos números.

Para ésta consulta en particular, el problema es que el optimizador desconoce las estadísticas de las tablas (que en éste caso no son tablas, son llamadas a funciones). Fácilmente, podemos "mejorar" el plan de ejecución, agregando el hint CARDINALITY para decirle al optimizador cuántos registros va a retornar esa función... que en éste caso, es un sólo registro:

SQL_10gR2> explain plan for
2  SELECT /*+ cardinality(t1 1) cardinality(t2 1) cardinality(t3 1) cardinality(t4 1) cardinality(t5 1) */
3        (t5.column_value).getstringval() t5
4  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
5       TABLE(xmlsequence(t1.column_value))t2,
6       TABLE(xmlsequence(t2.column_value))t3,
7       TABLE(xmlsequence(t3.column_value))t4,
8       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   1 |  NESTED LOOPS                         |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                        |                        |     1 |     8 |    97   (2)| 00:00:02 |
|   3 |    NESTED LOOPS                       |                        |     1 |     6 |    73   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                        |     1 |     4 |    49   (3)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Recuerden siempre el acrónimo GIGO: Garbage In ... Garbage Out :)

DML Error Logging

Alguna vez trataron de actualizar 10 millones de registros? Que sucedía cuando uno de los registros fallaba? Oracle realizaba un rollback automático de los cambios... y qué sucede si el registro que falló era uno de los últimos en actualizarse? Y bueno... seguramente desperdiciamos todos el tiempo de ejecución de ese proceso ya que los cambios se perdieron tan solo porque un registro falló! A muchos les habrá pasado lo mismo al ejecutar una sentencia del tipo INSERT AS SELECT cierto? En donde uno de los registros no pudo insertarse por X motivo y por consecuencia toda la sentencia falló!

Generalmente, sabemos que la manera más rápida de realizar un DML es en una sola sentencia. Cuando tenemos una sentencia en donde pueden haber registros que terminen en error, se solía armar un procedimiento que recorra los datos que se quieren insertar, updatear, eliminar, etc. e ir ejecutando la senetencia de a un/o varios registros a la vez, y los registros que fallaban, se insertaban en una tabla de errores. Este procedimiento suele ser muy lento, ya que no estamos realizando un DML en una sola sentencia y en una sola vez para todos los registros; sino que estamos recorriendo los datos proceduralmente y realizando el DML de a X cantidad de registros a la vez con el fin de loguear los registros que terminaron en error e ir comiteando, a medida que se va ejecutando la sentencia DML, los registros que terminan satisfactoriamente.

En Oracle 10g Release 2, existe una nueva funcionalidad llamada "DML Error Logging" que nos permite ejecutar una sentencia DML de "principio a fin"... y si en el transcurso de ejecución de esa sentencia, uno o más registros fallaran, esos registros se loguean en una tabla de errores para que luego podramos corregirlos, sin necesidad, de volver a insertar todos los registros nuevamente ya que sólo tendremos que volver a insertar los que terminaron en error.
Por ejemplo: Si intentamos insertar 1 millón de registros, y sólo un registro falla, se insertarán en la tabla final 999,999 de registros, y el registro que terminó en error, se loguea en otra tabla para que luego podamos corregirlo y volverlo a insertar. Acaso no es sensacional ésto???

Veamos un ejemplo muy simple:

Creamos una tabla llamada TEST, en donde vamos a insertar 1 millón de registros.

SQL_10gR2> CREATE TABLE test
2  (
3  ID     NUMBER,
4  NOMBRE VARCHAR2(7)
5  );

Tabla creada.

Creamos una tabla llamada ERROR_LOG_TEST que apunta a la tabla TEST. Esta tabla va a contener todos los registros que terminen en error cuando queramos realizar un DML en la tabla TEST.

SQL_10gR2> EXEC dbms_errlog.create_error_log('TEST','ERROR_LOG_TEST') ;

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> DESC error_log_test

Nombre                                                Nulo?    Tipo
----------------------------------------------------- -------- ------------------------------------
ORA_ERR_NUMBER$                                                NUMBER
ORA_ERR_MESG$                                                  VARCHAR2(2000)
ORA_ERR_ROWID$                                                 ROWID
ORA_ERR_OPTYP$                                                 VARCHAR2(2)
ORA_ERR_TAG$                                                   VARCHAR2(2000)
ID                                                             VARCHAR2(4000)
NOMBRE                                                         VARCHAR2(4000)

Bien, ahora vamos a insertar los registros en la tabla TEST.

SQL_10gR2> INSERT INTO test
2  SELECT level, 'nom_'||level
3  FROM dual
4  CONNECT BY level <= 1000000   
5  LOG ERRORS INTO ERROR_LOG_TEST REJECT LIMIT UNLIMITED;  

999 filas creadas.

WAW! De 1 millón de registros, sólo 999 registros fueron insertados satisfactoriamente, el resto de los registros se insertaron en la tabla de logueo de errores.

Como notarán, use la cláusula LOG ERRORS INTO ... REJECT LIMIT UNLIMITED. Esta cláusula nos permite decirle a Oracle, que queremos utilizar "DML Error Logging" para nuestra sentencia. Fijense que coloqué UNLIMITED como parámetro de REJECT LIMIT. Esto le dice a Oracle, que no se fije en la cantidad de registros que terminan en error, que simplemente me inserte todos esos registros en la tabla de logueo de errores y que continúe con la ejecución de la sentencia hasta que termine. En vez de UNLIMITED, podría haber puesto 100, 1000, etc... que denota el máximo número de registros que quiero que Oracle loguee. Si hay más registros que terminan en error, Oracle simplemente aborta la ejecución de la consulta y devuelve un error por pantalla.

SQL_10gR2> SELECT count(*) FROM error_log_test;

COUNT(*)
----------
999001

1 fila seleccionada.

Como verán, no perdimos ningún registros. Los registros que se insertaron satisfactoriamente están en la tabla final TEST y el resto en la tabla de logueo de errores. Tener los registros en la tabla de logueo, nos permite corregirlos y tratar de insertarlos nuevamente en la tabla TEST.

Veamos porqué falló la inserción de la mayoría de los registros...

SQL_10gR2> SELECT count(*), ora_err_mesg$ FROM error_log_test GROUP BY ora_err_mesg$;

COUNT(*) ORA_ERR_MESG$
---------- ----------------------------------------------------------------------------------------------------
90000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 9, máximo: 7)
 9000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 8, máximo: 7)
    1 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 11, máximo: 7)
900000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 10, máximo: 7)

4 filas seleccionadas.

Como podemos ver, los registros fallaron porque el campo NOMBRE tiene un tamaño de 7 caracteres, y nosotros estamos intentando insertar valores más grandes. Solucionar éste problema es muy sencillo, simplemente, tenemos que agrandar el campo NOMBRE.

SQL_10gR2> ALTER TABLE test MODIFY nombre VARCHAR2(100);

Tabla modificada.

Ahora vamos a tratar de insertar nuevamente los registros en la tabla TEST desde la tabla de logueo de errores.

SQL_10gR2> INSERT INTO test
2  SELECT id, nombre
3  FROM error_log_test;

999001 filas creadas.

SQL_10gR2> SELECT count(*) FROM test;

COUNT(*)
----------
1000000

1 fila seleccionada.

Excelenete!!! Ya tenemos todos los registros en la tabla TEST! Ahora sólo resta truncar la tabla de logueo de errores o simplemente borrarla en caso de que no la vayamos a utilizar nunca más.

SQL_10gR2> TRUNCATE TABLE error_log_test;

Tabla truncada.

CONCLUSIÓN:

Esta nueva funcionalidad está acotada para ser utilizada sólo para algunos casos en particulares, pero espero que en lo posible, todos puedan comenzar a hacer uso de ésta funcionalidad ya que es muy simple de utilizar y muy eficiente a la hora de realizar carga masiva de datos y de logueo de errores de manera simultánea en una sola sentencia DML sin necesidad de recurrir a procedimientos costosos en cuanto a performance, desarrollo, mantenimiento y debuging.

Oracle adquiere Sun Microsystems

En momentos de crisis... pueden pasar cosas increíbles...



unbreakable Linux... y ahora? unbreakable Solaris??? =)

Para más información... AQUI

Cuántos registros hay en cada bloque de mi tabla?

En el día de hoy, me llegó una e-mail de una persona preguntándome lo siguiente: "Me podrías decir cómo hago para saber cuántos registros hay en cada bloque de mi tabla?". Bueno, la verdad es que es muy fácil ver cuántos registros caben en cada bloque y también es muy fácil comprobarlo.

Veamos un ejemplo:

En la base de datos de prueba en la que estoy actualmente, tengo bloques de 8 KB.

SQL_10gR2> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Vamos a crear una tabla llamada TEST con 1.000 registros.

SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'nom_'||level nombre
3  FROM dual
4  CONNECT BY level <= 1000;      

Tabla creada. 

Para ver la cantidad de bloques que necesité para almacenar los 1.000 registros y la cantidad de registros que hay en cada uno de esos bloques, podemos ejecutar la siguiente consulta.

SQL_10gR2> SELECT dbms_rowid.rowid_block_number(rowid) "Número de Bloque", count(*)
2  FROM test
3  GROUP BY dbms_rowid.rowid_block_number(rowid)
4  ORDER BY dbms_rowid.rowid_block_number(rowid) ASC;

Número de Bloque   COUNT(*)
---------------- ----------
           46196        438
           46197        425
           46198        137

3 filas seleccionadas.

Por lo que podemos observar, en el bloque 46196 tengo 438 registros, en el bloque 46197 tengo 425 registros y en el bloque 46198 tengo 137 registros. Pero cómo hacemos para comprobar que realmente es cierto? Cómo hacemos para verificar que el resultado de la consulta es verdadero? Bueno, lo que vamos a hacer, es realizar un Dump de los 3 bloques y ver la información del Trace que se genera automáticamente. Para ejecutar un Dump, primero necesitamos obtener el número del DataFile donde se encuentra almacenada nuestra tabla (segmento). Para ésto, primero vamos a obtener ésta información y luego a realizar el Dump de los bloques. Veamos...

SQL_10gR2> SELECT header_file FROM dba_segments WHERE segment_name = 'TEST';

HEADER_FILE
-----------
          4

1 fila seleccionada.


SQL_10gR2>  alter system dump datafile 4 block min 46196 block max 46198;

Sistema modificado.

SQL_10gR2> select spid
2  from v$session s, v$process p
3  where p.addr = s.paddr
4  and s.audsid = sys_context('userenv','sessionid')
5  /

SPID
------------
4360

1 fila seleccionada.

Ya se generó el Trace en el directorio especificado en el parámetro user_dump_dest. El nombre con el que se generó es test_ora_4360.trc (el número es el SPID... "System Process Identifier" que obtuvimos). Veamos las partes que más nos interesan del archivo de Trace...

data_block_dump,data header at 0x4f06a7c
===============
tsiz: 0x1f80
hsiz: 0x37e
pbl: 0x04f06a7c
bdba: 0x0100b474
76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46196.
nrow=438  <-- número de registros contenidos dentro del bloque 46196. 
frre=-1
fsbo=0x37e  <-- comienzo del espacio libre del bloque 46196.
fseo=0x6a9  <-- fin del espacio libre del bloque 46196.
avsp=0x32b  <-- espacio disponible del bloque 46196.
tosp=0x32b  <-- espacio total del bloque 46196. 
0xe:pti[0]    nrow=438    offs=0  <-- hay 438 registros en el bloque 46196 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x364
pbl: 0x0a206a7c
bdba: 0x0100b475
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46197.
nrow=425  <-- número de registros contenidos dentro del bloque 46197.
frre=-1
fsbo=0x364
fseo=0x69d
avsp=0x339
tosp=0x339
0xe:pti[0] nrow=425 offs=0  <-- hay 425 registros en el bloque 46197 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x124
pbl: 0x0a206a7c
bdba: 0x0100b476
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46198. 
nrow=137  <-- número de registros contenidos dentro del bloque 46198. 
frre=-1
fsbo=0x124
fseo=0x177a
avsp=0x1656
tosp=0x1656
0xe:pti[0] nrow=137 offs=0  <-- hay 137 registros en el bloque 46198 comenzando desde el registro número 0.

Bien, con éste ejemplo pudimos comprobar y verificar el resultado de nuestra primer consulta.

Encriptando columnas con TDE (Transparent Data Encryption) en tablas con millones de registros

Para los que no conocen TDE, es una nueva funcionalidad de 10g R2 que permite proteger datos sensibles de las columnas de nuestras tablas encriptando los datos al almacenarlos en los respectivos Data Files en el sistema operativo. Para protegernos de personas malintencionadas que quieran desencriptar los datos sin autorización, guarda las claves de encriptamiento en un módulo seguro externo a la base de datos.

Mi intención en éste post no es mostrar el paso a paso de cómo implementar TDE, sino mostrarles cómo encriptar columnas con TDE en tablas con millones de datos de la manera más eficiente y con el menor impacto posible en cuanto a la performance.

En el momento en el que encriptamos columnas en una tabla, sólo podemos acceder a la tabla en modo lectura... NO están permitidas las operaciones DML hasta que el encriptamiento termine. En tablas chicas, con muy pocos datos, ésto no suele perjudicarnos demasiado. Pero qué sucede en tablas con millones de registros? En este caso, el encriptamiento puede durar varias horas!!!

La estrategia que vamos a utilizar para encriptar columnas en tablas con millones de registros, es utilizando el paquete DBMS_REDEFINITION.

Veamos un ejemplo:

Tengo una tabla llamada TEST_TDE con 1 millón de registros. Esta tabla tiene 3 columnas: ID (primary key), NOMBRE, NUM_TARJETA. La columna que vamos a encriptar es NUM_TARJETA ya que tiene los números de tarjetas, y como para mi es información sensible, quiero encriptarla y protegerla con TDE.

SQL_10gR2> SELECT count(*) FROM test_tde;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE

Nombre                                     Nulo?   Tipo
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
NOMBRE                                             VARCHAR2(44)
NUM_TARJETA                                        NUMBER

SQL_10gR2> SELECT index_name, column_name FROM dba_ind_columns WHERE table_name = 'TEST_TDE';

INDEX_NAME                               COLUMN_NAME
---------------------------------------- ----------------------------------------
TEST_TDE_ID_PK                           ID

1 fila seleccionada.

SQL_10gR2> SELECT constraint_name, constraint_type FROM dba_constraints WHERE table_name = 'TEST_TDE';

CONSTRAINT_NAME                C
------------------------------ -
TEST_TDE_ID_PK                 P

1 fila seleccionada.

Bien, ahora vamos a encriptar la columna NUM_TARJETA de la manera tradicional y sin utilizar ninguna técnica. En la SESIÓN 1, voy a encriptar la columna, en la SESIÓN 2, voy a modificar el valor de uno de los registros de la tabla mientras se está realizando el procedimiento de encriptado... veamos qué sucede!

SESIÓN 1:

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta ENCRYPT);

Tabla modificada.

Transcurrido: 00:01:20.11

SESIÓN 2:

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:01:17.64

Como podemos observar, el encriptamiento de 1 millón de registros demoró 1 minuto 20 segundos. Mientras que se encriptaban los datos, ejecuté un UPDATE en otra sesión y demoró 1 minuto 17 segundos. Esto es debido a que la tabla quedó loqueda en la SESIÓN 1 por el proceso de encriptamiento y la SESIÓN 2 tuvo que esperar que termine de encriptar los datos para poder modificarlos. Imaginen los problemas de performance que tendrían en sus aplicaciones si tienen varios usuarios concurrentes realizando operaciones DML sobre esa tabla mientras se está ejecutando el procedimiento de encriptación!!!

Si ejecutamos el mismo UPDATE luego de que los datos ya se encuentran encriptados, podemos observar que no demoró nada en ejecutarse la operación DML.

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:00:00.00

Ahora vamos a ver la técnica que les mencionaba con el paquete DBMS_REDEFINITION. Lo primero que vamos a hacer, es ejecutar el procedimiento CAN_REDEF_TABLE de éste paquete para corroborar que la tabla puede redefinirse online.

SQL_10gR2> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST', 'TEST_TDE', dbms_redefinition.cons_use_pk);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.31

Como el procedimiento terminó sin errores, quiere decir que la tabla puede ser redefinida online.
Ahora vamos a comenzar el proceso de redefinir la tabla. Para ésto, primero tenemos que crear una tabla intermedia (TEST_TDE_TEMP) con la estructura de la tabla TEST_TDE en donde Oracle va a colocar los datos de la tabla original de manera temporal.

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta DECRYPT);

Tabla modificada.

Transcurrido: 00:01:04.53

SQL_10gR2> SET long 400000000

SQL_10gR2> SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TDE', 'TEST') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TDE','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST_TDE"
(    "ID" NUMBER,
"NOMBRE" VARCHAR2(44),
"NUM_TARJETA" NUMBER,
CONSTRAINT "TEST_TDE_ID_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

1 fila seleccionada.

SQL_10gR2> CREATE TABLE TEST_TDE_TEMP
2  (
3  ID          NUMBER,
4  NOMBRE      VARCHAR2(44),
5  NUM_TARJETA NUMBER ENCRYPT,
6  CONSTRAINT TEST_TDE_TEMP_ID_PK PRIMARY KEY (ID)
7  );

Tabla creada.

Como pueden notar, lo primero que hice fue desencriptar la columna NUM_TARJETA para poder volver a encriptarla con ésta técnica. Luego utilicé el paquete DBMS_METADATA para ver la estructura de la tabla TEST_TDE y poder crear una similar con el nombre TEST_TDE_TEMP.

Ahora vamos a redefinir la tabla con el procedimiento START_REDEF_TABLE.

SQL_10gR2> exec dbms_redefinition.start_redef_table('TEST', 'TEST_TDE', 'TEST_TDE_TEMP', 'id, nombre, num_tarjeta', DBMS_REDEFINITION.CONS_USE_PK);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:41.18

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE_TEMP

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> SELECT master, log_table FROM dba_mview_logs WHERE log_table LIKE '%TEST%';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
TEST_TDE                       MLOG$_TEST_TDE

1 fila seleccionada.

Fijense, que al redefinir la tabla, Oracle copió los datos de la tabla TEST_TDE y los insertó en la tabla transitoria TEST_TDE_TEMP con la columna NUM_TARJETA encriptada. A su vez, Oracle creó una vista materializada para registrar cualquier cambio que se realice sobre la tabla TEST_TDE para luego, en el momento de hacer la sincronización de datos entre las 2 tablas, pueda impactar cualquier cambio realizado.

Antes de realizar el proceso de sincronización entre las 2 tablas, voy a agregar algunos registros a la tabla TEST_TDE para ver si luego de la sincronización, los últimos cambios que realicé fueron impactados. Recuerden, que todos éstos pasos los estamos haciendo online, y que por el momento, los usuarios siguen trabajando sobre la tabla TEST_TDE sin ningún tipo de impacto en cuanto a la performance.

SQL_10gR2> INSERT INTO test_tde
2  SELECT * FROM
3  (
4  SELECT level id, 'nom_'||level, round(dbms_random.value(100000000000,900000000000))
5  FROM dual
6  CONNECT BY level <= 1000100  
7  )  
8  WHERE id > 1000000;

100 filas creadas.

Transcurrido: 00:00:06.93

SQL_10gR2> COMMIT;

Confirmación terminada.

Ahora vamos a sincronizar con el procedimiento SYNC_INTERIM_TABLE, las 2 tablas para aplicar cualquier cambio realizado en la tabla TEST_TDE a la tabla TEST_TDE_TEMP.
En éste paso en adelante, necesitamos una ventana de tiempo muy chica para ejecutar los 2 procedimientos que restan. En esta ventana de tiempo, los usuarios no deben estar modificando la tabla TEST_TDE.

SQL_10gR2> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.29

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000100

1 fila seleccionada.

Transcurrido: 00:00:06.92

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER

Los 100 registros que habiamos agregado a la tabla TEST_TDE fueron impactados con éxito en la tabla TEST_TDE_TEMP! Para finalizar, lo único que resta es ejecutar el procedimiento FINISH_REDEF_TABLE para aplicar en la tabla TEST_TDE todos los cambios de la tabla TEST_TDE_TEMP (incluyendo la columna encriptada).

SQL_10gR2> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.37

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> DROP TABLE test_tde_temp;

Tabla borrada.

Con el mas mínimo impacto en la performance, pudimos encriptar la columna NUM_TARJETA de la tabla TEST_TDE (que contiene 1 millón de registros) y no afectar las operaciones de los usuarios online sobre esa tabla! Sólo tuvimos que necesitar una ventana de tiempo de 1 minuto para colocar la tabla TEST_TDE nuevamente disponible para todos los usuarios.