Catégorie : SQL

Métrologie
Développement, Exploitation, Métrologie, SQL, Tuning, Web

Nombre de redo log files générés, dataviz

Encore ce nombre de redo log files générés … pfff on voit ça tout le temps

Ce que l’on souhaite faire c’est afficher simplement le nombre de redo log files générés en mode graphiquesous forme de heat-map. Pour ce faire on utilise highcharts qui a le bon goût d’être gratuit pour un usage récréatif et non commercial. Et parce qu’on aime s’amuser on testera aussi avec plotly qui est gratuit tout court.

Pour ce faire, On choisit de séparer le code HTML, du javascript et des données de manière ce que chaque partie de code puisse être réutilisés au maximum.

(suite…)

Scripts SQL
Développement, Exploitation, SQL

Branchements et boucles en pur SQL

SQL est pratique mais il est compliqué de produire un script SQL qui s’adapte à toutes les versions de la base oracle ou qui réagit en fonction de certains résultats de requêtes. Cet article pour donner des astuces de scripting afin de gérer des branchements et boucles en pur SQL et de pouvoir créer des scripts adaptifs.
(suite…)

Développement, SQL

Arrondir une date

On est souvent confronté à la problématique suivante, comment arrondir une date en dehors des sentiers tracés par trunc (la minute, l’heure, la semaine …) et donc stackoverflow donne ces exemples de réponses pour arrondir une date à 10 minutes mais qui sont facilement extrapolables à d’autres intervals :

Le plus compréhensible

SELECT
  sysdate, TRUNC(sysdate, 'MI') - MOD(TO_CHAR(sysdate, 'MI'), 10) / (24 * 60)
FROM dual;

Le moins lisible

select
  trunc(sysdate, 'mi') - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;

Le plus à jour

select
  trunc(sysdate, 'mi') 
  - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;

Au niveau du coût pour les 3 on a (avec un temps d’execution identique inférieur au centième de seconde) :

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        657  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Un exemple ?

alter session set NLS_DATE_FORMAT='YY-MM-DDHH24:MI:SS' ;

col DT   for a8 wrap
col S10  for a8 wrap
col M    for a8 wrap
col M5   for a8 wrap
col M10  for a8 wrap
col M15  for a8 wrap
col M30  for a8 wrap
col H2   for a8 wrap
col H3   for a8 wrap
col H6   for a8 wrap
col H8   for a8 wrap
col H12  for a8 wrap
col DAY  for a8 wrap
col WEEK for a8 wrap

select sysdate                                                                                     DT
     , sysdate - mod(EXTRACT(second FROM cast(sysdate as timestamp)), 10) / (24 * 60 * 60)         S10
     , trunc(sysdate, 'mi')                                                                        M
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 5) / (24 * 60)  M5
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60) M10
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 15) / (24 * 60) M15
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 30) / (24 * 60) M30
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 2) / 24         H2
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 3) / 24         H3
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 6) / 24         H6
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 8) / 24         H8
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 12) / 24        H12
     , trunc(sysdate, 'dd')                                                                        DAY
     , trunc(sysdate, 'd')                                                                         WEEK
from dual
/
DT S10 M M5 M10 M15 M30 H2 H3 H6 H8 H12 DAY WEEK
18-05-14
17:28:39 		18-05-14
17:28:30 		18-05-14
17:28:00 		18-05-14
17:25:00 		18-05-14
17:20:00 		18-05-14
17:15:00 		18-05-14
17:00:00 		18-05-14
16:00:00 		18-05-14
15:00:00 		18-05-14
12:00:00 		18-05-14
16:00:00 		18-05-14
12:00:00 		18-05-14
00:00:00 		18-05-13
00:00:00

C’était pourtant pas si compliqué

Développement, Exploitation, SQL

Retrouver les informations relatives à ma session

Je veux savoir quelle est ma session au niveau de v$session et à quel processus OS elle correspond

La requête à passer est la suivante :

set pages 0 lines 230 newp none emb on

col machine for a25

select v.SID, v.USERNAME, v.MACHINE, v.PROGRAM, v.TERMINAL, SPID "OS PID", v.process
from v$session v inner join v$process p on v.paddr=p.addr
where v.AUDSID=USERENV('SESSIONID')
and v.MACHINE=SYS_CONTEXT('USERENV','HOST')
and v.TERMINAL=USERENV('TERMINAL')
/

Son résultat :

       
SID        USERNAME                       MACHINE                   
---------- ------------------------------ ------------------------- 
       633 SYS                            srvorcl.ojo.fr            
       
PROGRAM                                          TERMINAL                       
------------------------------------------------ ------------------------------      
sqlplus@srvorcl.ojo (TNS V1-V3)                  pts/2                          

OS PID                   PROCESS
------------------------ ------------------------  
147476                   147469

On a OS PID qui correspond à l’identifiant du process de connexion de SQLPLUS à la base de données et process qui correspond l’indentifiant du process sqlplus

ps -edf | grep 147476 | grep -v grep
oracle   147476 147469  0 15:12 ?        00:00:00 oracleTECH (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

ps -edf | grep 147469 | grep -v grep
oracle   147469 197784  0 15:12 pts/2    00:00:00 sqlplus
oracle   147476 147469  0 15:12 ?        00:00:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Pour tuer la connexion sans tuer sqlplus (et se retrouver en mode nolog) c’est le process 147476 qu’il faut tuer.

C’était pourtant pas si compliqué

Bonnes 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 OCI1 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”

Dé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

Dé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é

Dé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.

Développement, SQL

Générer un horodatage régulier

Problématique : on veut générer une suite de dates (surtout les heures, minutes et secondes) depuis en SQL pur

-- nombre de secondes
var nbsec number

-- nombre de valeurs souhaitées 
var nbVals number

-- On veut les 15 premiers multiples de 19 secondes
exec :nbsec := 19
exec :nbVals := 15 

-- La requête
select  to_char( trunc(sysdate-1) 
               + ( rownum/ ( 60 *60 *24 ) ) * :nbsec  
               , 'HH24:MI:SS' ) dt 
from dual
connect by rownum &lt;= :nbVals ;

Résutat

DT
--------
00:00:19
00:00:38
00:00:57
00:01:16
00:01:35
00:01:54
00:02:13
00:02:32
00:02:51
00:03:10
00:03:29
00:03:48
00:04:07
00:04:26
00:04:45

15 lignes sélectionnées.

C’était pourtant pas si compliqué.

Métrologie, SQL, Tuning

PCT COST ? Comment 7271 peut-il représenter 0% de 2 ?

Si on effectue une requête SQL et que l’on demande l’affichage de son plan d’exécutio par défaut on obtient le coût individuel de chaque action, ainsi un parcours complet de table ou d’index est associé à un coût global associé à son pourcentage de coût CPU et c’est là que ça se complique un poil. En effet, si on interroge directement les tables ou les vues du dictionnaire la relation en le contenu de la colonne CPU_COST des tables contenant les plans d’exécution rapportée à la colonne COST contenant le coût global de la l’action, on est loin de trouver un rapport quelconque avec le pourcentage affiché.

select * from sys.dual ;

D
-
X

select * from table(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cps942q7r3w9m, child number 0
-------------------------------------
select * from sys.dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

l
  1  select id
  2       , lpad(' ',DEPTH)||OPERATION||' '||OPTIONS operation
  3       , OBJECT_NAME NAME
  4       , CARDINALITY &quot;ROWS&quot;
  5       , BYTES
  6       , COST
  7       , CPU_COST
  8       , IO_COST
  9  from v$sql_plan
 10* where plan_hash_value=272002086
/

        ID OPERATION            NAME  ROWS  BYTES  COST   CPU_COST    IO_COST
---------- -------------------- ---- ----- ------ ----- ---------- ----------
         0 SELECT STATEMENT                           2
         1  TABLE ACCESS FULL   DUAL     1      2     2       7271          2

(suite…)