Oracle Live

11/04/2016

Performance Analyzer con AAS

Filed under: Database,SQL Tuning — mogukiller @ 11:07 pm
/*TEORIA
	Elapsed Time: Equivale al tiempo de observacion de la muestra.
	DB Time: Es la suma del CPU Time + Wait Time de las sesiones.
	AAS: Average Active Sesion. Metrica que nos ayuda a saber la carga de la base de datos
		1.	AAS << 1. La base de datos no esta haciendo nada
		2.  AAS ~= 0. La base de datos esta Idle
		3.	AAS < num CPU.	Puede haber sesiones aisladas al 100%
		4. AAS >> num CPU : La base de datos esta sobre cargada.
		
		Podemos calcular AAS de dos formas diferentes:
			1.- AAS = DB Time / Elapsed Time	( Basada en tiempos )
			2.- AAS = Count Active Users / samples (Basada en ASH)
			
	VARIANZA: La varianza es una medida de la desviacion de los elementos de una sucesion respecto a su media. 
		Una varianza muy alta refleja que los elementos de esa sucesion son muy dispares entre si. 
		Normalmente se presenta mediante un ratio respecto a la media.

*/

1.- Obtenemos la carga de la base de datos por hora

column sample_hour format a16
select
	instance_number,
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        instance_number,
		sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
		instance_number,
        sample_id,
        sample_time
   ) sub1
group by
	instance_number,
   round(sub1.sample_time, 'HH24')
order by
	instance_number,
   round(sub1.sample_time, 'HH24')
;

/*SALIDA
INSTANCE_NUMBER SAMPLE_HOUR         CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
--------------- ---------------- ---------- ---------- ---------- ------------
              1 2014-03-02 05:00        2,6        6,6        9,2           ,6
              1 2014-03-02 06:00        1,5        1,7        3,2           ,9
              1 2014-03-02 07:00        1,5        2,1        3,6           ,8
              1 2014-03-02 08:00        1,5        2,9        4,4           ,7
              1 2014-03-02 09:00        1,7        3,5        5,2           ,9
              1 2014-03-02 10:00        1,9        4,3        6,2           ,9
              1 2014-03-02 11:00        1,9        4,3        6,1           ,9
              1 2014-03-02 12:00        2,2        4,3        6,5           ,9
              1 2014-03-02 13:00        1,9          4        5,9           ,9
              1 2014-03-02 14:00        1,7        3,5        5,2           ,8
              1 2014-03-02 15:00        1,7        3,4        5,1           ,9
              1 2014-03-02 16:00        1,6        3,1        4,7           ,7
              1 2014-03-02 17:00          2        3,5        5,5            1
              1 2014-03-02 18:00        1,9        3,9        5,8           ,9
              1 2014-03-02 19:00          2        4,8        6,8            1
              1 2014-03-02 20:00          2          5          7          1,1
              1 2014-03-02 21:00        1,9          4        5,9           ,9
              1 2014-03-02 22:00        1,9        3,1          5           ,8
              1 2014-03-02 23:00        1,2        1,8          3           ,9
              1 2014-03-03 00:00        1,3          2        3,3            1
              1 2014-03-03 01:00        2,8        6,8        9,6          6,8
              1 2014-03-03 02:00        1,7        4,1        5,7          2,2
              1 2014-03-03 03:00        1,7       23,7       25,4         27,6	<< Vemos una gran varianza
              1 2014-03-03 04:00        1,4       28,7       30,1         26,8	<< 
              1 2014-03-03 05:00        2,8        3,6        6,4          4,5
              1 2014-03-03 06:00        1,3        1,6        2,9           ,8
              1 2014-03-03 07:00        1,3        2,4        3,6           ,9
              1 2014-03-03 08:00          2        3,5        5,5            1
              1 2014-03-03 09:00        1,9        4,4        6,3          1,1
*/

-- nota: en torno a las 03:00 a las 05:00 del dia 03/03 se ve un pico en la varianza.

2.- Obtenemos la distribucion de la carga por minutos

2.1 - Obtenemos la query desde las tablas de ASH

column sample_minute format a16
undefine  date_yyyy_mm_dd_hh24_mi
select
	INST_ID,
   to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
		INST_ID,
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        gv$active_session_history
     where
		sample_time > to_date('&&date_yyyy_mm_dd_hh24_mi','YYYY_MM_DD_HH24_MI_SS') and
        sample_time < (to_date('&&date_yyyy_mm_dd_hh24_mi','YYYY_MM_DD HH24:MI') + (&interval_min/1440))
     group by
		INST_ID,
        sample_id,
        sample_time
   ) sub1
group by
	INST_ID,
   round(sub1.sample_time, 'MI')
order by
	INST_ID,
   round(sub1.sample_time, 'MI');

/*SALIDA:
	no rows selected
*/

SELECT MIN(sample_time) from gv$active_session_history;
/*
MIN(SAMPLE_TIME)
------------------------
04/03/14 01:57:39,508		<< No podemos usar el ASH
*/

2.2 - Obtenemos la informacion del AWR.

column sample_minute format a16
undefine  date_yyyy_mm_dd_hh24_mi
select
	INSTANCE_NUMBER,
   to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per sampled ASH observation second
     select
		INSTANCE_NUMBER,
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > to_date('&&date_yyyy_mm_dd_hh24_mi','YYYY_MM_DD HH24:MI') and
        sample_time < (to_date('&&date_yyyy_mm_dd_hh24_mi','YYYY_MM_DD HH24:MI') + (&interval_min/1440))
     group by
		INSTANCE_NUMBER,
        sample_id,
        sample_time
   ) sub1
group by
	INSTANCE_NUMBER,
   round(sub1.sample_time, 'MI')
order by
	INSTANCE_NUMBER,
   round(sub1.sample_time, 'MI')
;

/* SALIDA
INSTANCE_NUMBER SAMPLE_MINUTE       CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
--------------- ---------------- ---------- ---------- ---------- ------------
              1 2014-03-03 03:00        3,7        5,3          9          1,3
              1 2014-03-03 03:01        2,8        4,7        7,5           ,3
              1 2014-03-03 03:02        1,3        5,5        6,8           ,6
              1 2014-03-03 03:03        1,2        4,8          6           ,3
              1 2014-03-03 03:04        2,8        5,7        8,5           ,9
              1 2014-03-03 03:05        1,3          6        7,3           ,5
              1 2014-03-03 03:06        2,7          6        8,7           ,4
              1 2014-03-03 03:07        2,7        7,8       10,5          2,3
              1 2014-03-03 03:08        2,3       17,3       19,7          2,3
              1 2014-03-03 03:09        1,3       36,3       37,7           ,8
              1 2014-03-03 03:10        2,3       42,7         45           ,1
.
.
.
              1 2014-03-03 03:50        1,5       54,5         56           ,1
              1 2014-03-03 03:51          2       54,3       56,3           ,1
              1 2014-03-03 03:52          1       54,5       55,5            0
              1 2014-03-03 03:53         ,8       54,7       55,5            0
              1 2014-03-03 03:54        1,2       55,3       56,5           ,1
              1 2014-03-03 03:55         ,8       54,5       55,3            0
              1 2014-03-03 03:56        1,5       54,3       55,8            0
              1 2014-03-03 03:57         ,8       54,2         55            0
              1 2014-03-03 03:58         ,8       54,3       55,2            0
              1 2014-03-03 03:59          8       32,8       40,8          8,5	<< Aqui vemos la carga
              1 2014-03-03 04:00          4        3,8        7,8           ,8

*/

3.- Vemos que se estaba lanzando a esa hora

select
   sub.sql_id,
   sub.seconds_since_date,
   sub.execs_since_date,
   sub.gets_since_date
from
   ( -- sub to sort before rownum
     select
		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 = &instance_number		
     group by		
        sql_id
     order by
        2 desc
   ) sub
where
   rownum < 30;

/*SALIDA
SQL_ID        SECONDS_SINCE_DATE EXECS_SINCE_DATE GETS_SINCE_DATE
------------- ------------------ ---------------- ---------------
18ssh03hc848h              18459               69             354
2bdqkav0vjnjj              16337             1911           52049
9jwmhppfaujyx              13605              261            1190
040nm44zu7urb              10879                0              55
c2cszghak6m08               5607              411           12818
03p2aqqbqbm11               2916               20             122
2brzu0hyax59m               1747               10             120
d748fg5mbmn2f               1742                0               0
cw9atuz5dgtuv               1400             4698         2046314
5fj1uq4f5xy72               1337                0             253
gyak9jayrfzyc                821              699        34759866
9c40fwcd6fh6t                340            19764        56373218
1mz7afnfwntk2                326           322775       100705488
9qkph8269kkf5                211           322774        49707196
18zs36h1cba4c                169            18812        28988650
3kn4htphb7d91                 92           322779        17107287
6jqqs26aahkpc                 85             2731        15381305
5au6fw8qqyhf4                 82           349404         2109444
6vn4ktp0v5qkn                 74                1           43679
6qzssd0f2uys8                 73                3         6397986
bjtc3nwnr91s1                 73                1           43637
dwjf6yzu38qs9                 64              166           20441
f37tqppq30cf2                 57           322778         3550558
c8yb2gyk6jbm7                 57           322769         3550459
1jb39z38t0hhr                 46                2             215
dabsp27dr4qj0                 28             3309         1200371
14yx3w5141d7p                 27            17772         3675219
3tm0w120bqh6k                 17             2559           10651
dnp3kx3xxpnau                 17               30         3853998
*/

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: