Como cambiar el umbral de tolerancia de cambios para recolección estadística en 11g (STALE_TOLERANCE)

En varios articulos escribí sobre las estadisticas y su importancia para el correcto funcionamiento del optimizador por costos (CBO). Mantener las estadisticas al dia es a veces una tarea bastante compleja y tediosa, en especial en entornos con gran volumen de datos y alta tasa de cambios. Asegurar que en cada ejecución de sentencias se cuente con estadisticas "frescas" es todo un desafio para los arquitectos y dba's.

A partir de 10g se automatizó bastante dicha tarea, ya que uno de los procesos que corren durante la ventana de mantenimiento, es justamente la recolección estadistica. Para optimizar la recolección solo se actualizan las tablas cuya tasa de cambio sea mayor al 10%. Se puede consultar que tablas estan desactualizadas consultando la vista de catálogo DBA_TAB_STATISTICS, en donde hay un campo llamado STALE_STATS que puede tomar dos valores YES (la tabla necesita nuevas estadisticas) o NO (la tabla no necesita nuevas estadisticas). El umbral es fijo en 10g y no puede modificarse. Ya que la ventana de mantenimiento esta configurada para activarse durante la noche por default, si por ejemplo, un proceso de cambio masivo sobre una tabla genera cambios por mas del 10% no tendremos estadisticas frescas hasta el otro dia. En esos casos se recomienda recolectar estadisticas manualmente inmediatamente despues de la operatoria de cambio sobre las tablas involucradas.

En 11g se puede cambiar el umbral a nivel de tabla, esquema o de la base completa, con lo cual se puede hacer tan sensible la toma de estadisticas como se requiera. En la práctica he usado dicho feature solo con granularidad de tabla en casos donde se detectaron cambios de planes de sentencias que referencian ciertas tablas con cambios menores al 10%. A continuación voy a mostrar como usar el nuevo procedure SET_TABLE_PREFS del paquete DBMS_STATS para cambiar el umbral.

Repasando, en 11g se agregaron los siguiente procedimientos al paquete DBMS_STATS

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

Con los sp's listados arriba se puede realizar las siguientes 3 nuevas configuraciones:

STALE_PERCENT: Para cambiar el umbral que determina cuando una tabla no tiene sus estadisticas al dia.

INCREMENTAL: Para optimizar la recolección sobre tablas particionadas (ver articulo xxx)

PUBLISH: Para testea un nuevo set de estadisticas antes de publicarlas

Voy a mostrar un ejemplo para cambiar el STALE_PERCENT de una tabla, consultando sobre el catalogo para que se vea como se van registrando los cambios:

Primero voy a crear una tabla y luego tomo le tomo las estadisticas manualmente.

create table t as select * from dba_objects

select count(1) from t

begin

dbms_stats.gather_table_stats(ownname = user; tabname = 'T');

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

La columna STALE_STATS nos permite determinar si las estadisticas estan frescas o no. Una práctica común que he visto muchas veces, es mirar la columna LAST_ANALYZED de la vista USER_TABLES. Claramente este valor puede ser engañoso, ya que se tiende a inferir que cuanto mas vieja haya sido la ultima toma mas desactualizada estará la tabla, pero... si la tabla no tuvo cambios importantes desde la ultima recolección?, en ese caso el campo STALE_STATS estará en NO y el LAST_ANALYZED podría tener varios dias o incluso meses. Esto ultimo no implica en absoluto que las stats de la tabla estén desactualizadas. Como regla, siempre recomiendo mirar la columna STALE_STATS para determinar si una tabla tiene las estadisticas correctas, y solo ver el LAST_ANALYZED como un dato adicional.

Ahora voy a generar cambios de tipos diversos a la tabla, de forma tal de generar mas del 10% de cambios, recordar que es el umbral de tolerancia default (STALE_TOLERANCE)

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

Voy a usar la vista USER_TAB_MODIFICATIONS que muestra la cantidad de DML´s por cada tabla desde la ultima toma de estadisticas. Pueden usar la info de dicha tabla, para conocer la tasa de cambios y el tipo de operaciones, lo cual resulta de mucha utilidad para conocer mas acerca de la operatoria en la base de datos.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

no rows selected

No hay registros para la tabla, que raro, no?, si recien habia realizado cambios importantes. En realidad no es raro, el tema es que los cambios primero se almacenan en memoria y son "flusheados" a disco cada 30'. Para forzar el flush hacemos:

begin

dbms_stats.flush_database_monitoring_info;

end;

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

INSERTS UPDATES DELETES

---------- ---------- ----------

3000 3000 3000

Ahora si aparecen los cambios, tal cual se esperaba. Chequeamos si las estadisticas se marcan como "viejas":

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: YES

Justamente, una vez impactados los cambios en el catalogo tambien se actualizó la columna STALE_STATS y pasó de NO a YES.

Con la intro que realicé mas arriba, ahora puedo mostrarles como cambiar el umbral para la tabla T, para que ahora en lugar de tomar el umbral global default, utilice un umbral mayor:

begin

dbms_stats.set_table_prefs(user,'T','STALE_PERCENT','15');

end;

Vuelvo a realizar los inserts, updates y deletes anteriores, realizo flush de cache para actualizar el catálogo y reviso si las estadísticas de la tabla están marcadas como STALE:

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

begin

dbms_stats.flush_database_monitoring_info;

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

Como es observa, ahora las estadísticas no estan desactualizadas para Oracle y por lo tanto no se recolectarán las estadisticas para la tabla en la próxima ventana de mantenimiento. Este nuevo feature permite mayor granularidad para determinar cuando una tabla necesita estadisticas y cuando no se requieren, con lo cual se minimizan los tiempos de recolección, adecuando con mayor precisión dicho proceso a las necesidades particulares de cada tabla, esquema o base de datos.

Row Prefetching en Oracle

Cada vez que una aplicación necesita obtener datos desde la base de datos, se lo solicita al driver y este ejecuta una cierta sentencia que retorna el resultado fila por fila, o mejor aún, retorna un conjunto de filas que son almacenadas del lado del cliente (caching de aplicación) y procesadas posteriormente.

El mecánismo de retornar un conjunto de filas a vez se denomina "row prefetching" y sirve principalmente para minimizar las idas y vueltas a la base (round trips); los datos serán almacenados en la memoria y consumidos desde allí por la aplicación con la consiguiente mejora de rendimiento ya que se minimiza la comunicación con la base de datos. En esta nota tambien voy a mostrar ejemplos usando PL/SQL, Java y C#.

Voy a realizar una comparativa usando bloques PL/SQL para implementar cursores para procesar los datos de una tabla T con 100,000 registros y creada de la siguiente manera:

create table t (id int, pad varchar2(200));

insert into t
select rownum,dbms_random.string('a',200)
from dual
connect by rownum <= 100000; 

Con la tabla creada, ahora voy a ejecutar el bloque que obtiene los datos a procesar con un cursor explicito, y voy a activar el trace para ver la cantidad de fetches que se requieren:

declare
cursor cur1 is select * from t;
l_rec t%ROWTYPE;
begin
open cur1;

loop
 fetch cur1 into l_rec;
 exit when cur1%notfound;
 null;
end loop;
close cur1;
end;


SELECT *
FROM
T


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   100001      0.99       0.76          0     100016          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100003      0.99       0.76          0     100017          0      100000

Se observa desde la salida del trace (previamente procesada con tkprof) que la cantidad de fetches es igual a la cantidad de registros,es decir, se realizó un fetch por cada fila. Probemos realizar la misma operatoria pero ahora usando cursores implicitos:

begin
for i in (select * from t)
loop
null;
end loop;
end;


SELECT *
FROM
T


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     1001      0.23       0.22          0       3899          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.23       0.22          0       3899          0      100000

Con cursores implicitos se necesitaron 1001, por lo cual haciendo una simple cuenta podemos afirmar que el prefetch fue de 100 filas, valor fijo y siempre y cuando el parametro plsql_optimize_level sea 2 (default a partir de 10g R2). Además notar que las lecturas lógicas y el tiempo de procesamiento total es menor cuando se usa prefetch.

Esta podría ser una de las tantas justificaciones para tentarlos a usar cursores implicitos, no?. En general yo suelo usar cursores implicitos (CI), ya que escribo menos, y además he probado que son mas eficientes que los cursores explicitos (CE). Claro, que en casos particulares no nos queda otra opción que usar CE cuando queremos tener mas control de todos las etapas (declare,open,fetch y close). Sin embargo con la introducción de BULK COLLECT podemos mejorar la performance con CE de forma tal de paralelizar. Tambien con bulk collect podemos definir facilmente el tamaño del fetch. Ahi va un ejemplo:

declare
cursor cur1 is select * from t;
type t_type is table of t%ROWTYPE;
l_t t_type;
begin
open cur1;
loop
 fetch cur1 BULK COLLECT into l_t LIMIT 100;
 exit when cur1%notfound;
 for i in l_t.first..l_t.last
 loop
    null;
 end loop;
end loop;
close cur1;
end;


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     1001      0.25       0.24          0       3899          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.25       0.24          0       3899          0      100000

En el ejemplo anterior definí un tamaño de prefetch de 100, tal cual se puede verificar en la salida del trace. Definamos ahora un tambaño de fetch mayor (1000):

declare
cursor cur1 is select * from t;
type t_type is table of t%ROWTYPE;
l_t t_type;
begin
open cur1;
loop
 fetch cur1 BULK COLLECT into l_t LIMIT 1000;
 exit when cur1%notfound;
 for i in l_t.first..l_t.last
 loop
    null;
 end loop;
end loop;
close cur1;
end;


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      101      0.24       0.24          0       3052          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.24       0.24          0       3052          0      100000

Hicieron falta 101 llamadas a la base, cada una retornó 1000 filas al cliente. Dichas filas quedan almacenadas en la memoria del cliente para su posterior utilización.

Por ultimo, voy a mostrar como se define el tamaño del fetch en java y C#:

Porción de Código java para usar prefetch:

try
{
sql = "select id, pad from t";
statement = connection.prepareStatement(sql);
statement.setFetchSize(100);
      resultset = statement.executeQuery();
while (resultset.next())
{
id = resultset.getLong("id");
pad = resultset.getString("pad");
// Implementación de la lógica en el cuerpo del bucle
}
resultset.close();
statement.close();
}
catch (SQLException e)
{
throw new Exception("Error : " + e.getMessage());
}

Porción de Código C# (.NET) para usar prefetch:

sql = "select id, pad from t";
command = new OracleCommand(sql, connection);
command.AddToStatementCache = false;
reader = command.ExecuteReader();
   reader.FetchSize = command.RowSize * 100;
while (reader.Read())
{
  id = reader.GetDecimal(0);
  pad = reader.GetString(1);
  // Implementación de la lógica en el cuerpo del buclejavascript:void(0)
}
reader.Close();

Para ver mas sobre este tema y sobre otros temas de performance, recomiendo la lectura del excelente libro de Christian Antognini: Troubleshooting Oracle Performance (by Christian-Antognini)

Nueva funcionalidad para el SELECT FOR UPDATE (SKIP LOCKED)

En muchas ocasiones tuve la oportunidad de revisar código pl/sql en donde se programa, entre otras cosas, la "marcación" de registros por medio de un flag. Estas marcas, en general se implementan modificando una columna (ej: estado) donde se denota en que etapa del procesamiento se encuentra la sesión y así evitar solapamientos con otras sesiones paralelas que esten haciendo lo mismo.

La forma mas común que yo he visto para realizar la operatoria descripta es usando "SELECT ... FOR UPDATE NOWAIT" del registro de la tabla maestra para asegurar que las demás sesiones no puedan procesar dicho registro. Una vez que el registro se procesó, las otras sesiones podrán tomar el siguiente registro disponible para procesar. Si no se requiere un orden para procesar, este enfoque atenta contra el paralelismo real. Esto se da porque mientras una sesión este procesando un registro las otras deberan esperar a que se commitee para poder procesar el siguiente registro. Esto sucede porque Oracle no lockea los selects, entonces aunque el proceso este procesando un registro dado, no se puede "saltear" y tomar el siguiente, sino que se devuelve un error de que el recurso esta siendo usado (ORA-00054 recurso ocupado y obtenido con NOWAIT).

A partir de 11g se documentó una opción (por lo que pude probar existe desde 9i pero no estaba documentada) muy interesante para lidiar con este tipo de procesamiento, que en realidad es una extensión de la sintaxis del for update para permitir, justamente, para poder procesar con mucho mayor grado de paralelismo y asi posibilitar que cada sesion "saltee" el registro en procesamiento y tome el próximo disponible para procesar. Esto mejora sensiblemente los tiempos de procesamiento general, ya que se podrán levantar n sesiones en paralelo minimizando la interdependencia entre ellas.

Abajo les muestro un ejemplo:

Creo una tabla T y le inserto 100 registros:

create table t (id int,
                estado char(1),
                fecha date,
                importe number(8,2))


insert into t
select rownum,
      'C',
      sysdate+dbms_random.value(-50,50),
      dbms_random.value(1,1000000)
from dual
connect by rownum <= 100  

Cambio el estado de 10 filas, elegidas al aleatoriamente. Dichas filas quedarán en estado 'P', suponiendo que el estado 'P' es disponibles para procesar.

create view t_v as 
select id
from t 
order by dbms_random.value  

update t
set estado = 'P'
where id in (select id from t_v) 
and rownum <= 10  


select * from t where estado = 'P';         

       ID E FECHA        IMPORTE
---------- - --------- ----------
        1 P 20-DIC-10  888292.36
       27 P 09-MAR-11  845864.47
       39 P 19-ENE-11  583901.49
       52 P 23-FEB-11  157817.12
       62 P 05-ENE-11   680744.2
       63 P 19-ENE-11  679375.69
       73 P 20-ENE-11   750069.3
       87 P 26-FEB-11  783555.02
       96 P 13-DIC-10  973668.87
      100 P 28-FEB-11  756671.07

En una consola ejecutamos el siguiente bloque pl, para tomar el siguiente registro a procesar (Sesion 1)

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 1

En otra sesion (Sesion 2) ejecutamos el mismo bloque pl:

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 27

La sesión 1 tomó el registro con id=1 y la sesión 2 tomó el registro con id=27, que son el primero y segundo respectivamente en el listado de mas arriba. Claramente no se commiteo nada y sin embargo la sesion 2 pudo tomar un nuevo registro para procesar mientras la sesión 1 estaba procesando. Con el select for update convencional la sesión 2 hubiese fallado y por código se deberia volver a intentar hasta que la sesión 1 libere el registro (commit/rollback) con id=1 y asi permitir pasar al siguiente.

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:

Como realizar update/delete masivos en forma efectiva

En esta nota voy a mostrarles un método efectivo para modificar o eliminar una gran cantidad de filas sobre una tabla grande. En general las tablas voluminosas se encuentran particionadas para lograr escalar en forma natural. El particionamiento principalmente provee 3 tipos de beneficios: 1) mejora la performance, 2) facilita la administración y mantenimiento y 3) incrementa la disponibilidad de los datos. Resolver una consulta usando como tabla subyacente particionada puede verse de la misma forma que resolver un problema dividiendolo en partes. La conocida premisa: divide y conquistaras es el principal objetivo detrás de particionar.

Desde que se introdujo el feature de partitioning (Oracle 8) se ha ampliado notablemente el set de operaciones posibles sobre tablas e indices para dar soporte y manejar las tablas/indices particionados. Con cada nuevo release se fueron agregando distintas opciones, métodos de particionado y operaciones para manipulación de segmentos. Los distintos features introducidas en cada release son:

Oracle 8 (1997)

• Partition Pruning (*)
• Range Partitioning (incluye operaciones ADD, DROP, RENAME, TRUNCATE, MODIFY, MOVE, SPLIT y EXCHANGE)

Oracle 8i (1999)

• Particionamiento Hash
  
• Particionamiento compuesto: range/hash
  
• Se agregó la operación MERGE

Oracle 9i R2 (2002)

• List Partitioning
• Particionamiento compuesto: Range/List
  
• Cláusula UPDATE GLOBAL INDEXES
  

Oracle 10g R1 (2004)

• Indices globales particionados por Hash y List
  

Oracle 10g R2 (2005)

• Se incremento el limite de particiones/subparticiones de 65k a 4M

Oracle 11g R1 (2007)

• Particionamiento compuesto: range-range, list-range, list-list y list-hash.
• Se agregó particionamiento por intervalo, por referencia y de sistema.

Oracle 11g R2 (2009)

• Columnas virtuales como primary key para tablas particionadas referenciadas.
• Indices particionados por sistema para tablas particionadas por lista.

Como se puede ver, practicamente en cada nuevo release hubo algún agregado de nueva funcionalidad. Sin embargo, a mi entender, el principal feature existe desde el primer release con partitioning (1997). Me refiero al partition pruning o poda de partición, que posibilita que el optimizador (siempre hablando de CBO) elija en forma automática, precisa y transparente la partición o particiones donde se encuentra los datos requeridos. Esto permite segmentar los datos y solo procesar los que nos interesan, sin tener que agregar ninguna inteligencia adicional en el código de aplicación.

Con respecto a las operaciones, la gran mayoria existen desde Oracle 8, solo se agregó tiempo después el MERGE. Una operación muy interensante es EXCHANGE, con la cual se puede intercambiar una tabla sin particionar con una partición. Justamente es esta la operación que voy a usar para proponer una alternativa rapida para cambiar o borrar gran cantidad de filas sobre tablas particionadas. A continuación, somo suelo hacer, voy a mostrar los pasos en detalle y comparar los tiempos y uso de recursos:

Voy a crear una tabla T particionada por lista con 3 particiones

create table t(c1 int,c2 varchar2(10),
c3 date,
c4 char(1))
partition by list (c4)
(
partition t_a values ('A') ,
partition t_b values ('B') ,
partition t_c values ('C')
);

Ahora voy a insertar 10M de filas distribuidas en forma arbitraria sobre las particiones:

insert into t
select rownum,
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
chr(trunc(dbms_random.value(65,68)))
from dual
connect by rownum <= 10000000;                

Inserto 5M de filas sobre la partición en la que voy a trabajar para tener mas filas:

insert into t
select rownum+10000000,          
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
'A'  from dual
connect by rownum <= 5000000;         

Luego de cargados todos los valores se confirman (commit) y luego se recolectan estadisticas.
Veamos el plan para una consulta que cuenta filas sobre la partición 1 (t_a):

explain plan for
select count(1)
from t where c2 > 'R' and c4 = 'A';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2901716037

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |  8455   (3)| 00:03:04 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL   | T    |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Claramente se observa que el optimizador solo accedió la partición 1. Ejecutando la consulta vemos que la estimación del optimizador fue buena:

select count(1)
from t
where c4 = 'A' and c2 > 'R';



COUNT(1)
----------
5610297

El total de filas de la partición es:

select count(1)
from t
where c4 = 'A' ;

COUNT(1)
----------
8333946

En este punto, ya tenemos una partición con mas de 8.3M de filas de las cuales vamos a modificar 5.6M, lo cual es mas del 67%.
Primero voy a testear un update normal sobre la tabla T para luego realizar la comparativa con la misma modificación pero usando otro enfoque mas eficiente.

update t
set c3 = c3+1
where c4 = 'A'
and c2 > 'R'

5610297 filas actualizadas.

Transcurrido: 00:04:45.37

La modificación demoró 4' 45". Pensemos que la base de datos debe mantener la consistencia para garantizar la lectura consistente (mediante el UNDO) y persistir los cambios para poder recuperarse si un evento de falla ocurre durante la modificación (REDO). Estos mecanismos provocan que los tiempos se incrementen y se genere información adicional.

Revisemos cuanto espacio de UNDO y REDO se necesitó para realizar el update:

select 'REDO_SIZE',
round(ms.value/1024/1024) value
from v$mystat ms,
v$statname sn
where ms.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
union all
SELECT 'UNDO_SIZE',
t.used_ublk*8/1024 value
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')

REDO_SIZE  2489 Mb
UNDO_SIZE   885 Mb

Para modificar 5.3M se necesitaron 2489Mb de redo y 885Mb de undo!!!. En el ejemplo, la tabla no tiene indices. Si tuviera indices y la columna modificada sea parte de las columnas de indexación se generaría mas redo y undo, y además la sentencia tendría que actualizar los indices por cada fila modificada lo cual provocaría que el update demore bastante mas. Si el procesamiento masivo fuera un delete en lugar de un update, se generará mas undo (el delete es la operación dml que mas undo genera) y se tendrá que mantener balanceados los indices, lo cual implica mas tiempo de procesamiento.

Existe una forma mas sencilla de realizar el update usando la operación estrella de partitioning: EXCHANGE. Antes de usar el exchange tenemos que crear una tabla auxiliar (T_A) y para acelerar la creación configuro la tabla como nologging e inserto en forma directa usando el hint APPEND.

create table t_a_aux nologging as
select /*+ APPEND */
c1,
c2,
case when (c2>'R') then c3+1
else c3 end c3,
c4
from t
where c4 = 'A'

Transcurrido: 00:00:22.04

Solo se necesitaron 22" para insertar la filas en la tabla auxiliar. Con la función DECODE o CASE realizo el cambio simulando el update. Ahora solo resta realizar el intercambio entre la tabla auxiliar y la partición t_a con la operación EXCHANGE:

ALTER TABLE t
EXCHANGE PARTITION t_a
WITH table t_a_aux ;

Transcurrido: 00:00:11.46

El exchange se realizó en casi 12". Sumando la creación de la tabla auxiliar y el exchange, todo demoró solo 44"!!!, es decir mas de 6 veces mas rapido que el update tradicional.
Ejecutando la consulta para obtener el espacio de redo y undo generado se obtiene:

REDO_SIZE     1 Mb
UNDO_SIZE     0 Mb

Practicamente no hubo alocación de undo/redo. Por lo cual, para ciertos casos resulta muy util usar este metodo para actualizar dado que los tiempos de procesamiento se reducen sensiblemente y ademas los requerimientos de undo y redo son minimizados casi por completo.

Para eliminar (delete) en forma masiva, la creación de la tabla auxiliar solo deberá llenarse con las filas que no se borran. Si se necesitara borrar muchas filas de una tabla no particionada se podrá utilizar el mismo enfoque, es decir reemplazar el delete por un insert en una tabla nueva, recrear los indices y renombrar.

Inserción Directa en Oracle (DIRECT INSERT)

La inserción directa de registros en tablas se realiza con las sentencias INSERT y MERGE (la parte de inserción) o desde una aplicación que utilice la interface directa de OCI (ej sqlloader). Cuando se necesita insertar un gran vólumen de filas en un tiempo óptimo, es necesario sacrificar cierta funcionalidad a expensas de velocidad. La mejora de rendimiento en la inserción no es gratis y hay ciertos requisitos que se deben cumplir y ciertas consecuencias a considerar antes de utilizarla. La inserción directa se activa de dos formas posibles:

- Agregando el hint /*+ APPEND */ en la sentencia INSERT INTO... SELECT ..
- Agregando el hint /*+ APPEND */ para INSERT INTO .. VALUES .. (en 11g R1)
- Agregando el hint /*+ APPEND_VALUES */ para INSERT INTO .. VALUES .. (en 11g R2)
- Ejecutando el insert en paralelo

En los siguientes casos no se puede utilizar la inserción directa

- La tabla a modificar tiene un trigger activo que se dispara con los inserts.
- La tabla a modificar tiene una foreign key habilitada.
- La tabla a modificar es una tabla indexada.
- La tabla a modificar esta almacenada en un cluster.
- La tabla a modificar contiene columna del tipo object type.


A continuación voy a comparar el insert normal con el directo poniendo foco en el espacio de redo y undo consumido en cada caso. Tambien voy a mostrar como el modo directo "saltea" el buffer cache. Justamente esto ultimo es la clave para acelerar los inserts, ya que se arman los bloques nuevos en memoria y se agregan a la tabla en forma directa sin necesidad de usar el cache. Durante la inserciòn no se incrementa el HWM y solo se actualiza al commitear la transacción. Por este motivo no se puede realizar ninguna operación adicional sobre la tabla modificada hasta tanto no se haya confirmado la transacciòn de insert directo. Si intentamos ejecutar cualquier sentencia que referencie a tabla luego de insertar en modo directo Oracle genera el error: "ORA-12838: No se puede leer/modificar un objeto despues de modificarlo en paralelo".

Para el ejemplo voy a crear una tabla T con dos columnas. Voy a usar una columna CHAR(500) para que se utilicen mas bloques sin tener que cargar tantas filas.

drop table t

create table t (x int,y char(500))

Ahora se insertaran 100000 registros en forma normal:

INSERT CONVENCIONAL
-------------------

insert into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

El insert demoró: 10.8 segundos.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

La cantidad de redo usado fue de: 53Mb

  
SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1104k

La cantidad de undo usado fue de: 1104Kb

Con la siguiente consulta chequeamos si se utilizo el buffer para el insert:

select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1

7174 bloques

Se cargaron todos los bloques en cache para la inserción.

Veamos que sucede con el insert en modo directo:

INSERT DIRECTO
--------------

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

10.3s

Demoró solo .5 segundos menos que el insert convencional. Esto puede llegar a desalentarnos de usar el modo directo, ya que no se ve tanta mejora, pero es importante aclarar que la diferencia de tiempos se va a notar mas cuando trabajemos con una cantidad de registros mas importante, yo diria del orden de los millones.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

56Gb

Ahora consumió un poco mas de redo, pero veamos el undo:

SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

8kb

Con el insert directo solo se consumieron 8k de undo, es decir 138 veces menos que con la el insert convencional.

select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1
  
0 Bloques

No se usaron bloques de cache, justamente esto era esperable ya que como comenté mas arriba el insert directo no utiliza memoria intermedia.
Como se vio con el insert directo se redujo notablemente el espacio de undo. Para reducir tambien el redo debemos realizar el insert sobre una tabla que tenga desactivado el logging, es decir que este en nologging (si la base esta en noarchivelog ya tiene desactivado el logging para la tablas cdo se inserta en modo directo).

INSERT DIRECTO CON NOLOGGING
---------------------------------  

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

9.6s

Se redujo el tiempo de inserción, ahora fue de 9.6s

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0

El consumo de redo fue nulo. Si bien esto parece muy bueno hay que tener en cuenta que deshabilitar el logging (en realidad se minimiza, ya que operaciones internas como correr el HWM o agregar extent generan redo y undo) provoca que ante un evento de falla no podamos realizar recovery de los datos recien insertados. Es recomendable realizar un backup lógico de las tablas o un backup incremental con RMAN inmediatamente luego de la carga directa. No es posible "bypassear" el redo para las tablas alojadas en tablespaces con force logging.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

8 kb

El undo se mantuvo exactamente igual que la prueba anterior.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0 bloques

y tampoco se cargaron bloques en memoria.

Recordemos que para las 3 pruebas anteriores utilizamos una tabla sin indices. Veamos que pasa cuando se le agrega un indice a la tabla T

create index t_idx on t (x)

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

11.2.s

Claramente el insert demoró mas, ya que tuvo que mantener el indice actualizado durante las inserciones.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

7

El espacio consumido de redo ahora no fue 0, sigue siendo poco pero ahora es de 7kb, ya que tuvo que guardar información de redo para el indice.

SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1600 kb

El consumo de undo tambien se incremento dado que se guardaron registros de undo para el nuevo indice

La siguiente tablita resume el resultado de las pruebas:




Como resumen, recomiendo utilizar siempre que sea posible el modo directo sobre todo cuando se necesita realizar una carga masiva (procesos ETL) y la performance en la carga es el principal objetivo. Tambien, en lo posible, y teniendo conciencia de lo que implica usar nologging, tambien recomiendo configurar la tabla receptoras en nologging. Por ultimo, es recomendable deshabilitar los indices (ponerlos en unusables) ya que como vimos en el ultimo caso, el mantenimiento de los indices durante la inserción genera mas undo/redo y ralentiza la carga general. Luego de la inserción habrá que reconstruir los indices inutilizados.

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.

Hard Parse vs Soft Parse vs Non Parse

El impacto del parsing en una base de datos puede ser muy variable. En ciertos casos, afortunadamente la mayoria, no es notorio. En otros casos mas especificos puede ocasionar problemas mayores de rendimiento de la base de datos. En general los problemas de excesivo parsing se deben a una mala programación, es decir se deben analizar y solucionar desde el código de las aplicaciones que interactuan con la base. Es por eso que los desarrolladores deben estar concientes del impacto negativo que puede provocar una mala programación y considerar este tema como punto prioritario desde el inicio de la confección del código.

El parsing es el primer paso que se lleva a cabo para procesar una sentencia. En esta etapa se debe conocer de que tipo de sentencia se trata (DML, DDL o un select) para asi poder realizar los correspondientes chequeos. Los principales actividades son el chequeo sintáctico y el análisis semántico

Chequeo Sintáctico

Este chequeo verifica si la sentencia cumple con la gramática de la sentencia definida para la versión de la base.

Análisis Semántico

Se analiza si los objetos referenciados en la sentencia existen, si las columnas existen, si se tiene acceso a los segmentos y a las columnas (privilegios), etc.

Una vez que se pasan con éxito las dos etapas antes mencionadas, Oracle busca en la memoria (shared pool) para ver si ya fue ejecutada la misma sentencia por otra sesión. Si la encuentra, entonces se dice que se realizó un SOFT PARSE. Por otro lado, si no la encuentra, se realizan dos pasos adicionales, que son la optimización de la sentencia y la generación y carga del plan en la memoria (row source generation). La ejecución de todos los pasos se llama HARD PARSE. El hard parsing es cpu intensivo, y en el caso de que sea elevado puede compromenter seriamente la performance general dada la alta contención que se provoca. Para evitar el hard parsing hay que usar variables BIND en los statements (ej: usar preparedStatement). Si se trata de un código "enlatado" donde no se utilizan binds y no puede modificarse se puede utilizar en la base CURSOR_SHARING, cuyo default es EXACT y habría que cambiarlo a SIMILAR (existe a partir de 9i) o FORCE, aunque siempre recomiendo usar SIMILAR, porque es menos riesgoso.

El soft parse puede ser aún mas soft si se cachea el cursor en la sesión (session_cached_cursor) y asi se evita ir a la shared pool a buscarlo. Desde el código de la app se puede habilitar y definir el tamaño de cache mas adecuado (ej: ((oracle.jdbc.OracleConnection)connection).setStatementCacheSize(40)). Esto esta disponible en casi todas las interfaces (jdbc,.NET,PL/SQL,oci,etc).

Para evitar el reparseo en una sesión se debe mantener abierto el cursor. Algunas interfaces tales como PL/SQL, jdbc y la oci permiten realizar esto. La interfaz OLE DB, SQLJ u ODP, al menos hasta la ultima version que conozco, no lo permiten. A continuación voy a copiar 3 fragmentos de código Java para mostrar la diferencia entre parseo hard, soft y no parsear.

El primer fragmento de abajo muestra la NO utilización de binding, ya que se concatenan los literales y no se usa PreparedStatement:

TEST 1
-------

      sql = "SELECT X FROM t WHERE Y = ";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.createStatement();
        resultset = statement.executeQuery(sql + Integer.toString(i));
        if (resultset.next())
        {
          val = resultset.getString("X");       
        }
        resultset.close();
        statement.close();
      }

Este código, además de se muy pobre en performance, propicia el hacking por sql injection.

El segundo fragmento utiliza binding pero abre y cierra el cursor en cada ejecución por lo cual genera soft parse.

TEST 2
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.prepareStatement(sql);
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
        statement.close();
      } 

El último fragmento, que es el óptimo, reduce el parsing al mínimo (solo un soft parsing):

TEST 3
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      statement = connection.prepareStatement(sql);
      for (int i=0 ; i<10000; i++)
      {
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
      } 
      statement.close();

En una prueba que realicé los tiempos de respuesta de cada test fueron los siguientes:

TEST1 --> 12.2"
TEST2 --> 6.4"
TEST2 (caching) --> 3.9"
TEST3 --> 3.7"
TEST3 (caching) --> 3.7"

Como se ve arriba, el TEST2, se puede mejorar usando caching, pero al usar caching en el TEST3 no se ven diferencias.

El parseo se puede ver como una mini compilación, podriamos comparar un código que ejecuta en un bucle un prepareStatement para cada sentencia con un código interpretado. Cualquier programador sabrá que la ejecución de un código compilado es mucho mas rápida que ejecutar un código que necesita interpretarse linea por línea.

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.

Como reasumir procesos que cancelan por falta de espacio (Resumable Space Management)

A partir de Oracle 9i se introduce el mecanismo para suspender y luego reasumir procesos que se quedan sin espacio disponible en el tablespace o alcanzan limitaciones de quota. Este feature permite al operador o dba ejecutar tareas correctivas y así evitar la generación de errores por falta de espacio.
Luego de que la condición de error es corregida la operación suspendida se reasume automáticamente Como funciona la resumisión de espacio alocado

Una sentencia esta habilitada para reasumir si la sesión desde donde fue ejecutada cumple alguna de las siguientes condiciones:

• El parámetro de inicio RESUMABLE_TIMEOUT es distinto a 0.
• Se habilita la sesión para resumir mediante: ALTER SESSION ENABLE RESUMABLE.

Una sentencia con resumisión habilitada es suspendida cuando ocurre una de las siguientes condiciones:

• Falta de espacio.
• Cantidad máxima de extents alcanzada.
• Quota de espacio excedida.

Cuando una sentencia es suspendida se generan las siguientes acciones:

• Se reporta el error en Alert log.
• El sistema genera una alerta de sesión reasumible suspendida.
• Si existe algún trigger registrado que se dispare ante el evento de sistema “AFTER SUSPEND” se ejecuta.

La suspensión de la sentencia resulta en la suspensión de la transacción por lo tanto los recursos transaccionales se mantendrán “tomados” hasta que se reasuma.
Cuando se resuelve la condición de error (por ejemplo, por intervención del usuario o por que otra sentencia haya liberado espacio) la sesión suspendida reasume
automáticamente y se limpia la alerta de sesión resumible suspendida.
Una sentencia suspendida puede ser forzada a terminar mediante la ejecución de DBMS_RESUMABLE.ABORT().
Cada sentencia reasumible tiene asociado un time-out (el default es de 2 horas) que una vez superado retoma la excepción suspendida y retorna el error al usuario.
Una sentencia pude ser suspendida y reasumida multiples veces durante su ejecución.

Las siguientes operaciones pueden ser reasumidas:
Consultas: Las sentencias SELECT que requieran de espacio temporal para ordenar o agrupar.
DML: Las operaciones de INSERT, UPDATE o DELETE ejecutadas desde cualquier interface (OCI, SQLJ, PL/SQL).
Utilidades de Carga y Descarga de datos: Las utilidades como exp/imp, expdp/impdp y sql loader pueden ser parametrizadas por consola para reasumir.
DDL: Las siguientes sentencias son candidatas a reasumir:

CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG


Existen 3 tipos de errores que pueden ser corregidos usando resumisión:

Falta de espacio disponible
La operación no puede alocar un nuevo extent para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice en un tablespace. Los siguientes errores son ejemplos del tipo de error por falta de espacio:
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...

Máxima cantidad de extents
El número de extents maximo es alcanzado para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice. Ejemplos de errores que entran en esta categoría son:
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...

Cuota de espacio excedida
El usuario excedió el espacio disponible en un tablespace dado. El siguiente error es arrojado en dicho caso:

ORA-1536 space quote exceeded for tablespace …

Para reasumir una operación es necesario que la sesión se encuentre en modo de resumisión. La habilitación de dicho modo se puede realizar a nivel general configurando adecuadamente el parámetro de entorno RESUMABLE_TIMEOUT o a nivel sesión usando las cláusula ALTER SESSION. Dado que este tipo de sesiones
bloquean los objetos involucrados cuando entra en modo suspendido, es requisito que el usuario tenga el privilegio de sistema RESUMABLE.

Seteando el parámetro RESUMABLE_TIMEOUT a nivel global o de instancia todas las sesiones ejecutaran sentencias en modo reasumible. El valor default del parámetro es 0 lo cual implica que ninguna sesión permite reasumir. Por ejemplo:
ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600
Implica que ante un evento de error como los citados anteriormente, la sesión quedará suspendida por un tiempo máximo de 1 hora

Utilización de ALTER SESSION para habilitar resumisión

Un usuario puede ejecutar:

Para Habilitar la resumisión:
ALTER SESSION ENABLE RESUMABLE

Para Deshabilitar la resumisión:
ALTER SESSION DISABLE RESUMABLE

También se puede definir el intervalo de time-out (7200 segundos si no se especifica)
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600
También se puede nombrar a la sentencia para identificarla mas fácilmente:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800 NAME 'insert into table';

Las siguientes vistas pueden ser usadas para monitorear el status de las sentencias con resumisión activada:

USER_RESUMABLE: Esta vista contiene detalle de todas las sentencias en ejecución o suspendidas en sesiones con resumisión habilitada de un determinado usuario.

DBA_RESUMABLE: Idem anterior pero para las sesiones de todos los usuarios

V$SESSION_WAIT: Cuando la sentencia queda suspendida la sesión invocante se pone en estado de wait y se puede ver un nueva fila en la vista con el siguiente evento “statement suspended, wait error to be cleared".


A fin de mostrar el uso de este feature se va a realizar la siguiente simulación:
Se crea un tablespace de 5Mb:

rop@TEST10G> create tablespace tbs datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' size 5m
Tablespace creado.

Luego para una tabla T con un solo campo CHAR(100) insertamos filas hasta que se produce el error:

rop@TEST10G> create table t (x char(100)) tablespace tbs;
                                   Tabla creada.
                                   rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; insert into t select 'a' from dual connect by rownum <= 100000             * ERROR en línea 1: ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS Ahora habilitamos la misma sesión para que suspenda y no genere error: rop@TEST10G> alter session enable resumable;
                                   Sesión modificada.
                                   rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; La sesión queda suspendida. Si realizamos la consulta en USER_RESUMABLE se ve lo siguiente:  USER_ID    77 SESSION_ID    65 INSTANCE_ID    1 COORD_INSTANCE_ID    COORD_SESSION_ID    STATUS    SUSPENDED TIMEOUT    7200 START_TIME    06/06/08 15:59:32 SUSPEND_TIME    06/06/08 15:59:32 RESUME_TIME    NAME    User ROP(77), Session 65, Instance 1 SQL_TEXT    insert into t select 'a' from dual connect by rownum <= 100000 ERROR_NUMBER    1653 ERROR_PARAMETER1    ROP ERROR_PARAMETER2    T ERROR_PARAMETER3    128 ERROR_PARAMETER4    TBS ERROR_PARAMETER5    ERROR_MSG    ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS 

Luego desde otra sesión se agrega espacio:

rop@TEST10G> alter database datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' resize 200m;

y una vez agregado el espacio adicional la otra sesión continua insertando las filas que faltaban:

rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000;                                         100000 filas creadas. 

Con este mecanismo se evita que los errores por falta de espacio ocasionen tener que reprocesar todo nuevamente. Cuando una sesión se queda sin espacio suficiente se mantiene suspendida hasta que se agrega mas espacio y luego en forma automática continúa su ejecución hasta que finaliza.
Como contrapartida ese mecanismo debe ser utilizado con cierto cuidado ya que las sesiones suspendidas dejaran transacciones sin confirmar y por lo tanto bloquearan a los objetos involucrados.
Para mas información: Managing Resumable Space Allocation.