toute version - YaoDB Blog

2 octobre 2015Bonnes pratiques, Développement, PL/SQL, SQL

Identifier sa session

En PL/SQL

exec sys.dbms_session.set_identifier          ('identifiant de session')
exec sys.dbms_application_info.set_client_info('info client')
exec sys.dbms_application_info.set_module     ('module', 'action')

Eventuellement, si l’on veut ne changer que l’action

exec sys.dbms_application_info.set_action('action')

En PHP

<?php 
 oci_set_client_identifier($db , 'identifiant de session'); 
 oci_set_client_info      ($db , 'info client'); 
 oci_set_module_name      ($db , 'module'); 
 oci_set_action           ($db , 'action'); 
?>

Retrouver ces informations

select sys_context('USERENV', 'CLIENT_IDENTIFIER') as "client identifier"
     , sys_context('USERENV', 'CLIENT_INFO') as "client info"
     , sys_context('USERENV', 'MODULE') as module
     , sys_context('USERENV', 'ACTION') as action
from sys.dual ;

Avec un peu de mise en forme

SQL> col "client identifier" for a25
SQL> col "client info" for a17
SQL> col module for a17
SQL> col action for a17

SQL> /

client identifier         client info       MODULE            ACTION
------------------------- ----------------- ----------------- -----------------
identifiant de session    info client       module            action

C’était pourtant pas si compliqué

PS: On peut aussi le faire en java ou en .Net si tant est qu’on utilise encore des trucs aussi dépassés ou encore en C via la bibliothèque OCI¹ si l’on est résolument moderne. Tout cela est détaillé dans l’excellent Troubleshooting Oracle Performance de Christian Antognini paru chez APress au chapitre 2 pages 45 à 48. Et de manière plus cryptique et éparpillée dans la documentation de l’éditeur.

1. OCI pour Oracle Call Interface, livré par Oracle avec tous les “clients”

30 septembre 2015Développement, SQL, Support Niveau 3

Lister les structures mémoire

La structure mémoire (X$…) qui garde la trace des structures mémoires est x$kqfta pour les lister c’est la requête suivante :

select KQFTANAM from x$kqfta

ou, plus simple encore :

select name from v$fixed_table

C’est tout de même plus simple que de passer par un code perl qui ne va pas forcément s’avérer complet

Toujours intéressant à lire sur le sujet : http://www.orafaq.com/wiki/X$_Table ou encore http://yong321.freeshell.org/computer/x$table.html

30 septembre 2015Développement, SQL

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é

28 avril 2015Développement, SQL

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 &lt;= :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 &lt;= :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.