Oracle Live

11/04/2016

QUERY – Get Top Buffer Gets

Filed under: Queries — mogukiller @ 11:29 pm
Tags:

Mira en el repositorio de awr las queries que mas buffer gets tienen.


/*
                   Elapsed                Elapsed
sql_id        |    Time(s) Executions per Exec(s)  % DB Time | Total Gets     % Gets
============= = ========== ========== =========== ========== = ========== ==========
5sqyjmww4phpm |       2364          1        2364      15,98 | 1636847171      72,56
dfgh288pnq2qa |        411   35576343           0       2,78 |   71013294       3,15
bx7jqsach7jjy |        267          1         267       1,81 |   59155424       2,62
6sbw157x4fhkk |        222          1         222        1,5 |   49483487       2,19
bjrd40k5mmn7v |        297          2       148,5       2,01 |   41118943       1,82
43f8u7y31hycv |        895          2       447,5       6,05 |   40541293        1,8
3kv57xhtutwx4 |         27          0          27        ,18 |   36819347       1,63
*/


SET UNDERLINE =
set HEADS '/'
COL separador HEADING '|' FOR A1
COL seconds_since_date HEADING 'Elapsed/Time(s)'
COL execs_since_date HEADING 'Executions'
COL elpased_execution HEADING 'Elapsed/per Exec(s)'
COL gets_since_date HEADING 'Total Gets'
COL pct_db_time HEADING '% DB Time'
COL pct_gets HEADING '% Gets'
COL ranking NOPRINT
select 
	sql_id,
	'|' as separador,
	seconds_since_date,
	execs_since_date,
	elpased_execution,
	pct_db_time,
	'|' as separador,
	gets_since_date,
	pct_gets,
	ranking
from
(select
   sub.sql_id,   
   sub.seconds_since_date,
   sub.execs_since_date,
   round(sub.seconds_since_date/DECODE(sub.execs_since_date,0,1,sub.execs_since_date),2) elpased_execution,
   round(sub.seconds_since_date*100/sum(sub.seconds_since_date) over ( order by instance_number), 2) pct_db_time,
   sub.gets_since_date,
   round(sub.gets_since_date*100/(sum(sub.gets_since_date) over ( order by instance_number)), 2) pct_gets,
   rank() over( order by gets_since_date desc) ranking
from
   ( -- sub to sort before rownum
     select
		instance_number,
		sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
        sum(executions_delta) as execs_since_date,
        sum(buffer_gets_delta) as gets_since_date		
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where
        begin_interval_time >= to_date('&start_yyyy_mm_dd_hh24','YYYY-MM-DD HH24')
		and end_interval_time <= to_date('&end_yyyy_mm_dd_hh24','YYYY-MM-DD HH24')
		and instance_number = 1		
     group by
		instance_number,
        sql_id
     order by
        2 desc
   ) sub
)where
	ranking <20
order by ranking;

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

Blog de WordPress.com.

A %d blogueros les gusta esto: