Oracle Live

12/04/2016

QUERY – SQL Elapsed time evolution

Filed under: Queries — mogukiller @ 12:07 am
Tags:
-- Obtener la informacion de queries recientes desde V$SQL
/*
                                                                       Physical Reads Logical Reads Time(misec)
SQL_ID        Hour sampled          Inst/Child/Hast Value  Executions       Per Exec      Per Exec    Per Exec
------------- --------------------- ---------------------- ---------- -------------- ------------- -----------
3upyga5tu51k9 2014/04/10 04:59:51   1/0/1744651832                  3          71431     142893,33        1227
*/

-- Obteniendo la información del repositorio de AWR.
/*

                     |            | Elapsed Time Average Elap. Average Elap.  Desviacion Elap.  |
Hour sampled         | Executions |  per Hour(s)   per Hour(s)  Time Total(s)       per Hour(s) |
==================== = ========== = ============ ============= ============== ================= =
2014/03/04 04:00     |          4 |          ,96           ,24            ,32          77623,08 |
2014/03/04 05:00     |          3 |          ,57           ,19            ,32         129652,94 |
2014/03/04 06:00     |          3 |          ,54           ,18            ,32         157611,94 |
2014/03/04 07:00     |          4 |          ,97           ,24            ,32          96131,82 |
2014/03/04 08:00     |          4 |          ,79            ,2            ,32         159091,47 |
*/

-- Obtener la informacion de queries recientes desde V$SQL
Set verify off
BREAK ON sql_id
COL SAMPLE_END HEADING 'Hour sampled'
COL child_hash HEADING 'Inst/Child/Hast Value' FOR A30
COL executions HEADING 'Executions'
COL pio_per_exec HEADING 'Physical Reads|Per Exec'	
COL lio_per_exec HEADING 'Logical Reads|Per Exec'
COL TIME_PER_EXEC HEADING 'Time(misec)|Per Exec'	
column sample_end format a21

SELECT 
	sql_id,to_char(to_date(FIRST_LOAD_TIME,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS') sample_end,
	INST_ID||'/'||CHILD_NUMBER||'/'||PLAN_HASH_VALUE child_hash
      ,EXECUTIONS 
      ,ROUND(DISK_READS/EXECUTIONS,2) PIO_PER_EXEC
      ,ROUND(BUFFER_GETS/EXECUTIONS,2) LIO_PER_EXEC
	  ,ROUND(ELAPSED_TIME/1000/EXECUTIONS) TIME_PER_EXEC	  
    FROM GV$SQL
     WHERE SQL_ID='&sql_id';    

/*
                                                                              Physical Reads Logical Reads Time(misec)
SQL_ID        Hour sampled          Inst/Child/Hast Value          Executions       Per Exec      Per Exec    Per Exec
------------- --------------------- ------------------------------ ---------- -------------- ------------- -----------
3upyga5tu51k9 2014/04/10 04:59:51   1/0/1744651832                          3          71431     142893,33        1227
*/

-- Obteniendo la información del repositorio de AWR.
/*SQL_ID Evolucion temporal */

SET UNDERLINE =
set HEADS '/'
COL time_our FOR A20
COL separador HEADING '|/|' FOR A1
COL time_our HEADING 'Hour sampled'
COL exec_per_hour HEADING 'Executions'
COL seconds_per_hour HEADING 'Elapsed Time/per Hour(s)'
COL sec_per_exec_total HEADING 'Average Elap. /Time Total(s)'
COL avg_seconds_per_exc HEADING 'Average Elap./ per Hour(s)'
COL varian_seconds HEADING 'Desviacion Elap. /per Hour(s)'
     select
		time_our,
		'|' as separador,
		exec_per_hour,
		'|' as separador,
		seconds_per_hour,
		avg_seconds_per_exc,
		round(sum(seconds_per_hour) over (partition by sql_id) / sum(exec_per_hour) over (partition by sql_id),2) as sec_per_exec_total,
		varian_seconds,'|' as separador		
	 from
		(
		 select
			sql_id,
			to_char(trunc(begin_interval_time,'HH'),'YYYY/DD/MM HH24')||':00' as time_our,
			sum(executions_delta) as exec_per_hour,
			round(sum(elapsed_time_delta/1000000),2) as seconds_per_hour,
			round(sum(elapsed_time_delta)/(DECODE(sum(executions_delta),0,1,sum(executions_delta)))/1000000,2) as avg_seconds_per_exc,
			round(variance(elapsed_time_delta)/avg(elapsed_time_delta),2) as varian_seconds		
		 from
			dba_hist_snapshot natural join dba_hist_sqlstat
		 where
			sql_id='&sql_id' 
			and begin_interval_time > sysdate - &days_back
			and elapsed_time_delta >0 and buffer_gets_delta>0
		 group by sql_id,trunc(begin_interval_time,'HH')) sub		 
	order by time_our
	;
/*

                     |            | Elapsed Time Average Elap. Average Elap.  Desviacion Elap.  |
Hour sampled         | Executions |  per Hour(s)   per Hour(s)  Time Total(s)       per Hour(s) |
==================== = ========== = ============ ============= ============== ================= =
2014/03/04 04:00     |          4 |          ,96           ,24            ,32          77623,08 |
2014/03/04 05:00     |          3 |          ,57           ,19            ,32         129652,94 |
2014/03/04 06:00     |          3 |          ,54           ,18            ,32         157611,94 |
2014/03/04 07:00     |          4 |          ,97           ,24            ,32          96131,82 |
2014/03/04 08:00     |          4 |          ,79            ,2            ,32         159091,47 |
*/	


/*SQL_ID Evolucion Physical Reads, Logical Reads*/
/* Informacion extraida del awr*/
Set verify off
BREAK ON sql_id
COL SAMPLE_END HEADING 'Hour sampled'
COL PLAN_HASH_VALUE HEADING 'Plan Hash'
COL executions HEADING 'Executions'
COL pio_per_exec HEADING 'Physical Reads|Per Exec'	
COL lio_per_exec HEADING 'Logical Reads|Per Exec'
COL msec_exec HEADING 'Time (misec)|Per Exec'	
column sample_end format a21

select q.sql_id,to_char(min(s.end_interval_time),'YYYY/MM/DD HH24:MI') sample_end
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and sql_id='&sql_id' 
and begin_interval_time > sysdate - &days_back
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value
/

/*
                                                          Physical Reads Logical Reads Time (misec)
SQL_ID        Hour sampled           Plan Hash Executions       Per Exec      Per Exec     Per Exec
------------- --------------------- ---------- ---------- -------------- ------------- ------------
6ajkhukk78nsr 2014/04/07 07:00               0          4          123,8         36470       1451,4
              2014/04/07 08:00               0          3             ,3       13650,7        348,3
              2014/04/07 09:00               0          4             ,3       14469,8        346,2
*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: