Je veux générer la consommation de temps de chaque SQL_ID sur une période donnée, j’utilise ASH (ou plus exactement v$active_session_history).
Je définis un certain nombre de variables pour rendre le code lisible et ré-utilisable.
- deb: date de début de l’échantillonnage
- fin: date de fin de l’échantillonnage
- nbsec: nombre de seconde d’aggrégation
- nbVals: nombre de valeurs à générer dans l’intervalle d’échantillonnage
- DBID: commenté, pourrait serveir à travailler avec DBA_HIST_ACTIVE_SESS_HISTORY
var deb varchar2(25) var fin varchar2(25) var frmt varchar2(25) var nbsec number var nbVals number -- var dbid number
Je positionne les valeurs des variables, je veux échantillonner sur les 10 dernières minutes
-- exec select dbid into :dbid from v$database exec :frmt := 'YYYYMMDDHH24MISS' exec select to_char(trunc( sysdate - interval '10' minute , 'MI'), :frmt ), to_char( trunc (sysdate, 'MI'), :frmt) into :deb, :fin from dual exec :nbsec := 60 exec :nbVals := 10
La requête est la suivante (et elle n’est forcément pas simple)
with dts as ( select to_char( to_date(:deb, :frmt)
+ ( rownum/ ( 60 *60 *24 ) ) * :nbsec
, :frmt ) dt
from dual
connect by rownum <= :nbVals ) ,
-- ----------------------------------------------------------------------------------
vals as ( select to_char(trunc(SAMPLE_TIME, 'MI'), :frmt) sample_time_min
-- , INSTANCE_NUMBER
-- , DBID
, SQL_ID
, count(*) sec_per_min
-- , count(*) * 10 sec_per_min
from v$active_session_history
-- from dba_hist_active_sess_history
where SAMPLE_TIME between to_date(:deb, :frmt) and to_date(:fin, :frmt)
-- and DBID = :dbid
group by trunc(SAMPLE_TIME, 'MI')
-- , INSTANCE_NUMBER
-- , DBID
, SQL_ID ),
-- ----------------------------------------------------------------------------------
TOPSQL as ( select SQL_ID
, sum(sec_per_min) pos
from vals
group by sql_id
order by 2 desc, sql_id asc )
-- ----------------------------------------------------------------------------------
select dt
, pos
, sql_id
, max(sec_per_min) sec_per_min
from ( Select distinct dt
, sql_id
, pos
, case when dt=sample_time_min then sec_per_min
else 0 end sec_per_min
from dts, vals natural join TOPSQL )
group by dt, sql_id, pos
order by dt, sql_id, pos desc
/
Décorticons
- La sous requête factorisée DTS :
select to_char( to_date(:deb, :frmt) + ( rownum/ ( 60 *60 *24 ) ) * :nbsec , :frmt ) dt from dual connect by rownum <= :nbValspermet de générer les dates d’échantillons à retenir sous forme d’horodatage régulier.
- La sous requête factorisée VALS (sans les commentaires qui permettent de travailler en mode historique)
select to_char(trunc(SAMPLE_TIME, 'MI'), :frmt) sample_time_min , SQL_ID , count(*) sec_per_min from v$active_session_history where SAMPLE_TIME between to_date(:deb, :frmt) and to_date(:fin, :frmt) group by trunc(SAMPLE_TIME, 'MI') , SQL_IDpermet de relier les SQL_ID à leur charge
- La sous requête factorisée TOPSQL
select SQL_ID , sum(sec_per_min) pos from vals group by sql_id order by 2 desc, sql_id asc )permet de calculer la charge globale de chaque SQL_ID et de les classer.
- LA requête en tant que telle effectue le produit cartésien et ne retient que ce qui l’intéresse … C’est cher payé, mais ça donne le résultat escompté
select dt , pos , sql_id , max(sec_per_min) sec_per_min from ( Select distinct dt , sql_id , pos , case when dt=sample_time_min then sec_per_min else 0 end sec_per_min from dts, vals natural join TOPSQL ) group by dt, sql_id, pos order by dt, sql_id, pos desc
Le résultat avec un peu de présentation ( break on dt skip 1 ) :
DT POS SQL_ID SEC_PER_MIN
------------------------- ---------- ------------- -----------
20150427211800 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427211900 10 a1xgxtssv5rrp 10
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212000 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212100 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212200 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212300 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 10
10 gv1qak5fn39tk 10
10 935y1fyshgm9v 0
20150427212400 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 10
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212500 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
20150427212600 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 10
20150427212700 10 a1xgxtssv5rrp 0
10 cyz7q9n00p23a 0
10 d0v9kqgaysm5j 0
10 gv1qak5fn39tk 0
10 935y1fyshgm9v 0
50 lignes sélectionnées.
Certes il fallait y réfléchir un peu plus d’une demi seconde mais …
C’était pourtant pas si compliqué.
Ca reste pourtant très perfectible. On va la remettre sur le métier, histoire que ça ne soit pas du vite-fait mal-fait. Suite dans un prochain épisode.