Comparacion de Métodos y Tipos de joins en Oracle

Para armar el plan de ejecución el optimizador debe realizar las siguientes acciones básicas:

1. Determinar el orden de evaluación de las tablas.
2. Determinar el método de join.
3. Determinar los tipos de accesos (access path, ej: full scan, rowid, index range, etc).
4. Determinar el orden de filtrado.

Los 3 primeros forman la estructura de árbol que da soporte al plan de ejecución. El 4to define el caudal de datos que "fluye" por el árbol. En esta oportunidad solo voy a concentrarme en el punto 2, dejando los otros para futuras notas.

Los joins se realizan siempre entre dos set de datos, si la sentencias tuviera mas de dos tablas se determinan la dos primeras tablas a joinear y el resultado se joinea con la siguiente tabla, ese resultado se joinea con la siguiente tabla y asi siguiendo.

Los métodos de join mas comunes son:

• NESTED LOOP JOIN
• SORT MERGE JOIN
• HASH JOIN
• CARTESIAN JOIN

Descripción de NESTED LOOP JOIN

Los dos set de datos procesados por nested loop (NL) se llaman outer loop e inner loop. El outer loop es ejecutado una sola vez y el inner loop una vez por cada registro retornado por el outer loop. Las principales caracteristicas de NL son:

• Son la mejor opción cuando se requiere obtener la primera fila lo antes posible, de esta forma no es necesario tener que procesar todos los datos para comenzar a retornar resultados. Esto es muy performante, por ejemplo, para aplicaciones front-end que usan paginación.
• Permiten aprovechar los filtros y condiciones de joins usando los indices disponibles.
• Se pueden usar con cualquier tipo de joins.

Descripción de HASH JOIN

Los dos set de datos procesados por hash join (HJ) son build input y probe input. Con el build input se construye en memoria (o en tablespace temporal si no hubiera suficiente memoria fisica disponible) una tabla de hash. Una vez que se construyó la build input se comienza a procesar usando para cada registro de la probe input la tabla de hash de modo de comparar si se satisface o no la condición de join. Las principales caracteristicas de HJ son:

• La tabla de hash usualmente es contruida usando el set de datos mas pequeño.
• No todos los tipos de joins pueden usarse, por ejemplo los theta joins y cross joins no son soportados.
• Para que se comiencen a retornar las filas la tabla de hash debe estar creada y procesada.
• HJ no puede aplicar condiciones de joins usando indices.

Descripción de SORT MERGE JOIN

Los dos set de datos procesado por el merge join (MJ) son leidos y ordenados de acuerdo a las columnas referenciadas en la condición de join. Una vez que los dos set estan ordenados son mezclados (merge). El ordenamiento se realiza en memoria siempre y cuando la memoria fisica sea suficiente, sino alcanza la memoria (pga) se deberá usar espacio temporal como soporte lo cual, como es esperable, ralentizará las operaciones. Las principales caracteristicas de MJ son:

• Ambos data set deben ser ordenados antes del merge
• La primera fila del result set recien es retornada cuando comienza el merge.
• Todos los tipos de joins son soportados.

Tipos de Joins

Existen dos sintaxis posibles para usar con joins:

SQL-ANSI-86
SQL-ANSI-92

La primera es la que uso en general, y es la mas común, la segunda es mas nueva y es standard para otros motores de base de datos, es mas común para la nuevas generaciones de desarrolladores y dbas o para los que vengan de usar sql server . Es, además mas clara porque separa los filtros de los joins, lo cual es mas sencillo para leer e interpretar. Ahora voy a hacer un breve repaso de los tipos de joins con ejemplos en la dos notaciones:


Cross Join

Tambien llamado producto cartesiano. En general se usa cuando no se especifican los joins para algunas tablas. Tambien lo he visto en ciertos planes particulares donde es la mejor opción , aunque es muy raro

select emp.ename,dept.dname
from emp, dept

select emp.ename,dept.dname
from emp CROSS JOIN dept

Theta Join

Tambien llamados inner join, y retorna solo las filas que satisfacen una condición de join

select emp.enam, salgrade.grade
from emp, salgrade
where emp.sal between salgrade.local and salgrade.hisal

select emp.ename, salgrade.grade
from emp INNER JOIN salgrade on emp.sal between salgrade.losal and salgrade.hisal

Equi Join

Tambien llamado natural join, es un caso especial de theta join donde solo se usan operadores
de igualdad para las condiciones de join

select emp.ename, dept.dname
from emp, dept
where emp.deptno = dept.deptno

select emp.ename, dept.dname
from emp NATURAL JOIN dept on emp.deptno = dept.deptno

Self Join

Son un caso especial de theta join donde la tabla joineada es la misma.

select emp.ename,mgr.ename
from emp, emp mgr
where emp.mgr = mgr.empno


select emp.ename, mgr.ename
from emp JOIN emp mgr on emp.mgr = mgr.empno

Outer Join

Los outer join extienden el result set de los theta joins. Con este tipo de join todas las filas de una de la tablas involucradas son retornadas aunque no matcheen con las columnas de join de la otra tabla, retornando NULL en las columnas de los registros de la tabla que no matchea. Oracle usa una sintaxis propia pero lo recomendable es usar la sintaxis ansi-92 ya que es portable a otros motores de base de datos.

Por ejemplo, para ver la cantidad de empleados por departamento, considerando tambien los departamentos que no tienen ningun empleado:

select dept.dname,count(emp.ename)
from  emp,  dept
where dept.deptno = emp.DEPTNO (+)
group by dept.dname

select dept.dname,count(emp.ename)
from dept LEFT OUTER JOIN emp on (dept.deptno = emp.DEPTNO)
group by dept.dname

Con la nueva sintaxis tambien se puede usar RIGHT OUTER JOIN y FULL OUTER JOIN.

A partir de Oracle 10g es posible usar un nuevo tipo de join ( o subtipo) llamado partitioned outer join. Este tipo de join a priori pareceria estar relacionado con tablas particionadas pero no, en este caso, el concepto de particionado es que los datos se dividen en subset durante la ejecución

select dept.dname, count(emp.empno)
from dept LEFT JOIN emp PARTITION BY (emp.job)  ON emp.deptno = dept.deptno
group by dept.dname

Semi Join

Este tipo de join entre dos tablas retorna solo las filas de una de las tablas cuyas columas de join existen en la otra tabla.

Por ejemplo para ver que empleados tienen bonus:

select *
from  scott.emp emp
where exists (select null from scott.bonus bon
            where emp.EMPNO = bon.ename)
         
select *
from  scott.emp emp
where empno in (select empno from scott.bonus bon)

Anti Join

Este tipo de join entre dos tablas retorna solo las filas de una de las tablas cuyas columnas de join NO existen en la otra tabla

Por ejemplo para consultar los empleados que no tienen bonus:

select *
from  scott.emp emp
where not exists (select null from scott.bonus bon
            where emp.EMPNO = bon.ename)
         
select *
from  scott.emp emp
where empno not in (select empno from scott.bonus bon)

Una vez repasados los tipos de joins retomemos los métodos de joins y veamos con algunos ejemplos como se arman los planes según cada método:

Como siempre voy a crear el entorno para poder probar y si alguien quiere testearlo en su propio ambiente puede hacerlo:

-- Creo tabla T1
create table t1
as
select rownum c1,
     trunc(dbms_random.value(1,100)) c2,
     dbms_random.string('a',100) c3
from dual
connect by rownum <= 1000000  -- Creo tabla T2 create table t2 as select rownum c1,        trunc(dbms_random.value(1,100000)) c2,        dbms_random.string('a',100) c3 from dual connect by rownum <= 2000000  -- Creo un indice para la tabla T2 create index t2_idx on t2(c2)  -- Recolecto estadisticas para los segmentos creados: begin     dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',cascade => true);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'T2',cascade => true);
end;

Ahora voy a mostrar cada método de join, obviamente lo voy a forzar con hints para hacerlo mas sencillo:

Forzamos para que se use NESTED LOOP JOIN:

select  /*+ leading(t1) use_nl(t2) index(t2)  */ count(1)
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'

Plan hash value: 3705558160

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |   109 |  3602   (1)| 00:01:19 |
|   1 |  SORT AGGREGATE     |        |     1 |   109 |            |          |
|   2 |   NESTED LOOPS      |        |    21 |  2289 |  3602   (1)| 00:01:19 |
|*  3 |    TABLE ACCESS FULL| T1     |     1 |   104 |  3600   (1)| 00:01:19 |
|*  4 |    INDEX RANGE SCAN | T2_IDX |    21 |   105 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 3 - filter("T1"."C3">'zzz')
 4 - access("T1"."C2"="T2"."C2")

Forzamos para que se use MERGE JOIN:

select /*+ ordered use_merge(t2) */ count(1)
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'

Plan hash value: 1164406001

------------------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   109 |       | 10796   (2)| 00:03:55 |
|   1 |  SORT AGGREGATE         |        |     1 |   109 |       |            |          |
|   2 |   MERGE JOIN            |        |    21 |  2289 |       | 10796   (2)| 00:03:55 |
|   3 |    SORT JOIN            |        |     1 |   104 |       |  3601   (1)| 00:01:19 |
|*  4 |     TABLE ACCESS FULL   | T1     |     1 |   104 |       |  3600   (1)| 00:01:19 |
|*  5 |    SORT JOIN            |        |  1997K|  9754K|    45M|  7195   (2)| 00:02:37 |
|   6 |     INDEX FAST FULL SCAN| T2_IDX |  1997K|  9754K|       |  1007   (2)| 00:00:22 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 4 - filter("T1"."C3">'zzz')
 5 - access("T1"."C2"="T2"."C2")
     filter("T1"."C2"="T2"."C2")

Forzamos para que se use HASH JOIN:

select /*+ leading(t1) use_hash(t2) */ t1.*
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'


Plan hash value: 442409572

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |   109 |  4620   (2)| 00:01:41 |
|   1 |  SORT AGGREGATE        |        |     1 |   109 |            |          |
|*  2 |   HASH JOIN            |        |    21 |  2289 |  4620   (2)| 00:01:41 |
|*  3 |    TABLE ACCESS FULL   | T1     |     1 |   104 |  3600   (1)| 00:01:19 |
|   4 |    INDEX FAST FULL SCAN| T2_IDX |  1997K|  9754K|  1007   (2)| 00:00:22 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("T1"."C2"="T2"."C2")
 3 - filter("T1"."C3">'zzz')

Comparando los 3 planes para cada método se ve que solo con NL usa index range en lugar de usar un full scan por el indice. Los tiempos de NL son los mejores según la estimación del plan. Ejecutando cada uno de los 3 queries se puede ver que dicha estimación coincide con la realidad y que NL es el mas rapido. Esto se da porque tanto con HJ como con MJ no se puede usar el indice para buscar las coincidencias sobre la tabla T2 basado en los valores retornados por la tabla T1. Con NL se aprovecha dicha información para acceder mas puntualmente, via el indice. Cuanto menor sea la selectividad (o mas fuerte) el método NL tendrá mayor ventaja sobre los otros dos.

Cuando una consulta utiliza un indice, pero no el mejor indice posible

Muchas veces me preguntaron por que una consulta no responde en un tiempo adecuado cuando el plan de ejecución muestra que se esta utilizando un indice. La respuesta en ciertos casos es muy sencilla y se debea a que el indice no es lo mas selectivo posible, no filtra todo lo que podria filtrar. Decir que una consulta usa un plan que accede por un indice no es suficiente para asegurar que se haya encontrado el camino mas eficiente. Para mostrarles un poco de que estoy hablando voy a armar un caso, un tanto trivial pero no por eso menos ilustrativo, para que se entienda la idea.

Voy a crear una tabla T con 3 columnas. La columna COL1 va a tener 1000 valores distintos y la columna COL2 va a tener 10 valores posibles. Además agrego la columna COL3 de relleno

create table t
as 
select mod(rownum,1000) col1,
       mod(rownum,100000) col2,         
      dbms_random.string('a',50) col3
from dual
connect by rownum <= 1000000

Una vez creada la tabla voy a crear un indice por COL1 y recolecto las estadisticas para la tabla y para el indice:

La importancia de ordenar adecuadamente las columnas cuando se define una tabla

Dependiendo del caso, hay que prestar suficiente atención en el orden en el que se definen las columnas en la etapa de diseño fisico de las tablas. Para poder entender la situación que planteo, primero seria bueno que les muestre como almacena Oracle las filas en los bloques.

Una fila se almacena en un bloque de la siguiente forma:

Primero se define el Encabezado (H) que guarda propiedades acerca de la fila en si misma, tales como la cantidad de columnas que tiene y el flag que determina si esta lockeada. Luego vienen los datos en formato de duplas (largo de la columna,contenido de la columna). Como cada columna puede tener diferentes largos, cada una de ellas consta de dos partes: el largo Lx y los datos en si mismo Dx. Dado que el motor de base de datos no conoce el offset de las columnas en la fila, tiene que comenzar desde la primera columna, ver el largo, desplazarse hasta donde se encuentra el dato del largo del segunda columna y asi siguiendo hasta encontrar la columna buscada. Abajo, les muestro como se guarda la fila:



Como se habrán dado cuenta, si se necesita buscar una columna que esta al final, Oracle tardará mucho mas que para buscar una columna del principio, este overhead no es despreciable y podría afectar la performance, sobre todo para aplicaciones con requerimientos de tiempos de respuesta muy bajos, del orden de los milisegundos. Es por eso que en ciertos casos es recomendable definir al principio las columnas con mayor tasa de referencia y al final las que sean menos frecuentemente consultadas.
Para que puedan observar el grado de impacto de un orden de columnas no optimo, voy a armar un ejemplo sencillo que se pueda entender mejor:

Voy a crear una tabla T con 200 columnas de tipo INT, y luego las voy a insertar 5000 filas:

Creo la tabla T con la primera columna X1

create table t (x1 int);

Para no escribir la ddl con las 200 columnas lo voy a hacer dinamicamente, agregando las 199 columnas restantes:

begin
for i in 2..200
loop
  execute immediate 'alter table t add x'||i||' int';
end loop;
end;
/

Ahora voy a insertar las 5000 filas, de forma tal de llenar todas las columnas con el mismo valor por fila.

begin
for i in 1..5000
loop
  insert into t(x1) values (i);
  for j in 2..200
  loop
     execute immediate 'update t set x'||j||' = '||i||' where x1 = '||i;
  end loop;
  commit;
end loop;
end;
/

Una vez creada y populada la tabla T, voy a ejecutar un bloque anonimo que realiza 1000 veces la suma de todas las filas para cada columna Xn:

declare
l_cnt int;
l_foo int;
l_stime int;
begin
for i in 1..200
loop
   l_stime := dbms_utility.get_time();
   for j in 1..1000
   loop
      execute immediate 'select sum(x'||i||') from t' into l_foo;
   end loop;
   insert into t2 values (i,dbms_utility.get_time()-l_stime));
   commit;
end loop;
end;
/

Curva de Comparación

En la curva de arriba el eje X mide la posición de la columna en la fila y el eje Y el tiempo de procesamiento en segundos (usando el bloque pl de arriba) para operar con la columna. Como se puede apreciar el tiempo de procesamiento es directamente proporcional a la ubicación de la columna en la fila.

Como solucionar errores de UNDO cuando se refrescan Vistas Materializadas

La semana pasada estuve en una reunión para definir como solucionar un inconveniente en una de las bases de un cliente. El problema estaba relacionado con el refresco de dos vistas materializadas (las voy a llamar MV1 y MV2 para mantener la privacidad) y lo que ocurría era que en los ultimos dias no se habia podido refrescar las vistas porque se cancelaba el proceso por falta de espacio de UNDO. Las vistas se refrescan en modo COMPLETE cada 1 hora mediante un job en la base y mantienen un detalle diario. En general nunca superan los 100,000 registros, pero ahora tenian mas de 100 millones ya que se detectó que por un error de filtro en el where de la vista MV1 (la MV2 usa una sentencia que referencia a MV1) se tomo el detalle de mas de 2 años en lugar de lo del día.

El equipo de base de datos planteó recrear las vistas, lo cual es una solución valida y estuve de acuerdo en una primera instancia, pero tiene ciertas desventajas: 1) hay que ejecutar un drop e inmediatamente un create de cada vista lo cual puede ocasionar invalidaciones en cascada y por lo tanto debe hacerse en una ventana de mantenimiento y 2) hasta que no finalice la recreación de ambas vistas los objetos dependientes quedarán invalidos y es un tanto complicado estimar con certeza cuanto va a demorar este proceso, con el consiguiente riesgo de salirse de la ventana.

Como solución alternativa sugerí realizar un refresco de la siguiente forma (es importante notar que esto no requiere dropear ninguna mv):

sqlplus>exec dbms_refresh(list=>'MV1',atomic_refresh=>FALSE)

sqlplus>exec dbms_refresh(list=>'MV2',atomic_refresh=>FALSE)

A partitr de 10g el parámetro atomic_refresh por default es TRUE y para saber que significa voy a explicar brevemente como es el proceso de refresco intenamente:

Cada vez que se refresca una vista en modo FORCE se ejecutan dos pasos:

1) Se purga o se eliminan todas las filas actuales de la vista materializada
2) Se insertan las nuevas filas ejecutando el query definido en la MV.

El parámetro atomic_refresh define el método que se usará para realizar el paso 1. En 10g el paso 1 implica un DELETE de todas las filas, se dice que el proceso de refresco en 10g es atómico porque el delete e insert se hacen en una sola transacción (atomicamente). Antes de 10g el valor default del parámetro era FALSE lo cual implicaba que el paso 1 se hiciera con un TRUNCATE, que obviamente es mas rapido que el DELETE ya que no es transaccional. Justamente al no ser transaccional no consume espacio en UNDO, recordar que el DELETE es la operación DML que mas undo consume por lejos, ya que se debe guardar todas las columnas de cada fila por si es necesario una vuelta atrás.

Como conté mas arriba, en el caso particular del refresco de las dos MV's, ambas, por un errror de filtrado en la MV1, quedaron con millones de filas en lugar de con algunas pocas decenas de miles como debiera y dado que la base es 10g esta tomando el parametro default atomic_refesh = TRUE lo que dicta realizar un delete, en este caso será un delete de alrededor de 100M de filas en ambos casos y por lo tanto cancelaba siempre por espacio de UNDO, ya que no esta preparado ni cofigurado para soportar semejante borrado masivo. La sugerencia de cambiar el parametro default atomic_refresh= FALSE realizará un TRUNCATE y luego el insert refrescando las vistas en forma rapida sin necesidad de recrearlas.

Es común que una vez explicado el nuevo funcionamiento en 10g, que alguien se pregunte porque no se sigue truncando en lugar de hacer delete. La explicación es que en el caso que al realizarse el truncate y luego fallar el insert, la MV quedará vacia lo cual podría afectar el negocio ya que quedaran vacias hasta que el refresco se pueda completar con exito. En otro caso que tiene sentido el delete es cuando no pueden quedar nunca vacias las MV's porque se consultan mucho y si se hace truncate no se retornaran filas hasta que finalice el refresco. Generalmente los errores de refresco se produce cuando los datos se obtienen accediendo las tablas fuente por un dblink desde otra base. En el caso de la base en cuestión, este problema no existe ya que las MV's se refrescan con datos de tablas que estan en el mismo esquema.

Como ultima aclaración, es importante resaltar que no existe riesgo en la realización del refresco sugerido y se podrá realizar en cualquier momento del dia sin afectar el funcionamiento general. Una vez que este refrescado se podrán activar los jobs que disparan los refrescos normalmente.

A continuación voy a mostrarles un ejemplo para comparar tiempos, generando una tabla T y una vista materializada MV_T

rop@DESA10G> alter table t add primary key (x);

Tabla modificada.

rop@DESA10G> create materialized view mv_t
  2          refresh complete
  3          as
  4          select * from t;

Vista materializada creada.

rop@DESA10G> set timing on
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>TRUE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:39.75
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>FALSE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:15.54
rop@DESA10G> 

En sintesis, es importante analizar los requerimientos de negocio, si estos requerimientos soportan la corta indisponibilidad que se provoca al refrescar no atomicamente (truncate) además de la posibilidad que quede vacia la MV, producto de un error o cancelación, hasta el próximo refresh, entonces es posible refrescar mas rapido y con muy poco consumo de UNDO seteando el parámetro atomic_refresh en FALSE.

Evolución de los mecanismos para Estabilizar Planes (graciosa comparación)

Mirando un ppt de una presentación de los nuevos features de 11g me gustó uno de los slides donde se hace una especie de parodia sobre la evolución de los mecanismos para estabilizar los planes de ejecución que se fueron agregando en las distintas versiones. Se refiere a Larry (Ellison) que es el fundador y dueño de Oracle comparandolo con Dios y la creación de la tierra. Me pareció muy gracioso, y seguramente van a entender la sutileza todos aquellos que estén en el mundillo Oracle, en especial los que nos dedicamos a temas de performance que muchas veces admiramos la forma en que trabaja el optimizador y otras, sinceramente no entendemos porque toma ciertas decisiones que lo llevan a armar un plan desastroso, en fin... ahi va la evolución de los mecanismos de estabilidad:


In the beginning was the RULE …
On the Second Day, Larry Created the CBO … (7)
On the Third Day, Larry Created the Hint … (7)
On the Fourth Day, Larry Created the Outline … (8)
… and Larry Saw That it Was Good
On the Fifth Day, Larry Created the Profile … (10g)
On the Sixth Day, Larry Created the Baseline (11g)

Una introducción a los histogramas. Para que se usan y como se interpretan

Si alguna vez se pusieron a buscar información sobre que son, como funcionan y para que sirven realmente los histogramas en Oracle se habrán percatado que no existe demasiada data al respecto. La mayoria de la información es solo de referencia y no se explica claramente la verdadera esencia. El tema es bastante extenso, en esta primera parte, mi idea es introducir los conceptos principales, como se guarda la información de histogramas en el catálogo y como se interpreta dicho contenido. En futuras notas voy a mostrarles mas detalle de como se calcula la cardinalidad y el costo de los planes en base a la información estadística y de histogramas y tambien en que casos no sirven.

Oracle utiliza los histogramas para mejorar los calculos de cardinalidad y selectividad cuando la distribución de los datos no es uniforme. Hay que pensar a los histogramas como una "dibujo" de los datos que representa la distribución del contenido en las columnas. Existen dos tipos de histogramas: "Frecuency Histograms" y "Height Balanced Histrograms". Los primeros se usan cuando los valores distintos de la columna son pocos, y los segundos cuando la columna tiene gran cantidad de valores diferentes.

En los histogramas por frecuencia cada valor de la columna se corresponde con una entrada del histograma. Cada entrada contiene en número de concurrencia para un valor. En los histogramas balanceados la columna es dividida en buckets (barras), cada barra tiene el mismo número de filas y cada valor puede ser representado por uno o mas buckets, según su popularidad (cantidad de ocurrencias).

La principal tarea de los histogramas es ayudar a obtener la cardinalidad correcta, como ya comenté en otras notas, el calculo de la cardinalidad es fundamental para que Oracle arme el plan mas adecuado, lo que implica elegir el método y el orden de los joins y la elección de los indices correctos, y asi garantizar la mejor performance posible.

La forma de almacenamiento sigue conceptos estadísticos como percentiles y cuartiles y los valores se agrupan en buckets (si se grafica un histogramas, los buckets son como las barras en un gráfico de barras común y corriente).

Ahora vamos a hacer un par de pruebas y asi tratar de entender un poco mas el concepto detrás de los histogramas.

Primero voy a crear una tabla que tendrá 11110 filas, con cuatro valores posibles (1,2,3 y 4) de forma tal de que cada valor tenga una cantidad redonda de registros (potencia de 10).

rop@DESA10G> create table t
  2  as select case when (rownum between 1 and 10) then 1
  3                 when (rownum between 11 and 110) then 2
  4                 when (rownum between 111 and 1110) then 3
  5                 when (rownum between 1111 and 11110) then 4
  6            end x
  7  from dual
  8  connect by rownum <= 11110;

Tabla creada.

rop@DESA10G> select x,count(1)
  2          from t
  3          group by x;

         X   COUNT(1)
---------- ----------
         1         10
         2        100
         3       1000
         4      10000

La tabla T nos quedó con la distribución que se muestra arriba. Voy a recolectar
estadísticas y ver en la tabla de catálogo USER_TAB_COL_STATISTICS los datos estadisticos de la columna X:

rop@DESA10G> begin 
  2      dbms_stats.gather_table_stats(ownname => user,tabname => 'T');
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : .25
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 02-dic-2009 15:28:59
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE
-----------------

Procedimiento PL/SQL terminado correctamente.

De los datos mostrados arriba vemos que Oracle detectó 4 valores distintos, que no hay valores nulos, que la densidad es 0.25 (1/"cant. de valores distinto"=1/4),que hay un solo bucket y que no hay histogramas (NONE). Ya que en la recolección no explicitamos el parámetro method_opt, que da directivas de como armar los histogramas, se uso el default que es: "for all columns size auto". La unica información de distribución es la siguiente:

rop@DESA10G> select * from user_tab_histograms where table_name = 'T';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
T          X                       0              1
T          X                       1              4

Recolectando estadísticas diciendole a Oracle que use 4 buckets para representar la distribución obtenemos:

rop@DESA10G> begin 
  2      dbms_stats.gather_table_stats(ownname => user,
  3                                    tabname => 'T',
  4                                    method_opt=>'for all columns size 4');
  5  end;
  6  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : .000045004500450045
NUM_NULLS                     : 0
NUM_BUCKETS                   : 4
LAST_ANALYZED                 : 02-dic-2009 16:12:21
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : FREQUENCY
-----------------

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select * from user_tab_histograms where table_name = 'T';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
T          X                       10              1
T          X                      110              2
T          X                     1110              3
T          X                    11110              4
rop@DESA10G> 

Notemos que ahora se creo un "Frecuency Histrogram", que la densidad es mucho menor y viendo la tabla USER_TAB_HISTOGRAMS ahora se representa exactamente la distribución. Para interpretar esa tabla pensemos que la columna ENDPOINT_VALUE es cada valor posible de la columna y el ENDPOINT_NUMBER es la cantidad de registros iguales. Para 1 tenemos 10, para 2 tenemos 110-10=100, para 3 tenemos 1110-110=1000 y asi siguiendo.

Ahora voy a armar una distribución distinta, con muchos valores distintos para mostrarles el otro tipo de histograma:

rop@DESA10G>  drop table t;

Tabla borrada.

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create table t
  2  as select rownum x
  3  from dual
  4* connect by rownum <= 11110
rop@DESA10G> /

Tabla creada.

rop@DESA10G>  update t set x = 1 where rownum <= 5000;

5000 filas actualizadas.

rop@DESA10G> update t set x = 2 where rownum <= 3000 and x != 1;

3000 filas actualizadas.

rop@DESA10G> create index t_idx on t(x);

Índice creado.

Se creó una tabla con la misma cantidad de filas que el ejemplo anterior pero ahora tenemos una distribución muy diferente. Para X=1 tenemos 5000 valores, para x=2 hay 3000 filas y para las demas solo 1 ocurrencia. Como ahora tengo muchos valores distintos si recolecto sin especificar me va a crear el histograma. Para evitar que lo cree en el parametro method_opt le especifiqué "for columns size 1" para que no asocie ningun histograma.

rop@DESA10G> begin
  2      dbms_stats.gather_table_stats(ownname => user,
  3                                    tabname => 'T',
  4                                    cascade => true,
  5                                    method_opt => 'for columns size 1');
  6  end;
  7  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');

Procedimiento PL/SQL terminado correctamente.

Como se ve, consultando la tabla USER_TAB_COL_STATISTICS no obtenemos ningua fila, es decir no tenemos información de histogramas.
Recordemos que para X=1 tenemos 5000 valores, es decir casi la mitad del total de filas, por lo tanto si filtramos en el predicado por dicho valor es de esperar que el optimizador use un full_scan, no?, veamos que pasa:

rop@DESA10G>  explain plan for select count(1) from t where x = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3482591947

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |   111 |   333 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=1)

14 filas seleccionadas.

Notar que el optimizador estimó (columna ROWS) 111 filas, lo cual dista mucho de la realidad, recordemos que tenemos 5000 filas que cumplen con X=1. Si recolectamos usando el método default:

rop@DESA10G> ed
Escrito file afiedt.buf

  1  begin
  2      dbms_stats.gather_table_stats(ownname => user,
  3                                    tabname => 'T',
  4                                    cascade => true);
  5* end;
rop@DESA10G> /

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 3112
LOW_VALUE                     : C102
HIGH_VALUE                    : C3020C0B
DENSITY                       : .00009000900090009
NUM_NULLS                     : 0
NUM_BUCKETS                   : 254
LAST_ANALYZED                 : 04-dic-2009 10:02:53
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 4
HISTOGRAM                     : HEIGHT BALANCED
-----------------

Procedimiento PL/SQL terminado correctamente.

El tipo de histograma ahora es: "HEIGHT BALANCED", la cantidad de filas distintas es 3312 y el nro de buckets es de 254 (el máximo posible)

La representación de los datos en la tabla T ahora es la siguiente:

rop@DESA10G> select * from user_tab_histograms where table_name = 'T';


TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1
T                              X                      181              2
T                              X                      182           8008
T                              X                      183           8052
T                              X                      184           8096
T                              X                      185           8140
T                              X                      186           8184
T                              X                      187           8228
T                              X                      188           8272
T                              X                      189           8315
T                              X                      190           8358
T                              X                      191           8401
T                              X                      192           8444
T                              X                      193           8487
T                              X                      194           8530
T                              X                      195           8573
T                              X                      196           8616
T                              X                      197           8659
T                              X                      198           8702
T                              X                      199           8745
T                              X                      200           8788
T                              X                      201           8831
T                              X                      202           8874
T                              X                      203           8917
T                              X                      204           8960
T                              X                      205           9003
T                              X                      206           9046
T                              X                      207           9089
T                              X                      208           9132
T                              X                      209           9175
T                              X                      210           9218
T                              X                      211           9261
T                              X                      212           9304
T                              X                      213           9347
T                              X                      214           9390
T                              X                      215           9433
T                              X                      216           9476
T                              X                      217           9519
T                              X                      218           9562
T                              X                      219           9605
T                              X                      220           9648
T                              X                      221           9691
T                              X                      222           9734
T                              X                      223           9777
T                              X                      224           9820
T                              X                      225           9863
T                              X                      226           9906
T                              X                      227           9949
T                              X                      228           9992
T                              X                      229          10035
T                              X                      230          10078
T                              X                      231          10121
T                              X                      232          10164
T                              X                      233          10207
T                              X                      234          10250
T                              X                      235          10293
T                              X                      236          10336
T                              X                      237          10379
T                              X                      238          10422
T                              X                      239          10465
T                              X                      240          10508
T                              X                      241          10551
T                              X                      242          10594
T                              X                      243          10637
T                              X                      244          10680
T                              X                      245          10723
T                              X                      246          10766
T                              X                      247          10809
T                              X                      248          10852
T                              X                      249          10895
T                              X                      250          10938
T                              X                      251          10981
T                              X                      252          11024
T                              X                      253          11067
T                              X                      254          11110

75 filas seleccionadas.

La tabla de arriba se lee diferente a la que info que guardaba el histograma del tipo "FRECUENCY" ya que hay muchos valores para representar Oracle uso el tipo de histograma "HEIGHT BALANCED". Analizando el histograma vemos que la columna ENDPOINT_VALUE tiene 254 valores (buckets). La primera fila:

TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1

Tiene 113 buckets asignados. De donde sale es valor?. Pensemeos que tenemos 11110 filas, de las cuales tenemos 5000 con valor 1. Hagamos un poco de aritmetica:

Si tenemos 11100 filas representadas con 254 buckets y para el valor 1 se asignaron 113 tenemos:

rop@DESA10G> select (11110/254)*113 from dual;

(11110/254)*113
---------------
      4942.6378

Cercano a 5000, no?, veamos que pasa para el valor 2:

TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1
T                              X                      181              2

Para sacar la cantidad de buckets del ENDPOINT_VALUE tenemos que hacer:

rop@DESA10G> select (11110/254)*(181-113) from dual;

(11110/254)*(181-113)
---------------------
           2974.33071

Como se ve esta muy cercano a 3000 que es la cantidad real del filas del tipo 2.
La diferencia se da por lo siguiente: Ya que tenemos 11110 filas representadas por 254 barras a buckets entonces tenemos: 11110/254= 43.74 filas por bucket. Los dos valores obtenidos arriba para X=1 y X=2 no son exactos porque se calcula discretizando por bucket.

Una vez obtenido el histograma veamos el plan que se obtiene para la consulta:

rop@DESA10G>  explain plan for select count(1) from t where x = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4943 | 14829 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

14 filas seleccionadas.

Esta vez el optimizador eligió ir por un FULL_SCAN que es el mejor plan ya que tiene que recorrer casi la mitad de la tabla.

Si buscamos un valor con una sola ocurrencia:

rop@DESA10G> explain plan for select count(1) from t where x = 999999;

Explicado.

rop@DESA10G>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3482591947

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=999999)

14 filas seleccionadas.

rop@DESA10G> 

Se observa que se usa el indice en el plan, que obviamente es el mejor acceso posible.
Por último, es importante prestar atención a la cardinalidad (columna ROWS del plan) para el caso de X=1 el calculo fue: 4943 que es el valor redondeado que obtuvimos mas arriba haciendo: (11110/254)*113. De esto podemos confirmar que el Optimizador consultó el histograma para obtener la cardinalidad mas cercana a la real y por lo tanto generó el plan mas adecuado.

Como afecta la frecuencia de commits en los procesos de carga masiva

Hace ya casi 7 años leí una nota en el sitio http://www.dbasupport.com/ , donde un "especialista" aconsejaba commitear con la mayor frecuencia posible, dando argumentos falsos que no resistian a la mas mínima prueba. En ese momento yo estaba leyendo un libro de Tom Kyte donde aconsejaba justamente lo contrario, por eso le postee una pregunta a Tom en su reconocido sitio asktom.oracle.com comentandole lo que habia leido, pueden leerla en: "Issue Frequent COMMIT Statements", les aseguro que no tiene desperdicio. La verdad que nunca imaginé la repecución que tuvo eso, ya que el mismo Tom se comunicó con el autor de la nota y luego de unas idas y vueltas, el autor terminó admitiendo que lo que había escrito lo habia sacado de otro sitio y que analizandolo mejor coincidía que estaba mal. Luego este mismo caso fue referenciado por Tom en su libro "Effective Oracle by Design".

Es importante destacar que además de la degradación de performance, producida por las esperas del tipo sync writes debido a la escritura necesaria en los log files cada vez que se realiza un commit, también se promueven los errores ORA-01555 y las posibles violaciones de integridad cuanto mas frecuentes sean los commits. En esta nota solo les voy a mostrar el impacto de la sobrecarga de commits en el rendimiento de la base y no en los otros problemas derivados.

Voy a mostrarles con un ejemplo de prueba (Test Case) que cuanto menos commits se puedan efectuar mejor será la performance en los procesos de carga o actualización masiva de datos.

Para realizar la prueba usé una base de datos versión 10g R2 y se armé un bloque pl/sql anónimo que esencialmente recorre un cursor autogenerado de 1 millón de registros con una transaccionabilidad definida (cantidad de commits). Para la prueba se realizaron 3 iteraciones por cada valor de prueba. El código utilizado fue el siguiente:

declare
l_time int;
begin
l_time := dbms_utility.get_time;
  for i in (select rownum x,
                dbms_random.string('a',10) y,
                sysdate+(dbms_random.value(-200,200)) z
         from dual
         connect by rownum <= 1000000)       
loop          
  insert into t values (i.x,i.y,i.z);          
  if (mod(i.x,p_filas) = 0) then            
     commit;          
  end if;           
end loop;
dbms_output.put_line((dbms_utility.get_time-l_time)/100);
end; 

Donde el parámetro p_filas denota la cantidad de filas insertadas por cada commit. El proceso carga una tabla T con campos: x (number); y (varchar2) y z (date) con un cursor que genera valores aleatorios en el momento. Este tipo de cursor se creo para evitar cualquier tipo de buffering que pudiera darse si se inserta desde una tabla fuente, ya que haría que la prueba no sea aislada debido a que desde la primera iteración quedarán cacheados en algún substitema de disco o en el cache de Oracle las filas a insertar.

Los resultados obtenidos fueron los descriptos en la siguiente tabla:



En el gráfico de abajo se ve como van bajando los tiempos a medida que se dilata la ejecución del commits, es decir cuanto mas filas se procesan por cada commit:



Como conclusión final podemos ver que cuanto menos commits se realicen mejores serán los tiempos de procesamiento. Por lo tanto, siempre que el tamaño de los segmentos de UNDO estén debidamente estimados y que las reglas de negocio lo permitan es recomendable “commitear” lo menos frecuentemente posible.

Truncate vs Delete

Abajo va un listado de las principales diferencias entre truncar y "deletear" todas las filas de una tabla. Por lo que me han contado gente conocida, parecería ser que enumerar las diferencias entre estas dos operaciones es una de las preguntas clásicas en los test de admisión de perfiles Oracle en las empresas.


1. TRUNCATE es una operacion DDL y es rapido y DELETE una operación DML y es lento.
2. TRUNCATE resetea el HWM y dealoca el espacio, DELETE no.
3. TRUNCATE no tiene vuelta atrás, ni siquiera se puede hacer un flashback. Es raro
que se pueda hacer flashback de un drop pero no de un truncate, no?. Con delete
se puede hacer un rollback y si ya se confirmo el borrado (commit) se podría
utilizar flashback.
4. TRUNCATE no dispara DML's triggers asociados a la tabla truncada.
5. TRUNCATE tiene un tratamiento especial para la MATERIALIZED VIEW LOG vinculada
con la tabla.
6. DELETE puede utilizarse para eliminar un subconjunto de datos, con TRUNCATE hay
que eliminar todas las filas. Sería bueno que existiera algo asi como: TRUNCATE
.. WHERE .., no?
7. TRUNCATE no puede mantener foreign keys, por el contrario con DELETE podemos
hacer delete cascade.
8. TRUNCATE invalida indices globales cuando se truncan particiones y
subparticiones. Por suerte desde 9i R2 se pueden mantener los indices globales
validos usando UPDATE GLOBAL INDEXES.
9. TRUNCATE puede validar indices que ya estaban invalidos y pasarlos a estado
valido. Cuidado cuando para acelerar procesos de carga se deshabiliten los
indices y luego se trunque, ya que la carga se hara con los indices habilitados.
Primero truncar y luego pasar los indices a unusables.
10.Ni TRUNCATE ni DELETE de todas las filas eliminan las estadísticas asociadas.
Sería interesante que se eliminen las estaditicas automaticamete con el TRUNCATE,
no?.
11.TRUNCATE invalida los cursores que referencian a la tabla en cuestión.
12.DELETE de tablas grandes genera una importante cantidad de UNDO y REDO.

Este listado no es definitivo, si alguien lee esta nota y gusta aumentar las lista con mas diferencias será bienvenido.