Reporte de Perfil de Carga de Trabajo Historica en la Base (Historical Load Profile)

La sentencia que copié más abajo permite realizar un reporte historico de la actividad general o carga de trabajo de una base de datos Oracle. Ya que la consulta obtiene información del AWR la cantidad de historia disponible dependerá de la retención definida en el repositorio (por default es de 7 dias aunque yo siempre aconsejo cambiarlo a 30 dias). La información proporcionada es lo que se muestra en un reporte AWR en la sección LOAD PROFILE, que esta al principio del reporte. Todos aquellos que hayan analizado performance mirando reportes de awr (ejecutando el script awrrpt.sql o bien usando una herramienta gráfica como el TOAD), sabrán que la sección de "Load Profile" junto con los 5 eventos tops son la primera "foto" del "estado de salud" general de la base. Sin embargo esa "foto" no sirve de mucho si no se conoce bien de antemano la actividad y tipo de carga o si no se ve alguna métrica con valores notoriamente grandes.

Para poder realizar un análisis efectivo hay que sacar varios reportes awr y compararlos para establecer diferencias. En esos casos, yo prefiero tener toda la historia disponible de un patallazo para así comparar mas facilmente, ver si alguna métrica esta en valores no habituales, poder exportar la salida del query a una planilla y realizar un gráfico historico para confeccionar un informe.

Antes de ejecutar el query es importante aclarar que los valores reportados están en unidades por segundo (primera columna del load profile de awr) y que funciona en bases 10g o superiores.

with intervals
as
  (select snap,
         extract(second from int)+
         extract(minute from int)*60+
         extract(hour from int)*60*60 int_sec         
  from
  (select end_interval_time snap,
          end_interval_time-lead(end_interval_time) 
  over (partition by startup_time order by snap_id desc) int
   from dba_hist_snapshot))
select to_char(snap_time,'YYYY/MM/DD HH24') snap_time,
       round((redo_size-lead(redo_size) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Redo Size",
       round((logical_reads-lead(logical_reads) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Logical Reads",
       round((db_block_changes-lead(db_block_changes) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Block Changes",
       round((physical_reads-lead(physical_reads) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Physical Reads",
       round((physical_writes-lead(physical_writes) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Physical Writes",
       round((user_calls-lead(user_calls) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "User Calls",
       round((parses-lead(parses) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Parses",
       round((parses_hard-lead(parses_hard) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Parses Hard",
       round((sorts-lead(sorts) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Sorts",
       round((logons-lead(logons) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Logons",
       round((executes-lead(executes) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Exectutes",
       round((user_rollbacks-lead(user_rollbacks) 
       over (partition by startup_time order by snap_time desc)+
       user_commits-lead(user_commits) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Transactions"
from
(select s.end_interval_time snap_time,
        s.startup_time startup_time,
       max(decode(ss.stat_name,'redo size',value,null)) redo_size, 
       max(decode(ss.stat_name,'user rollbacks',value,null)) user_rollbacks,
       max(decode(ss.stat_name,'user commits',value,null)) user_commits,
       max(decode(ss.stat_name,'session logical reads',value,null)) logical_reads,
       max(decode(ss.stat_name,'db block changes',value,null)) db_block_changes,
       max(decode(ss.stat_name,'physical reads',value,null)) physical_reads,
       max(decode(ss.stat_name,'physical writes',value,null)) physical_writes,
       max(decode(ss.stat_name,'user calls',value,null)) user_calls,
       max(decode(ss.stat_name,'parse count (total)',value,null)) parses,
       max(decode(ss.stat_name,'parse count (hard)',value,null)) parses_hard,
       max(decode(ss.stat_name,'sorts (memory)',value,null)) sorts,
       max(decode(ss.stat_name,'logons cumulative',value,null)) logons,
       max(decode(ss.stat_name,'execute count',value,null)) executes
from dba_hist_sysstat ss,
     dba_hist_snapshot s
where s.snap_id = ss.snap_id
  and ss.stat_name in ('user rollbacks','user commits','session logical reads',
'db block changes','physical reads','physical writes','user calls',
'parse count (total)','parse count (hard)','sorts (memory)','logons cumulative',
'execute count','redo size')
group by s.end_interval_time,s.startup_time) t,
intervals int
where t.snap_time = int.snap
order by snap_time desc

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.