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