A pedido de mucha gente que visita el blog y que utiliza Oracle...
Este post es para que ustedes puedan sugerir los temas que tienen dudas y que les gustaría que se les explicara con más detalle en éste blog. Los temas pueden estar relacionados con Performance Tuning, Administración, Desarrollo, etc.
Los invito a todos a sugerir temas técnicos relacionados con Oracle que requieran ser explicados en detalle.
Saludos a todos!
jueves, 22 de octubre de 2009
martes, 20 de octubre de 2009
Añadiendo columnas con valores por defecto y constraint NOT NULL en Oracle 11g
Antes de la versión 11g, cuando agregabamos una columna a una tabla con valores por defecto y una constraint del tipo NOT NULL, Oracle realizaba un loqueo exclusivo en la tabla para insertar los valores en cada uno de los registros existentes en la misma.
A partir de la versión 11g release 1, ésta operación se optimizó para mejorar en gran medida la utilización de los recursos del sistema y el espacio de almacenamiento de los nuevos valores. Oracle logra ésta optimización, guardando el valor por defecto en el diccionario de datos en vez de modificar todos los registros de la tabla, haciendo la ejecución de ésta operacion de manera instantánea. Gracias a ésto, obtenemos un beneficio enorme a la hora de modificar tablas con millones de registros.
Por otro lado, las siguientes operaciones ADD COLUMN ahora pueden ejecutarse de manera concurrente junto con las operaciones DML:
- Agregar una columna NOT NULL con un valor por defecto.
- Agregar una columna NULL sin un valor por defecto.
- Agregar una columna virtual.
Veamos un ejemplo:
- versión 9i release 2
- versión 11g release 1
Como podemos observar, creamos una tabla con 1 millón de registros y luego agregamos una columna con valores por defecto. En 9iR2, esa operación se ejecutó en 1 minuto 17 segundos; en cambio en 11gR1, se ejecutó de manera instantánea en tan solo 17 milisegundos!!!
IMPORTANTE: Tengan en cuenta que existen bugs en 11g (leer nota Metalink 602327.1) relacionados con éste tipo de operación.
A partir de la versión 11g release 1, ésta operación se optimizó para mejorar en gran medida la utilización de los recursos del sistema y el espacio de almacenamiento de los nuevos valores. Oracle logra ésta optimización, guardando el valor por defecto en el diccionario de datos en vez de modificar todos los registros de la tabla, haciendo la ejecución de ésta operacion de manera instantánea. Gracias a ésto, obtenemos un beneficio enorme a la hora de modificar tablas con millones de registros.
Por otro lado, las siguientes operaciones ADD COLUMN ahora pueden ejecutarse de manera concurrente junto con las operaciones DML:
- Agregar una columna NOT NULL con un valor por defecto.
- Agregar una columna NULL sin un valor por defecto.
- Agregar una columna virtual.
Veamos un ejemplo:
- versión 9i release 2
SQL_9iR2> CREATE TABLE test
2 NOLOGGING
3 AS
4 SELECT level id
5 FROM dual
6 CONNECT BY level <= 1000000;
Table created.
SQL_9iR2> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);
Table altered.
Elapsed: 00:01:17.62
SQL_9iR2> DESC test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NOMBRE NOT NULL VARCHAR2(100)
SQL_9iR2> exec dbms_stats.gather_table_stats(USER, 'TEST');
PL/SQL procedure successfully completed.
SQL_9iR2> SELECT count(*)
2 FROM test
3 WHERE nombre IS NULL;
COUNT(*)
----------
0
- versión 11g release 1
SQL_11gR1> CREATE TABLE test
2 NOLOGGING
3 AS
4 SELECT level id
5 FROM dual
6 CONNECT BY level <= 1000000;
Table created.
SQL_11gR1> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);
Table altered.
Elapsed: 00:00:00.17
SQL_11gR1> DESC test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NOMBRE NOT NULL VARCHAR2(100)
SQL_11gR1> exec dbms_stats.gather_table_stats(USER, 'TEST');
PL/SQL procedure successfully completed.
SQL_11gR1> SELECT count(*)
2 FROM test
3 WHERE nombre IS NULL;
COUNT(*)
----------
0
Como podemos observar, creamos una tabla con 1 millón de registros y luego agregamos una columna con valores por defecto. En 9iR2, esa operación se ejecutó en 1 minuto 17 segundos; en cambio en 11gR1, se ejecutó de manera instantánea en tan solo 17 milisegundos!!!
IMPORTANTE: Tengan en cuenta que existen bugs en 11g (leer nota Metalink 602327.1) relacionados con éste tipo de operación.
jueves, 15 de octubre de 2009
TKPROF y el parámetro SYS=Y
Conozco muchas personas que al ejecutar la herramienta TKPROF, lo hacen con el parámetros SYS=N para que no se incluyan en el archivo de salida las consultas recursivas que realiza internamente la base de datos. Si bien en algunos casos se hace ésto para que el archivo de salida no contenga información en exceso, muchas veces cuando buscamos la causa de un determinado problema, si colocamos SYS=N lo único que estaremos logrando será "ocultar" el causante de ese problema.
Hace unos días, investigando un problema de performance en un proceso de un cliente, ejecuté la herramienta TKPROF con el parámetro SYS=Y (que es la opción por defecto) y noté que había una consulta recursiva que estaba leyendo millones de bloques de datos!
La consulta es la siguiente:
Esta consulta es interna de la base de datos. Debido a que el proceso se ejecuta con el usuario APPS y debido a que a éste usuario durante su creación se lo asignó dentro de un grupo de tablespaces temporales (new feature en 10g) llamado TEMP (que contienen los tablespaces TEMP1 y TEMP2), Oracle necesita ejecutar ésta consulta para determinar cuál de los dos tablespaces es el mejor para realizar la operación de sort que está requiriendo el proceso. Como pueden ver, esta consulta es ALTAMENTE costosa y consume muchos recursos del sistema ya que la consulta lee 3,245,150 bloques (410 * 7915) y el proceso en total lee 4,452,813 bloques! Por lo cual, ésta consulta está leyendo más del 70% del total de todos los bloques de datos del proceso!!!
Para solucionar éste problema, lo que hice fue modificar el usuario APPS, sacarlo del grupo de tablespaces temporales TEMP y asignarle directamente el tablespace TEMP2 que tiene un tamaño de 39 GB a comparación del TEMP1 que tiene sólo 6 GB. Al hacer éste cambio, lo que logramos fue que Oracle deje de ejecutar esa consulta debido a que ya no tiene necesidad de determinar cual de los dos tablespaces es el mejor para ejecutar una determinada operación de sort.
Al ejecutar nuevamente el proceso, podemos ver el resultado de la solución implementada:
Luego que se realizó éste cambio, se notó una mejora muy importante en cuanto a la performance y se notó una disminución drástica de la utilización de los recursos del sistema.
Tengan siempre presente la importancia del parámetro SYS=Y al momento de ejecutar la herramienta TKPROF.
Por cierto, esta consulta es un Bug! Hay que aplicar el Parche 5455880 para solucionar éste grave problema de performance.
Hace unos días, investigando un problema de performance en un proceso de un cliente, ejecuté la herramienta TKPROF con el parámetro SYS=Y (que es la opción por defecto) y noté que había una consulta recursiva que estaba leyendo millones de bloques de datos!
La consulta es la siguiente:
select min(bitmapped)
from
ts$ where dflmaxext =:1 and bitand(flags,1024) =1024
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7915 0.08 0.07 0 0 0 0
Execute 7915 0.17 0.14 0 0 0 0
Fetch 15830 4.24 4.23 0 3245150 0 7915
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31660 4.50 4.45 0 3245150 0 7915
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=410 pr=0 pw=0 time=691 us)
2 TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=659 us)
Esta consulta es interna de la base de datos. Debido a que el proceso se ejecuta con el usuario APPS y debido a que a éste usuario durante su creación se lo asignó dentro de un grupo de tablespaces temporales (new feature en 10g) llamado TEMP (que contienen los tablespaces TEMP1 y TEMP2), Oracle necesita ejecutar ésta consulta para determinar cuál de los dos tablespaces es el mejor para realizar la operación de sort que está requiriendo el proceso. Como pueden ver, esta consulta es ALTAMENTE costosa y consume muchos recursos del sistema ya que la consulta lee 3,245,150 bloques (410 * 7915) y el proceso en total lee 4,452,813 bloques! Por lo cual, ésta consulta está leyendo más del 70% del total de todos los bloques de datos del proceso!!!
Para solucionar éste problema, lo que hice fue modificar el usuario APPS, sacarlo del grupo de tablespaces temporales TEMP y asignarle directamente el tablespace TEMP2 que tiene un tamaño de 39 GB a comparación del TEMP1 que tiene sólo 6 GB. Al hacer éste cambio, lo que logramos fue que Oracle deje de ejecutar esa consulta debido a que ya no tiene necesidad de determinar cual de los dos tablespaces es el mejor para ejecutar una determinada operación de sort.
Al ejecutar nuevamente el proceso, podemos ver el resultado de la solución implementada:
select min(bitmapped)
from
ts$ where dflmaxext =:1 and bitand(flags,1024) =1024
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 410 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 410 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=410 pr=0 pw=0 time=3045 us)
2 TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=2952 us)
Luego que se realizó éste cambio, se notó una mejora muy importante en cuanto a la performance y se notó una disminución drástica de la utilización de los recursos del sistema.
Tengan siempre presente la importancia del parámetro SYS=Y al momento de ejecutar la herramienta TKPROF.
Por cierto, esta consulta es un Bug! Hay que aplicar el Parche 5455880 para solucionar éste grave problema de performance.
miércoles, 2 de septiembre de 2009
lunes, 6 de julio de 2009
adiós a los RAW-devices en 12g...
Según la nota de Metalink 578455.1 en 12g NO se seguirán soportando RAW-devices para los archivos físicos de la base de datos (data files, redo logs, control files), OCR (Oracle Cluster Registry) o discos Voting.
IMPORTANTE!!! Este anuncio NO afecta a ASM.
En 12g, ya NO será posible ejecutar la siguiente sentencia porque intenta utilizar RAW-devices directamente...
Lo que tendremos que hacer, es ejecutar alguna de las siguientes sentencias para crear un diskgroup...
o
Luego de crear el diskgroup, podemos ejecutar la sentencia que acostumbramos...
Para los que tengan que migrar desde RAW-devices, las opciones existentes incluyen ASM (Automatic Storage Management), OCFS (Oracle Cluster File System) y otros sistemas de archivos clúster.
Muchos estarán leyendo ésta nota y diciendo... OUCHHHH!!!!!! Y bueno... tendremos que irnos aconstumbrando a ésta clase de cambios ya que éste es sólo uno de los grandes cambios que veremos en 12g...
IMPORTANTE!!! Este anuncio NO afecta a ASM.
En 12g, ya NO será posible ejecutar la siguiente sentencia porque intenta utilizar RAW-devices directamente...
SQL> create tablespace ABC DATAFILE '/dev/raw/ABC1.dbf' size 2GB;
Lo que tendremos que hacer, es ejecutar alguna de las siguientes sentencias para crear un diskgroup...
SQL> alter diskgroup MYDG add disk '/dev/raw/ABC1.dbf';
o
SQL> create diskgroup MYDB EXTERNAL REDUNDANCY disk 'dev/raw/ABC1.dbf'
Luego de crear el diskgroup, podemos ejecutar la sentencia que acostumbramos...
SQL> create tablespace ABC DATAFILE '+MYDG' size 2GB;
Para los que tengan que migrar desde RAW-devices, las opciones existentes incluyen ASM (Automatic Storage Management), OCFS (Oracle Cluster File System) y otros sistemas de archivos clúster.
Muchos estarán leyendo ésta nota y diciendo... OUCHHHH!!!!!! Y bueno... tendremos que irnos aconstumbrando a ésta clase de cambios ya que éste es sólo uno de los grandes cambios que veremos en 12g...
jueves, 25 de junio de 2009
Stress Test
El "Stress Test" es una prueba que mide el comportamiento de nuestro sistema bajo una cierta demanda concurrente de conexiones. Esta es una de las pruebas claves que se DEBEN realizar durante el ciclo de vida del software para garantizar que nuestro sistema va a cumplir con las expectativas previstas cuando sea implementado en producción.
Quiero mostrarles una simple prueba de Stress que hice para que vean qué tan importante es realizarla y qué tan mortal puede ser evitarla.
Ejemplo:
Vamos a crear una tabla llamada TEST que vamos a utilizar en el Stress para cargar datos.
Bien, yo tengo creados unos Shell Scripts propios para realizar el Stress Test. Los más importantes son los siguientes:
- iniciar_stress.sh --> Iniciamos el Stress Test.
- reporte.sh --> Genera el reporte con el detalle sobre el Stress Test.
Lo que vamos a realizar es un Stress Test simulando una concurrencia de 100 conexiones simultáneas ejecutando el siguiente bloque PL/SQL anónimo:
Como pueden observar, el INSERT que vamos a realizar es muy sencillo y debería ejecutarse rápido, cierto? Veamos cuánto demora si lo ejecuto sin concurrencia:
Bien, vemos que demoró 1.25 segundos. El bloque se ejecutó bastante rápido y esperamos que se comporte de la misma manera cuando realicemos el Stress Test... asi que hagamos la prueba para comprobarlo!
Para comenzar el Stress Test, vamos a ejecutar el Shell Script llamado iniciar_stress.sh
Como pueden ver, se iniciaron 100 conexiones a la base de datos. Las mismas se ejecutan con el comando nohup realizando la conexión a la instancia vía SQL*PLUS.
Ahora vamos a ejecutar el Shell Script llamado reporte.sh para generar el reporte con la información resultante de la prueba.
Abrimos el reporte y vemos lo siguiente...
Como podemos observar, el comportamiento para cada sesión fue el mismo que cuando ejecutamos la prueba sin concurrencia. El bloque se ejecutó en un promedio de 1.5 segundos por sesión.
Qué sucede si repetimos la prueba con 300 usuarios concurrentes? El resultado seguirá siendo el mismo? Veamos el reporte de la ejecución...
Como observamos, cuando incrementamos la cantidad de conexiones concurrentes, aumentó casi un 400 porciento el tiempo de ejecución de cada conexión!!! Este es un ejemplo muy claro del porqué es TAN importante hacer ésta clase de pruebas de Stress para evitar problemas futuros cuando pasemos nuestro sistema a producción!
Quiero mostrarles una simple prueba de Stress que hice para que vean qué tan importante es realizarla y qué tan mortal puede ser evitarla.
Ejemplo:
Vamos a crear una tabla llamada TEST que vamos a utilizar en el Stress para cargar datos.
SQL_9iR2> CREATE TABLE test (id NUMBER);
Tabla creada.
Bien, yo tengo creados unos Shell Scripts propios para realizar el Stress Test. Los más importantes son los siguientes:
- iniciar_stress.sh --> Iniciamos el Stress Test.
- reporte.sh --> Genera el reporte con el detalle sobre el Stress Test.
Lo que vamos a realizar es un Stress Test simulando una concurrencia de 100 conexiones simultáneas ejecutando el siguiente bloque PL/SQL anónimo:
BEGIN
FOR x IN 1 .. 100 LOOP
INSERT INTO test VALUES (x);
END LOOP;
END;
/
Como pueden observar, el INSERT que vamos a realizar es muy sencillo y debería ejecutarse rápido, cierto? Veamos cuánto demora si lo ejecuto sin concurrencia:
SQL_9iR2> BEGIN
2 FOR x IN 1 .. 100 LOOP
3 INSERT INTO test VALUES (x);
4 END LOOP;
5 END;
6 /
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:01.25
Bien, vemos que demoró 1.25 segundos. El bloque se ejecutó bastante rápido y esperamos que se comporte de la misma manera cuando realicemos el Stress Test... asi que hagamos la prueba para comprobarlo!
Para comenzar el Stress Test, vamos a ejecutar el Shell Script llamado iniciar_stress.sh
$ ./iniciar_stress.sh
=================================================================
====================== STRESS TEST ==============================
=================================================================
Autor: Leonardo Horikian - Oracle Argentina
Descripción: Programa que inicia un StressTest
Apretar ENTER para continuar...
=================================================================
Ingresar la password del usuario STRESS_TEST:
TNS alias (para local dejar en blanco):
Esta instancia se encuentra configurada para soportar 769 conexiones concurrentes!
Cuántas conexiones concurrentes querés ejecutar [1-769]?
100
=================================================================
StressTest listo para ser ejecutado.
Para comenzar, apretar ENTER
=================================================================
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
Como pueden ver, se iniciaron 100 conexiones a la base de datos. Las mismas se ejecutan con el comando nohup realizando la conexión a la instancia vía SQL*PLUS.
Ahora vamos a ejecutar el Shell Script llamado reporte.sh para generar el reporte con la información resultante de la prueba.
$ ./reporte.sh
=================================================================
====================== STRESS TEST ==============================
=================================================================
Autor: Leonardo Horikian - Oracle Argentina
Descripción: Programa que genera el reporte con la información
sobre la ejecución del StressTest
Apretar ENTER para continuar...
=================================================================
Ingresar la password del usuario STRESS_TEST:
TNS alias (para local dejar en blanco):
NOTA: Este script generará un archivo con la información del reporte en el directorio /reportes/.
Ingresar el nombre del archivo de salida:
stress_test.txt
Elegir el TEST_ID con el cual se quiere generar el reporte...
TEST_ID TEST_DATE COMMENTS
********** ****************************** ****************************************
37 06/25/2009 08:46 100 conexiones concurrentes.
Ingresá el TEST_ID: 37
El archivo fue generado satisfactoriamente!
Abrimos el reporte y vemos lo siguiente...
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------- INFORMACION SOBRE EL STRESS_TEST -------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
ID: 37 - FECHA: 06/25/2009 08:46 - COMENTARIOS: 100 conexiones concurrentes.
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------- RESULTADO INDIVIDUAL DEL STRESS_TEST (para cada una de las sesiones) -------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 248 - SPID: 11065
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.51 - TOTAL_TIME_IN_MIN: .03 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits --> WAITS: 47 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 1.3
EVENT: library cache pin --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .52
EVENT: enqueue --> WAITS: 8 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .31
EVENT: latch free --> WAITS: 6 - TIMEOUTS: 2 - TIME_WAITED_IN_SEC: .03
EVENT: row cache lock --> WAITS: 11 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .03
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: db file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 808 - SPID: 11143
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.5 - TOTAL_TIME_IN_MIN: .03 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits --> WAITS: 47 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 1.3
EVENT: enqueue --> WAITS: 7 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .3
EVENT: library cache pin --> WAITS: 3 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .16
EVENT: latch free --> WAITS: 1 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: .01
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 714 - SPID: 11116
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.48 - TOTAL_TIME_IN_MIN: .02 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits --> WAITS: 35 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 1.3
EVENT: library cache pin --> WAITS: 3 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .31
EVENT: enqueue --> WAITS: 2 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .24
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .01
EVENT: latch free --> WAITS: 2 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 204 - SPID: 11110
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.48 - TOTAL_TIME_IN_MIN: .02 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits --> WAITS: 38 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 1.2
EVENT: library cache pin --> WAITS: 3 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .36
EVENT: enqueue --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .3
EVENT: db file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .09
EVENT: row cache lock --> WAITS: 7 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .03
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .02
EVENT: latch free --> WAITS: 2 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
...
...
CONTINUA ...
...
...
Como podemos observar, el comportamiento para cada sesión fue el mismo que cuando ejecutamos la prueba sin concurrencia. El bloque se ejecutó en un promedio de 1.5 segundos por sesión.
Qué sucede si repetimos la prueba con 300 usuarios concurrentes? El resultado seguirá siendo el mismo? Veamos el reporte de la ejecución...
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------- INFORMACION SOBRE EL STRESS_TEST -------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
ID: 38 - FECHA: 06/25/2009 09:07 - COMENTARIOS: 300 conexiones concurrentes.
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------- RESULTADO INDIVIDUAL DEL STRESS_TEST (para cada una de las sesiones) -------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 607 - SPID: 15310
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 5.47 - TOTAL_TIME_IN_MIN: .09 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue --> WAITS: 12 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 4.8
EVENT: row cache lock --> WAITS: 12 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 2.9
EVENT: latch free --> WAITS: 15 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 1.2
EVENT: buffer busy waits --> WAITS: 11 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .77
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .06
EVENT: db file sequential read --> WAITS: 3 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .04
EVENT: log file sync --> WAITS: 1 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .03
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 286 - SPID: 15362
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 5.19 - TOTAL_TIME_IN_MIN: .09 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue --> WAITS: 13 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 4.9
EVENT: row cache lock --> WAITS: 11 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 2.9
EVENT: latch free --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 1.1
EVENT: buffer busy waits --> WAITS: 5 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .45
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .12
EVENT: db file sequential read --> WAITS: 3 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .02
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 435 - SPID: 15329
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 4.97 - TOTAL_TIME_IN_MIN: .08 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue --> WAITS: 9 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 3.5
EVENT: row cache lock --> WAITS: 8 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 2.7
EVENT: latch free --> WAITS: 37 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 2.5
EVENT: buffer busy waits --> WAITS: 7 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .58
EVENT: db file sequential read --> WAITS: 17 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .18
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .07
EVENT: library cache pin --> WAITS: 1 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 202 - SPID: 15746
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 4.63 - TOTAL_TIME_IN_MIN: .08 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue --> WAITS: 76 - TIMEOUTS: 1 - TIME_WAITED_IN_SEC: 5.5
EVENT: row cache lock --> WAITS: 181 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 2.1
EVENT: latch free --> WAITS: 47 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 2.0
EVENT: buffer busy waits --> WAITS: 5 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .16
EVENT: SQL*Net message from client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .07
EVENT: wait list latch free --> WAITS: 1 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read --> WAITS: 4 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: SQL*Net message to client --> WAITS: 19 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
EVENT: db file sequential read --> WAITS: 2 - TIMEOUTS: 0 - TIME_WAITED_IN_SEC: 0
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
...
...
CONTINUA ...
...
...
Como observamos, cuando incrementamos la cantidad de conexiones concurrentes, aumentó casi un 400 porciento el tiempo de ejecución de cada conexión!!! Este es un ejemplo muy claro del porqué es TAN importante hacer ésta clase de pruebas de Stress para evitar problemas futuros cuando pasemos nuestro sistema a producción!
martes, 19 de mayo de 2009
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:
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:
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:
Recuerden siempre el acrónimo GIGO: Garbage In ... Garbage Out :)
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 :)
miércoles, 13 de mayo de 2009
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.
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.
Bien, ahora vamos a insertar los registros en la tabla TEST.
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.
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...
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.
Ahora vamos a tratar de insertar nuevamente los registros en la tabla TEST desde la tabla de logueo de errores.
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.
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.
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.
CONCLUSIÓN:
SQL_10gR2> TRUNCATE TABLE error_log_test;
Tabla truncada.
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.
viernes, 8 de mayo de 2009
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
unbreakable Linux... y ahora? unbreakable Solaris??? =)
Para más información... AQUI
domingo, 3 de mayo de 2009
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.
Vamos a crear una tabla llamada TEST con 1.000 registros.
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.
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...
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...
Bien, con éste ejemplo pudimos comprobar y verificar el resultado de nuestra primer consulta.
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.
sábado, 2 de mayo de 2009
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.
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:
SESIÓN 2:
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.
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.
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.
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.
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.
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.
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).
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.
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.
jueves, 30 de abril de 2009
En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!
"EXPLAIN PLAN FOR" tiene problemas...
Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.
Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).
Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.
- 1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.
SQL_10gR2> DESC emp
Nombre Nulo? Tipo
----------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
GENDER VARCHAR2(1)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL_10gR2> VAR bind NUMBER;
SQL_10gR2> EXECUTE :bind := 7900
Procedimiento PL/SQL terminado correctamente.
SQL_10gR2> explain plan for
2 SELECT * FROM emp WHERE empno = :bind;
Explicado.
SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMPNO_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BIND))
Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.
- 2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.
SQL_10gR2> DESC dept
Nombre Nulo? Tipo
----------------------- -------- ----------------
DEPTNO NOT NULL VARCHAR2(10)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL_10gR2> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 filas seleccionadas.
SQL_10gR2> VAR bind NUMBER;
SQL_10gR2> EXECUTE :bind := 20
Procedimiento PL/SQL terminado correctamente.
SQL_10gR2> explain plan for
2 SELECT * FROM dept WHERE deptno = :bind;
Explicado.
SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_DEPTNO_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:BIND)
SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
1 fila seleccionada.
SQL_10gR2> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEPTNO")=:BIND)
Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).
Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.
miércoles, 29 de abril de 2009
Diferencias entre COUNT(1) y COUNT(*) - Parte 2
Hace unas semanas, se me acercaron y me hicieron la siguiente pregunta:
"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".
Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:
Bien, veamos el plan de ejecución de la siguiente consulta:
Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.
"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".
Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:
SQL_10gR2> CREATE TABLE test AS
2 SELECT level id, 'texto_'||level texto
3 FROM dual
4 CONNECT BY level <= 100000 ;
Tabla creada.
SQL_10gR2> CREATE UNIQUE INDEX test_id_uq ON test(id) ;
Índice creado.
SQL_10gR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE) ;
Procedimiento PL/SQL terminado correctamente.
Bien, veamos el plan de ejecución de la siguiente consulta:
SQL_10gR2> SELECT COUNT(*)
2 FROM test
3 WHERE id = 100;
COUNT(*)
----------
1
1 fila seleccionada.
SQL_10gR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID d6urw3zfuxz32, child number 0
-------------------------------------
SELECT COUNT(*) FROM test WHERE id = 100
Plan hash value: 4041652814
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN| TEST_ID_UQ | 1 | 1 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.
Suscribirse a:
Entradas (Atom)
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.