Trouver le DDL d’une vue dynamique (V$… ou GV$…)

On utilise v$fixed_view_definition

SELECT * 
FROM v$fixed_view_definition 
where view_name = upper('v$fixed_view_definition')

On obtient :

V$FIXED_VIEW_DEFINITION
SELECT * FROM gv$fixed_view_definition where inst_id = USERENV('Instance')

Et donc, comme on a autre chose que du fromage blanc entre les oreille on passe la requête suivante :

SELECT * 
FROM v$fixed_view_definition 
where view_name = upper('gv$fixed_view_definition')

Pour finalement obtenir :

GV$FIXED_VIEW_DEFINITION
select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx

C’était pourtant pas si compliqué

Requêtage du vide 1/2

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 <= :nbVals

    permet 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_ID 

    permet 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.