Consideraciones importantes antes de cambiar tablas a modo nologging

Las tablas en una base Oracle por default registran en redo todos los cambios (transacciones) que se le fueron aplicando. Esto sirve para recuperar, ante una caida abrupta de la base de datos, aplicando un procedimiento denominado rolling forward, las transacciones confirmadas que no llegaron a impactarse en los datafiles. El grabado en redo es sincronico y muchas veces es causante de demoras generales en la base de datos, en especial en bases OLTP con alta concurrencia y alta tasa transaccionabilidad, uno de los eventos de espera mas comunes es el log file sync.

Cuando en un reporte AWR o statspack vemos entre los TOp 5 events este tipo de eventos y buscamos en manuales, metalink o en foros, las dos recomendaciones principales son: a) bajar la transaccionabilidad, esto es minimizar los commits, y/o rollbacks, todo lo que sea posible sin afectar las reglas de negocio modeladas ó b) optimizar la escritura en redo, usando por ejemplo discos mas rápidos, recordemos que la escritura en redos, al contrario de la escritura en datafiles, es escritura secuencial, ideal es poner los redos sobre discos de estado solido, por la muy baja latencia y tambien verificar que no esten alojados sobre raid 5, siempre se recomienda raid 1 para estos archivos.

Otra opción interesante es pensar en generar la minima cantidad de redo sin modificar ninguno de los dos puntos tratados en el parrafo anterior. Cambiar a un tabla a nologging permite minimizar el redo generado para ciertas operaciones. Un mito muy común es poner todas las tablas en nologging y pensar que esto impide la registración de cambios en redo, nada mas lejos. La anulación de redo es imposible, ya que es un mecanismo fundamental del funcionamiento de la base de datos, lo que si se logra es reducir bastante la cantidad de redo, con la consecuencia que esto implica, que es ni mas ni menos que ante una falla de la instancia la operacion realizada en modo nologging no se puede recuperar, la unica opción es reprocesar. Las siguientes operaciones son las unicas disponibles para usar nologging:

1. create table...as select
2. create index
3. direct load con SQL*Loader
4. direct load INSERT (usando el hint APPEND)
5. alter table...move partition
6. alter table...split partition
7. alter index...split partition
8. alter index...rebuild
9. alter index...rebuild partition

Ahora, como siempre, comprobemos las operaciones que registran, y las que no, los cambios sobre redo:

Voy a crear una tabla en modo logging (default) y luego voy a a ver el redo que consumió dicha operación.

SQLPLUS> create table t as select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 1610k

La cantidad de redo fue de 1610k, veamos que sucede si creamos la tabla en modo nologging:

SQLPLUS> create table t nologging as select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 100k

El redo usado no fue nulo pero si mucho menor, en el caso aproximadamente 16 veces inferior al de crear la tabla en modo logging.

En el siguiente ejemplo vamos a comparar el redo insumido en una operacion de insert convencional con un insert en modo directo, ambos sobre una tabla nologging:

SQLPLUS> create table t nologging as select * from dba_tables where 1=0

SQLPLUS> insert into t select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 1497k

SQLPLUS> insert into t select /*+ APPEND */ * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 34k

Lo que se puede observar es que en el primer caso, el consumo de redo fue similar al del create table del ejemplo anterior en modo logging. Esto muestra claramente que no todas las operaciones inhiben el redo, en el caso del insert convencional sobre una tabla en nologging no hay diferencia de hacerlo sobre una tabla logging. La diferencia se ve en el insert directo, que justamente es una de las pocas tipos de sentencias que aprovechan el nologging.

Como siempre, es recomendable leer la documentación detenidamente para ver si tal funcionalidad o tal caracteristica es beneficiosa para lo que queremos hacer. Si por el contrario no entendemos bien cierto funcionamiento, tal vez, como es en el caso descripto en esta nota, tendemos a setear todas las tablas que podamos en nologging sin reducir como esperabamos el redo. Además hay que saber evaluar las consecuencias negativas que podría ocasionar tener tablas en nologging y perder la información al no estar sustentada con los redo's.

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.

Factorial de n usando una sentencia SQL simple.

Quizás alguna vez se han preguntado porque no existe una función de agregación PROD, que obtenga el producto de una columna numerica o expresión de una tabla, me parece que no, verdad?. Muy probablemente a la mayoria, nunca se le ha generado esa inquietud, porque de hecho, no tiene, a priori, demasiada utilidad en los sistemas reales. De todas formas, la idea de la nota es mostrarles que tan simple y elegante puede ser una solución usando solo una simple sentencia SELECT, independientemente si le encuentran alguna utilidad o no.

Como una breve introducción, voy a repasar las funciones de agregación, que son variadas. Las mas conocidas y usadas mas frecuentemente son:

SUM: Retorna la suma de una columna o expresión
AVG: Retorna el promedio de una columa o expresión
MIN: Retorna el valor mínimo
MAX: Retorna el valor máximo

Otras funciones, son las estadisticas, que suelo usar cuando necesito analizar tendencias y patrones, y son:

STDDEV : Retorna la desviación standard de una columna o expresión
VARIANCE : Retorna la varianza de una columna o expresión
MEDIAN : Retorna la mediana de una columna o expresión

existen muchas mas funciones de agregación para usar, ver detalle en el manual oficial para la versión de BD que necesiten, por ejemplo para 11g R2: SQL Reference 11.2 R2 (Funciones de Agregación)

En esta oportunidad, voy a mostrar como simular la función PROD usando algunas reglas matematicas simples, y por ultimo, usar dicha función para obtener el factorial de un número dado:

Para comenzar, voy a definir las dos funciones que voy a utilizar:

ln(n) = Retorna el logarimo natural de n

y su función inversa:

exp(n) = Retorna e elevado a la n

Ahora, partiendo de la siguiente propiedad matematica de los logaritmos:

ln(a) + Ln(b) = ln(a * b)

Elevando e con cada lado de la ecuación anterior:

exp(ln(a)+ln(b)) = exp(ln(a*b))

Dado que exp y ln son funciones inversas, entonces se anulan y nos queda a multiplicado por b:

exp(ln(a*b)) = a*b

En este punto, ya tenemos definida y explicada la regla. Ahora voy a mostrar un ejemplo de uso, para obtener el factorial:

La sentencia SQL para obtener el factorial de N tiene la siguiente forma:

select exp(sum(ln(rownum))) from dual
connect by rownum <= N ;

Un ejemplo usando como consola sqlplus:

rop@DESA10G> variable n number
rop@DESA10G> exec :n := 3;

Procedimiento PL/SQL terminado correctamente.


rop@DESA10G> select exp(sum(ln(rownum))) from dual
2            connect by rownum <= :n ; 

EXP(SUM(LN(ROWNUM)))
--------------------                   
6 

rop@DESA10G> exec :n := 6;

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select exp(sum(ln(rownum)))
             from dual
2            connect by rownum <= :n ; 

EXP(SUM(LN(ROWNUM)))
--------------------                 
720  

Como se ve, se puede obtener el factorial de cualquier número con una simple sentencia. También se podria usar para multiplicar las columnas de una tabla, multiplicar expresiones, etc.

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.

Un ejemplo de como realizar calculos con fechas con sql

Hace unos dias me llegó un mail de esos tipicos mails cadena que supuestamente traen suerte si se lo envias a 20 personas mas. Claramente estos mails no tienen sustento real en general y actuan por ingenieria social como mecanismo de generación de tráfico basura. Rara vez alcanzo a leer mas de 2 lineas antes de borrarlos, pero en este caso me llamó la atención y despertó mi curiosidad, ya que probar su veracidad con una consulta sql seria muy facil. Claramente esto no tiene ninguna utilidad mas que mostrarles como resolver cuestiones y relaciones de fechas usando, en este caso Oracle, como una
calculadora extremadamente costosa.

El mail decia algo asi: "Este año el mes de julio tiene 5 viernes, 5 sabados y 5 domingos, esto se da cada 823 años, esto se denomina saco de dinero, envia esto a 20 amigos... bla bla bla". Cuando lo leí, me dí cuenta que no podia ser real ya que tener esa periodicidad tan exacta y extensa, tratandose de fechas, no era posible. Para probarlo prolijamente, realicé una consulta de forma tal de generar fechas automaticamente, comenzando desde una fecha bien lejana (500000 años hacia atrás) y sumando cada vez que el mes fuera Julio (07) los dias viernes, sabado y domingo (6,7,1). Si la suma es 15 entonces en ese año se da que lo que reza el mail en cuestión. A continuación les muestro la consulta y el resultado:

select to_char(dt,'YYYY') dt,count(1)
from (select sysdate-500000+rownum dt
     from dual
     connect by rownum <= 500000)
where to_char(dt,'MM') = '07'  
 and to_char(dt,'d') in (6,7,1)
group by to_char(dt,'YYYY')
having count(1) > 14
order by to_number(dt) desc

 1    2005    15
 2    1994    15
 3    1988    15
 4    1983    15
 5    1977    15
 6    1966    15
 7    1960    15
 8    1955    15
 9    1949    15
10    1938    15
11    1932    15
12    1927    15
13    1921    15
14    1910    15
15    1904    15
16    1898    15
17    1892    15
18    1887    15
19    1881    15
20    1870    15

Como se ve, en el 2005 se dió por ultima vez la relación citada, pasaron solo 6 años para que se repita y no 823!!!.

Análisis de consumo de espacio REDO global, por cada sesión y por cada sentencia

El consumo de espacio de redo (redo consumption) es algo inevitable, aunque es posible minimizarlo en ciertos casos y con ciertas operaciones, no se puede cancelar por completo. El redo es necesario para asegurar que ante una caida imprevista de la base, los datos en los bloques modificados, commiteados y todavia no persistidos en disco (dirty blocks), puedan recuperarse al levantar nuevamente la base con un proceso automatico denominado "rolling forward".

Si la base esta en modo archivelog, cada redo log se copia aparte para que no se sobreescriba y asi permitir, cuando se lo requiera, por ejemplo, poder realizar backup con la base online, ir a un estado anterior de la base, recuperar una base usando el ultimo backup full y aplicando los archives, etc.

Si el consumo de redo es importante, la I/O se va a ver comprometida y podría afectar el rendimiento general de la base. Recordar que la escritura en redo es secuencial, distinta a la escritura en datafiles. Siempre alojar los redo sobre raid 1 o similares y separados de los datafiles. Además, tambien tener en cuenta que con cada commit se debe escribir en redo en forma sincronica. Esto significa serializar, y por lo tanto debe ser lo mas eficiente posible.

Para que puedan medir el consumo de redo, y en consecuencia cantidad de archives generados, en sus bases, les paso una serie de metodos y consultas que uso habitualmente. Las consultas permiten obtener lo siguiente:

• Consumo de Redo de la ultima hora
• Consumo de Redo por sesión
• Consumo de Redo por Consultas (*)

(*) El consumo por sentencia no se puede obtener en forma directa, entonces armé un procedimiento para ir guardando espacio por sqlid. Esta forma puede no ser muy precisa en ciertas ocasiones. Tiene que usarse teniendo ciertos requisitos y consideraciones. Puede ser muy util para testear el consumo de redo de una aplicación antes de la puesta en producción.

Para obtener consumo de redo global de la ultima hora (desde el ultimo snapshot de AWR) (redo consumption by DB)

Ejecutar la siguiente consulta que da el consumo de la base de datos desde el ultimo snapshot, es decir desde la ultima hora exacta. Es decir si lo ejecutamos a las 16:30, nos dará el consumo desde las 16hs para toda la base

select round((t1.value-t2.value)/1024/1024,2) "Consumo_Redo(MB)"
from
(select value from v$sysstat where name = 'redo size') t1,
(select value from dba_hist_sysstat
where stat_name = 'redo size'
and snap_id = (select max(snap_id) from dba_hist_sysstat)) t2

Para obtener consumo de redo por sesión (redo consumption by session)

Ejecutar la siguiente consulta, que da el consumo de redo por sesión. Considerar que el acumulado es desde que la sesión se abre

select se.INST_ID,
 se.SID,
 se.USERNAME,
 se.OSUSER,
 se.TERMINAL,
 se.PROGRAM,
 round(ss.value/1024/1024,2) "Redo Size(Mb)"
from gv$session se,
gv$sesstat ss,
v$statname sn
where se.SID = ss.SID
and ss.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
and username is not null
order by  "Redo Size(Mb)" desc

Para obtener consumo de redo por sentencia (redo consumption by sqlid/sentence)

Primero realizar el setup copiado a continuación (copiar el contenido en un archivo .sql y ejecutarlo desde sqlplus en el usuario elegido como repositorio) :

Rem
Rem  setup_redo_usage.sql (Redo Usage by Sentence/SQLID)
Rem
Rem  NOMBRE
Rem       setup_redo_usage.sql
Rem
Rem  DESCRIPCION
Rem       Configura la programacion de un job para recolectar         
Rem       información de uso de espacio de redo por sqlid.
Rem
Rem  NOTAS - REQUISITOS DE INSTALACION
Rem       El usuario que ejecute el script debera tener quota
Rem       suficiente sobre un tablespace auxiliar (ej:TOOLS)
Rem       para poder almacenar la informacion de monitoreo generada
Rem       cada 5".
Rem       Es necesario otorgar privilegios de SELECT sobre las
Rem       vistas dinamicas:
Rem
Rem       Conectado con sys hacer:
Rem
Rem       grant select on gv_$sesstat to ;
Rem       grant select on v_$statname to ;
Rem       grant select on gv_$session to ;
Rem       grant select on gv_$sqlarea to ;
Rem      
Rem
Rem  MODIFICADO (DD/MM/YY)
Rem
Rem
Rem      Pablo A. Rovedo  06/01/11  -- Creado     v 1.0
Rem

-- Borra la tabla TBL_REDO_USAGE si existe

drop table tmp$redo_usage
/

-- Crea la tabla de repositorio TBL_REDO_USAGE

create table tmp$redo_usage
(
USERNAME     VARCHAR2(30),
OSUSER       VARCHAR2(30),
TERMINAL     VARCHAR2(30),
PROGRAM      VARCHAR2(48),
SQL_ID       VARCHAR2(13),
SQL_FULLTEXT CLOB,
REDO_SIZE    NUMBER,
SNAP         DATE,
INST_ID      NUMBER(1)
)
pctfree 0
nologging
/


-- Crea el procedimiento que recolecta la información de Alocación de
-- espacio de redo

create or replace procedure p_get_redo_usage
is
begin
insert /*+ append */ into tmp$redo_usage
select s.USERNAME,
       s.OSUSER,
       s.terminal,
       s.PROGRAM,
       s.SQL_ID,
       sa.SQL_FULLTEXT,
       round(ss.VALUE/1024/1024) redo_size,
       sysdate,
       s.inst_id
from gv$sesstat ss,
     v$statname sn,
     gv$session s,
     gv$sqlarea sa
where s.sid = ss.sid
  and s.inst_id = ss.inst_id
  and sn.STATISTIC# = ss.STATISTIC#
  and s.sql_id = sa.SQL_ID
  and s.inst_id = sa.inst_id
  and sn.name = 'redo size'
  and s.username not in ('SYS','SYSTEM');
commit;
end;
/

-- Borra el job si ya existe

begin
dbms_scheduler.drop_job(job_name => 'J_SAVE_REDO_USAGE',
                     force => true);
end;
/

-- Crea un nuevo job

begin
dbms_scheduler.create_job(
job_name => 'J_SAVE_REDO_USAGE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin p_get_redo_usage; end;'
,start_date => sysdate
,end_date => sysdate+1/24  -- Recolecta durante 1 hora
,repeat_interval => 'FREQ=SECONDLY;BYSECOND=0,5,10,15,20,25,30,35,40,45,50,55'
,enabled => TRUE
,comments => 'Almacena Informacion de Alocación de espacio de redo');
end;
/

Luego de terminada la recolección (en el script se definió un intervalo
de monitoreo de 1 hora) se puede ejecutar la siguiente consulta para ver
los resultados:

select sql_id,sum(redo_size) "redo_size(Mb)"
from (select unique sql_id,
       redo_size-lead(redo_size) over (partition by sql_id order by snap desc) redo_size
from tmp$redo_usage)
where redo_size is not null
group by sql_id
order by "redo_size(Mb)" desc