Reporte historico de tiempo de ejecucion máxima, mínima y promedio de sentencias SQL

Cualquier DBA que haya trabajado un tiempo administrando bases de datos de producción, seguramente fue consultado, y a veces acusado, debido a demoras en los procesos o reportes. Ante ese tipo de cuestionamientos, lo primero que tenemos que asegurar es si realmente el proceso esta demorado o si se trata de la percepción o ansiedad del usuario u operador. La unica manera de saber eso, es analizando la historia de ejecución de las sentencias involucradas en las rutinas afectadas. Como es sabido, desde 10g contamos con un completo repositorio que se actualiza automaticamente, que entre otras estadisticas y metricas posee información sobre las sentencias ejecutadas. La vista DBA_HIST_SQLSTAT recolecta para cada sentencia, el tiempo de ejecucion general, el tiempo en cpu, el tiempo en i/o, cantidad de ejecuciones, etc. Utilizando dicha información armé un query que muestra la ejecucion mas larga, la ejecucion mas corta y un promedio para cada sentencia registrada.
El query de abajo realiza las agregaciones (max,min y avg) para todas las sentencias ejecutadas durante toda la historia almacenada en AWR (por default 7 dias). Tambien se podria reescribir levemente la query para que dado un sql_id retorne los resultados particulares, recordemos que sql_id es la identificación unica desde 10g para las sentencias sql (antes se usaba hash_value para identificar univocamente una sentencia).

select sql_id,
       to_char(trunc(max(max_ela_time)/60/60),'09')||
       to_char(trunc(mod(max(max_ela_time),3600)/60),'09')||
       to_char(mod(mod(max(max_ela_time),3600),60),'09') max_ela_time,
       to_char(max(max_ela_time_dt),'DD/MM/YYYY HH24:MI') max_ela_time_dt, 
       to_char(trunc(min(min_ela_time)/60/60),'09')||
       to_char(trunc(mod(min(min_ela_time),3600)/60),'09')||
       to_char(mod(mod(min(min_ela_time),3600),60),'09') min_ela_time,
       to_char(min(min_ela_time_dt),'DD/MM/YYYY HH24:MI') min_ela_time_dt, 
       to_char(trunc(avg(avg_ela_time)/60/60),'09')||
       to_char(trunc(mod(avg(avg_ela_time),3600)/60),'09')||
       to_char(mod(mod(avg(avg_ela_time),3600),60),'09') avg_ela_time
from
(select unique sql_id,
       round((first_value(elapsed_time) 
        over (partition by sql_id order by elapsed_time desc))/executions/1000000) max_ela_time,
       first_value(dt) over (partition by sql_id order by elapsed_time desc) max_ela_time_dt,
       round((first_value(elapsed_time) 
        over (partition by sql_id order by elapsed_time))/executions/1000000) min_ela_time,
       first_value(dt) 
        over (partition by sql_id order by elapsed_time) min_ela_time_dt,
       round((avg(elapsed_time) over (partition by sql_id))/executions/1000000) avg_ela_time
from (select unique 
          ss.sql_id,
          s.snap_id,
          lag (s.snap_id) over (partition by s.startup_time,ss.sql_id order by ss.snap_id desc) snap_id_n,
          ss.elapsed_time_total elapsed_time,
          s.begin_interval_time dt,
          lag (ss.elapsed_time_total) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) elapsed_time_n,
          lag (s.begin_interval_time) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) dt_n,
          executions_total executions,
          lag (ss.executions_total) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) executions_n
      from dba_hist_sqlstat ss,
           dba_hist_snapshot s
      where s.snap_id = ss.snap_id)
where elapsed_time > elapsed_time_n
  and executions != 0)
group by sql_id
order by avg_ela_time desc
/

SQL_ID        MAX_ELA_T MAX_ELA_TIME_DT  MIN_ELA_T MIN_ELA_TIME_DT  AVG_ELA_T
------------- --------- ---------------- --------- ---------------- ---------
89qyn4bbt03jq  00 05 24 22/09/2009 18:00  00 00 56 22/09/2009 06:00  00 03 06
gfjvxb25b773h  00 00 13 22/09/2009 17:48  00 00 13 22/09/2009 17:48  00 00 13
a1axyycsv1fb1  00 00 06 21/09/2009 18:00  00 00 06 21/09/2009 18:00  00 00 06
fqmpmkfr6pqyk  00 00 05 21/09/2009 12:00  00 00 05 21/09/2009 12:00  00 00 05
b7jn4mf49n569  00 00 05 21/09/2009 20:00  00 00 05 21/09/2009 20:00  00 00 05
4c1xvq9ufwcjc  00 00 03 22/09/2009 17:48  00 00 03 22/09/2009 17:48  00 00 03
06fhnfwzpzvug  00 00 03 22/09/2009 17:48  00 00 03 22/09/2009 17:48  00 00 03
ahtrk133zdqa5  00 00 02 21/09/2009 22:00  00 00 02 21/09/2009 22:00  00 00 02
bunssq950snhf  00 00 02 21/09/2009 19:00  00 00 02 21/09/2009 19:00  00 00 02
d92h3rjp0y217  00 00 01 23/09/2009 03:00  00 00 00 21/09/2009 19:00  00 00 01
abtp0uqvdb1d3  00 00 02 23/09/2009 08:00  00 00 00 22/09/2009 06:00  00 00 01
8bfst16kjukv6  00 00 01 22/09/2009 17:48  00 00 01 22/09/2009 17:48  00 00 01
6jgrbypm756nu  00 00 01 21/09/2009 11:00  00 00 01 21/09/2009 11:00  00 00 01