Iniciando con la versión 11g de la base de datos Oracle, una nueva y poderosa técnica ha sido implementada para mejorar el rendimiento de operaciones de tipo "Data Definition Language (DDL)". Un nuevo mecanismo de optimización es ejecutado cada vez que se agrega una columna con restricción "not null" a una tabla existente con un valor constante de facto para ser insertado en cada fila, con el objetivo que dicha operación DDL sea realizada casi instantáneamente. ¿Cómo podría ser esto posible si estamos hablando de una tabla que tiene millones de registros los cuales deberían tener su nueva columna modificada con el valor constante que se indicó? ¿Esta nueva clase de optimización de DDLs trae consigo algún efecto secundario del cual deberíamos de estar conscientes antes de usarlo? Esto es lo que se tratará de responder a través de este artículo.
Considere la siguiente tabla con 3 millones de filas:
SQL> create table t1
as select
rownum n1
, trunc ((rownum-1)/3) n2
, trunc(dbms_random.value(rownum, rownum*10)) n3
, dbms_random.string('U', 10) c1
from dual
connect by level <= 3e6;
SQL>
desc t1
Name Null? Type
----------------------- ---------
1 N1 NUMBER
2 N2 NUMBER
3 N3 NUMBER
4 C1 VARCHAR2(4000 CHAR)
Se le agregará una columna adicional con la restricción "not null" incluyendo un valor de facto a la tabla que se ha creado anteriormente:
SQL> alter table t1 add C_DDL number default 42 not null;
Se ha resaltado en negrita dos palabras claves "default" y "not null" porque estas palabras representan la clave para explicar esta nueva característica en Oracle 11g.
Para poder apreciar la diferencia en el tiempo de ejecución de la sentencia "alter table", se ejecutara en dos versiones diferentes de Oracle Database, siendo estas "10.2.0.4.0" y "11.2.0.3.0":
10.2.0.4.0 > alter table t1 add C_DDL number default 42 not null;
Table altered.
Elapsed: 00:00:48.53
11.2.0.3.0> alter table t1 add C_DDL number default 42 not null;
Table altered.
Elapsed: 00:00:00.04
Note la diferencia en los tiempos de ejecución. La columna C_DDL ha sido agregada casi instantáneamente en la base de datos versión 11gR2 y tomó 49 segundos en ejecutar la misma sentencia en la versión "10gR2". ¿Cuál será este nuevo mecanismo que permite este tiempo de ejecución tan extremadamente rápido cuando se agrega una columna con restricción "not null" y con un valor de facto en una tabla existente?
¿Cómo se pudieron actualizar 3 millones de filas en tan solo 4 milisegundos?
A continuación se verificará rápidamente si las modificaciones en las filas de la tabla han sido hechas (De ahora en adelante cuando la versión de Oracle no sea especificada se estará refiriendo a la versión 11.0.2.3)
SQL> select count(1) from t1;
COUNT(1)
----------
3000000
SQL>select count(1) from t1 where c_ddl = 42;
COUNT(1)
----------
3000000
Aunque Oracle haya alterado la tabla T1 casi instantáneamente, la consulta está mostrando que todos los campos de la columna C_DDL han sido actualizados con el valor de facto establecido a 42, esto para todas las filas en la tabla. ¿Cómo puede ser esto posible? ¿tendrá que ver algo aquí el plan de ejecución?
SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3016 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 2999K| 8788K| 3016 (5)| 00:00:10 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
Note de nuevo cómo la parte del predicado del plan de ejecución de arriba puede revelar información muy vital cuando se intenta entender lo que está pasando. A pesar de que no se ha usado la función NVL en la consulta, esta aparece en la parte del predicado indicando que, internamente, Oracle esta todavía usando la columna C_DDL como una columna que contiene los valores establecidos al valor de facto indicado anteriormente 42, sin embargo, lo que en realidad está haciendo es reemplazar el valor nulo con el valor de facto 42. Para esto se tiene la versión anterior como una línea base para comparar e identificar la diferencia.
10.2.0.4.0>select count(1) from t1 where c_ddl = 42;
COUNT(1)
----------
3000000
10.2.0.4.0> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4001 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 3000K| 8789K| 4001 (8)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42)
La usencia de la function NVL en la parte del predicado junto con el tiempo que tomó para agregar la columna en 10gR2 (00:00:48.53) explica el concepto introducido en 11gR1 para optimizar la agregación de columnas "not null" con un valor de facto a una tabla existente.
Digámoslo simple, iniciando desde 11gR1, cuando se agrega una columna "not null" con un valor de facto, Oracle no modificará todas las filas existentes con el valor de facto. En lugar de eso Oracle almacenará como "metadato" este valor de facto para la columna que se está agregando (restricción "not null" y valor de facto 42) y permite a la columna ser agregada casi instantáneamente independientemente de qué tan grande es la tabla que se está alterando. Por supuesto, esto es posible pagando el costo de agregar una función NVL cuando se recupera información de la columna agregada mediante una consulta.
Habiendo explicado este sorprendente concepto de optimización de DDL, se realizará, en la siguiente sección, para investigar un poco más en cómo se maneja esta característica por Oracle con el objetivo de poder asegurar la rapidez de DDL y garantizar el correcto y eficiente resultado durante la recuperación de los datos. Se verá particularmente la diferencia que existe entre recuperar datos de la columna agregada desde los bloques de la tabla en contraste cuando la tabla es accedida por un índice.
Se vio anteriormente que se gana rendimiento cuando se agrega una columna "not null" con un valor de facto. Pero también se vio que esto ha sido hecho posible porque Oracle introduce una función NVL que es aplicada en la columna agregada de modo que intercambia el valor nulo de C_DDL con el valor de facto, el valor de facto es recuperado desde el diccionario de datos. ¿Este uso implícito de la función NVL introduce un efecto secundario?
Bien, primero se vio arriba que esto no ha influenciado en la estimación hecha por el CBO, pues este está estimando exactamente el número de filas que serán recuperadas:
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.37 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.37 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2999K| 3000K|00:00:00.44 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
Pero hay un tiempo extra (44ms) el cual sucede cuando se realiza un escaneo completo de los bloques de la tabla, probablemente debido a la función NVL, este tiempo no aparece cuando se consultan los bloques de la tabla pero usando la versión anterior de Oracle (5ms):
10.2.0.4.0> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42
COUNT(1)
----------
3000000
10.2.0.4.0> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.06 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 3000K| 3000K|00:00:00.05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42)
Cuando una función es aplicada a la columna que está dentro de la parte del predicado, excluirá el uso de cualquier índice que podría existir en esa columna. En este particular caso será la función NVL que está aplicada a la columna C_DDL. ¿Se excluye la función NVL por CBO si esta columna está indexada? Esto es lo que se verá a continuación
Considere el siguiente índice:
SQL> create index i1_c_ddl on t1(c_ddl);
Index created.
Elapsed: 00:00:02.14
Se ejecutará la consulta de nuevo:
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.47 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.47 |
|* 2 | INDEX FAST FULL SCAN| I1_C_DDL | 1 | 2999K| 3000K|00:00:00.75 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42)
Hay buenas noticias para mencionar aquí: La función oculta NVL no es aplicada a la columna C_DDL cuando se recupera su valor usando un índice.
Pero también se podría argumentar y decir que esto es un comportamiento normal, pero: Un índice no puede contener valores con columnas nulas. Entonces se creará un índice compuesto por múltiples columnas con una columna "not null" para proteger valores no nulos de la columna C_DDL.
Algo parecido a esto:
SQL> drop index i1_c_ddl;
Index dropped.
SQL> alter table t1 modify n1 not null;
Table altered.
SQL> create index i2_n1_c_ddl on t1(n1,c_ddl);
Index created.
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where n1= 100 and C_DDL = 42;
COUNT(1)
----------
1
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | INDEX RANGE SCAN| I2_N1_C_DDL | 1 | 1 | 1 |00:00:00.01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=100 AND "C_DDL"=42)
Incluso cuando la columna agregada C_DDL es protegida contra valores nulos por su presencia en un índice compuesto, no hay señales de la función oculta NVL aplicada a la columna C_DDL. Esto está claramente demostrando que, en contraste a los bloques de la tabla donde no hay modificación de la columna C_DDL en cada una de las filas de la tabla, un índice que es creado en la misma columna tendrá sus bloques llenados con el valor de facto de la columna C_DDL. Es decir, el índice sí incluye el valor de facto dentro de sus bloques.
Antes de finalizar esta sección se mostrará un problema interesante. Se ha visto hasta ahora que, cada vez que el CBO ha decidido acceder un bloque de la tabla, aplicó la función NVL a la columna C_DDL para asegurar que los valores de la columna C_DDL no sea un valor nulo, sino el valor de facto (hasta aquí esto no ha sido modificado). Pero se ha visto también que este filtro es siempre aplicado cuando la tabla es escaneada completamente (TABLE ACCESS FULL). ¿Aplicará el CBO esta función NVL cuando la tabla T1 sea accedida por un índice (TABLE ACCESS BY INDEX ROWID)? Se realizará un caso simple y se observará la reacción del CBO en esta particular situación.
SQL> drop index i2_n1_c_ddl;
SQL> create index i2_n1_c_ddl on t1(n1);
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where n1= 100 and C_DDL = 42;
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | I2_N1_C_DDL | 1 | 1 | 1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
3 - access("N1"=100)
Vea como la función NVL es también aplicada en la columna C_DDL incluso cuando la tabla T1 es accedida por medio de un índice.
Ahora se puede decir con confianza que cada vez que el CBO accede un bloque de la tabla, por medio de un bloque sencillo o una lectura de múltiples bloques, aplicará la función NVL a cualquier columna donde una optimización de DDL haya sido aplicada. Sin embargo, el CBO no aplicará la función NVL a la columna donde una optimización de DDL haya sido aplicada, si dicha tabla es accedida desde un bloque hoja de índice.
Con la liberación de la versión 12c de Oracle Database es justo preguntar si la optimización de DDLs está todavía disponible o no. Un ejemplo vale más que mil palabras por lo que se repetirá el mismo experimento pero ahora con esta nueva versión de Oracle Database:
12c > alter table t1 add C_DDL number default 42 not null;
Elapsed: 00:00:00.02
Casi instantáneamente. La optimización de DDL se utiliza aquí también como se mostró de nuevo vía el uso de la función NVL en la parte del predicado de la siguiente consulta:
12c> select count(1) from t1 where c_ddl=42;
COUNT(1)
----------
3000000
12c> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3802 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T1 | 3538K| 43M| 3802 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Pero hay algo adicional en la optimización de DDLs en la versión 12c cuando se compara con la versión 11gR1. En la versión 12c, la optimización de DDLs ha sido ejecutada para incluir columnas nulas teniendo un valor por defecto, no solo no nulas como en 11g. Considere el siguiente "alter table" hecho en 11gR2 y 12cR1 respectivamente para apreciar con más claridad esta diferencia.
11.2.0.3.0> alter table t1 add C_DDL_2 number default 84;
Table altered.
Elapsed: 00:00:58.25
12c> alter table t1 add C_DDL_2 number default 84;
Elapsed: 00:00:00.02
La agregación de la columna C_DDL_2, la cual puede ser nula, tomó 58 segundos para ser ejecutada en 11gR2 y ha sido casi instantáneo en 12cR1.
Esta es una clara demostración que en la versión 12c, la optimización de DDLs ha sido extendida a incluir columnas nulas teniendo valores de facto. De hecho cuando se consulta la tabla T1 para obtener los valores distintos de la nueva columna agregada (C_DDL_2) se verá que todas las filas de la tabla han tenido modificaciones en sus metadatos (valor de facto 84) tal como se muestra vía la siguiente consulta:
12c> select c_ddl_2, count(1) from t1 group by c_ddl_2;
C_DDL_2 COUNT(1)
------- ----------
84 3000000
SQL> select count(1) from t1 where c_ddl_2=84;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3803 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T1 | 3538K| 43M| 3803 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Sin embargo, para ejecutar la optimización de DDLs en columnas nulas con valores de facto, las cosas se convierten más complejas de lo que solían ser con las columnas no nulas en la versión anterior de Oracle Database 11gR2. Fuimos desde un simple uso implícito de la función NVL hasta un complejo y exótico predicado que involucra SYS_OP_VECBIT, una función no documentada por Oracle y una nueva columna interna SYS_NC00006$ para poder decodificar el valor de facto en aquellas filas de la tabla que no han sido modificadas.
En contraste a lo que se pudiera pensar inmediatamente sobe esto, la columna SYS_NC00006$ no es una columna virtual. Representa una columna oculta generada por el sistema como se muestra a continuación:
12c> SELECT
column_name
,virtual_column
,hidden_column
,user_generated
FROM
user_tab_cols
WHERE table_name = 'T1'
AND column_name = ‘SYS_NC00006$’;
COLUMN_NAME VIR HID USE
-------------------- --- --- ---
SYS_NC00006$ NO YES NO
A pesar de que esta columna es oculta no impide que pueda ser seleccionada:
12c>select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
where c_ddl_2 =84
andrownum<=5;
C_DDL_2 SYS_NC00006$
------- ------------
84
84
84
84
84
La columna SYS_NC00006$ permanecerá nula hasta que a la columna C_DDL_2 le sea dado un valor que no es igual al valor de facto 84. Considere las siguientes inserciones de datos:
12c> insert into t1 values (0,0,0,'xxxxx',110,130);
1 row created.
12c> insert into t1 values (1,1,1,'xxxxx',140,150);
1 row created.
12c> insert into t1 values (1,1,1,'xxxxx',200,null);
12c> select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
wherea.c_ddl_2 in (130,150);
C_DDL_2 SYS_NC00006$
------- ------------
130 01
150 01
SQL> select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
wherea.c_ddl_2 is null;
C_DDL_2 SYS_NC00006$
------- ------------
01
Note como la columna oculta SYS_NC00006$ ya no tiene un valor NULL una vez se haya insertado un valor dentro de la columna C_DDL_2 que no es el valor de facto.
Poniendo en su lugar todas las piezas del rompecabezas, se puede fácilmente entender lo que esta parte del predicado exótica, pero muy simple, está realizando:
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84)
Oracle está simplemente verificando a través de su columna generada por el Sistema y vía la función SYS_OP_VECBIT si considerar el valor de facto de la columna C_DDL_2 o el valor real introducido por un usuario final o vía una sentencia INSERT que ha sido ejecutada explícitamente. A continuación se imitará lo que Oracle está haciendo con los valores de la columna SYS_NC00006$, por ejemplo "01" y "NULL".
12c>SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE a.c_ddl_2 IN (130,150)
UNION ALL
SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE a.c_ddl_2 IS NULL
UNION ALL
SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE c_ddl_2 =84
AND rownum<=1
order by c_ddl_2 nulls last
;
C_DDL_2 CBO_DDL
---------- ---------
84 {null}
130 1
150 1
{null} 1
Hay cuatro valores distintos en la columna C_DDL_2, el valor de facto (84) y 3 valores explícitamente insertados 130,150 y null. Cuando se usa un predicado contra la columna C_DDL_2 para recuperar una fila de esa tabla, el CBO decodificará el valor de la columna CBO_DDL (basándose en SYS_NC00006$) para comprobar el valor contra la variable bind (o literal) de entrada. Una vez analizado los datos, Oracle puede decir qué valor retornar, si el valor de facto o el valor insertado explicitamente.
Oracle Database 11gR1 vino con una maravillosa característica que hace que no nos preocupemos del todo de la continuidad de nuestras aplicaciones cuando se agrega una columna no nula con un valor de facto para una tabla grande de producción en la vida real. Esta característica, llamada optimización de DDLs, permite que sentencias tales como "alter table add column" no solamente sean casi instantáneas sino que también no bloquea la tabla. Oracle Database 12c extendió esta característica para incluir columnas nulas con valores de facto. Y la ciruela en el pastel es que parece ser que no repercute en un efecto secundario en cuanto a rendimiento durante la recuperación de los datos en la columna alterada aunque.
Mohamed Houri tiene el grado de PhD en mecánica de fluidos (Computación científica) de la Universidad de Aix-Marseille II, precedida por un diploma de ingeniero en Aeronáutica. Él ha estado trabajando con tecnología Oracle por más de 14 años para diferentes clientes europeos como Consultor independiente especializado en afinación y resolución de problemas de rendimiento de bases de datos. Mohamed ha también trabajado con la Sociedad de Arquitectos Navales de Japón en el análisis de tsunamis usando un poderoso análisis de señales llamado "Wavelet Transform". El mantiene un blog de Oracle y está active en los foros oficiales de Oracle. Su cuenta de twitter es @MohamedHouri. Mohamed también es un miembro de OraWorld Team (www.oraworld-team.com).
Deiby Gómez es un experto administrador de bases de datos, con experiencia en Configuraciones y Soluciones de alta disponibilidad para bases de datos utilizando tecnologías como RAC, Data Guard, Golden Gate, entre otros. Deiby también posee una certificación de Oracle Exadata Database Machine. Es un conferencista frecuente de diferentes eventos de Oracle en Guatemala, entre ellos OTN LAD Tour, Java Day, Primer Simposio de Oracle y en varias universidades. Deiby es el primer Oracle ACE de Guatemala, premio que adquirió en el año 2013. También fue el primer guatemalteco en publicar artículos en las páginas oficiales de Oracle en Español, Portugués e Inglés. Su cuenta de twitter es @hdeiby y su blog es www.oraclefromguatemala.com.gt. Deiby también es un miembro de OraWorld Team (www.oraworld-team.com)