Mercredi 15 janvier 2014 3 15 /01 /Jan /2014 11:21
Nouvelle année, nouvelles résolutions.
La première : coder un échiquier pour jouer sous SQL*Plus.

J'ai posté le script complet ici : 

1) Le modèle de données

Le modèle de données est simple : c'est la liste des coups joués, ni plus ni moins.

CREATE TABLE cs_mouv (
    x number, 
    y number,
    x_dest number, 
    y_dest number,
    n_mouv number, 
    piece char(1),
    col char(1), 
    move_str varchar2(10),
    fic char(1) default 'N'
);

Pour cela : 
- on enregistre la case de départ (x, y), la case d'arrivée (x_dest, y_dest), la pièce et sa couleur
- le numéro du coup (n_mouv)
- Le flag "fic", comme fictif, permet de décomposer les coups spéciaux en plusieurs mouvements.
Comme le roque, la prise en passant, la promotion...
- Le "move_str" ne sert qu'à conserver la châne saisie par l'utilisateur, pour des raisons pratiques.

2) Reconstituer la position à partir de la liste des coups

Quand on a la liste des coups, et si on n'est pas complètement stupide, on peut en déduire la position.

CREATE OR REPLACE VIEW pieces AS
SELECT x_dest x, y_dest y, max(piece) keep(dense_rank last order by n_mouv) piece
    , max(col) keep(dense_rank last order by n_mouv) col 
FROM (
    SELECT x_dest, y_dest, n_mouv
         , piece 
         , col
    FROM cs_mouv
    WHERE x_dest IS NOT NULL
    UNION ALL
    SELECT x, y, n_mouv
         , null 
         , null
    FROM cs_mouv
    )
GROUP BY x_dest, y_dest
/

Il s'agit là de se baser sur les cases de l'échiquier, et de chercher quelle est la pièce qui s'y situe actuellement.
La première partie de l'UNION ALL liste les pièces arrivant sur une case donnée, la deuxième partie les cases abandonnées... on retient dans tout ça la pièces correspondant au plus récent numéro de coup pour chaque case.

3) Afficher la position

La procédure pkg_csboard.display affiche la position actuelle. La vue "pieces" donnant déjà toutes les positions, il s'agit juste de metre en forme le résultat.
Le paramètre en entrée permet d'orienter l'affichage (noirs ou blancs) par l'inversion du système de coordonnées.

A noter que par défaut, SQL*Plus justifie les "leading spaces" (les premiers espaces à gauche), ce qui fout en l'air les efforts de mise en forme.
Pour empêcher cela, il faut entrer l'option :

SET SERVEROUTPUT ON FORMAT WRAPPED

4) Initialiser l'échiquier

La procécdure pkg_csboard.init supprime la liste des coups, puis insère toutes les pièces dans leur position initiale.


cs1-copie-1.jpg
 

5) Jouer des coups

La procédure cs_move prend en paramètre la chaîne de caractères représentant la position initiale et la destination de la pièce à déplacer, contrôle la validité du coup, l'exécute, puis affiche la position : 

SQL> exec pkg_csboard.csmove('e2e4');

cs1-copie-2.jpg

La sous-procédure parse_move vérifie la validité du coup, et retourne le type de coup effectué (erreur, coup normal, petit roque, ...). J'ai pas eu la motivation d'implémenter la prise en passant et la promotion, mais ça doit pas être bien compliqué.
La sous-procédure exec_move est appelée ensuite pour effectuer l'insertion effective.

SQL> exec pkg_csboard.csmove('oo');

cs1-copie-4.jpg
6) Afficher la liste des coups.

C'est la procédure display_list. J'aurais pu m'amuser à mettre des tirets, mais j'avais la flemme. 

SQL> exec pkg_csboard.display_list;
  1. e2e4    e7e5
  2. g1f3    b8c6
  3. f1c4    g8f6
  4. oo
  
7) Annuler des coups

Comme on a la liste des coups, ce n'est pas bien compliqué non plus de revenir en arrière...
La procédure cancel_move supprime le nombre de coups passés en paramètres, en rassemblant au préalable les coups fictifs avec leur coup réel associé.

SQL> exec pkg_csboard.cancel_move(2);

cs1-copie-5.jpg

Voilà, il resterait encore plein de choses à faire :
- implémenter les coups spéciaux manquants
- vérifier la validité des coups
- insérer le concept de partie
- utiliser si possibles des queues pour que deux personnes puissent jouer "sur le réseau"
- encapsuler ça dans du shell pour pouvoir faire des boucles
- ...

Mais je préfère faire les choses à moitié, c'est mieux.
Par Pacman - Publié dans : SQL cosmétique
Ecrire un commentaire - Voir les 1 commentaires
Mardi 10 décembre 2013 2 10 /12 /Déc /2013 16:38

Dans l'exemple type des row generators (par exemple ici), on utilise parfois une construction étrange qui est "CONNECT BY level <= N".


C'est certes étrange et abusé, mais ça se conçoit : "connecte moi avec n'importe quoi et récursivement, mais pas plus loin que le 3ème étage".

 

Cela dit, il y a des moments où on veut générer N lignes pour chaque lignes appartenant à une sélection (de plus d'une ligne cette fois).

 

CREATE TABLE test_rowgen AS                

SELECT 'pacmann' c, 3 n FROM DUAL UNION ALL

SELECT 'robert' c, 2 n FROM DUAL UNION ALL 

SELECT 'squelettor' c, 1 n FROM DUAL       

 

Par exemple ici, on voudrait générer 3 lignes pacmann, deux lignes robert, et 1 ligne squelettor.

 

Et là, si on tente : 

 

SELECT *             

FROM test_rowgen     

CONNECT BY level <= n

 

... on obtient toutes les combinaisons possibles récursivement, tant que le niveau est inférieur à "n", ce qui n'est pas bon. La différence avec le générateur à partir de dual, c'est que la ligne de dual ne peut se connecter qu'avec elle-même, alors que là, on a une tripotée d'autres arbres distincts à générer combinatoirement.

 

Si, pour empêcher de connecter n'importe quoi, on tente de lui préciser qu'il doit se connecter qu'avec lui-même... 

 

SELECT c, n          

FROM test_rowgen     

CONNECT BY level <= n

    AND c = prior c  

 

... on obtient : 

ORA-01436: CONNECT BY loop in user data

 

La boucle est détectée au niveau de l'analyse de la requête, et non au niveau de l'exécution (pour preuve la première requête, une autre preuve un peu plus loin dans cet article)

 

Si on ajoute NOCYCLE, on ne va pas bien loin : 

 

SELECT c, n                  

FROM test_rowgen             

CONNECT BY NOCYCLE level <= n

    AND c = prior c          

 

C                   N

---------- ----------

pacmann             3

robert              2

squelettor          1

 

Bien entendu, NOCYCLE évite l'erreur, mais pour autant, lorsque le cycle est détecté, aucune connexion n'est tentée pour les chemins suspectés d'induire des cycles.

 

Pour cela, des petits génies (dont Laurent Schneider, mais lui seul pourra nous dire s'il l'avait repompé ailleurs à l'époque :)) ont hacké le système et ont tenté : 

 

SELECT c, n                         

FROM test_rowgen                    

CONNECT BY level <= n               

    AND c = prior c                 

    AND prior sys_guid() IS NOT NULL

 

C                   N

---------- ----------

pacmann             3

pacmann             3

pacmann             3

robert              2

robert              2

squelettor          1

 

Alors oui, ça marche... mais qu'est ce que ça veut dire ?

 

Testons de réécrire un peu la requête : 

WITH u AS (                  

    SELECT c, n, sys_guid() t

    FROM test_rowgen         

    )                        

SELECT *                     

FROM  u                      

CONNECT BY level <= n        

    AND c = prior c          

    AND prior t IS NOT NULL  

 

C                   N T                               

---------- ---------- --------------------------------

pacmann             3 ED2ED377E71B437AE0436203650A0428

pacmann             3 ED2ED377E71D437AE0436203650A0428

pacmann             3 ED2ED377E71F437AE0436203650A0428

robert              2 ED2ED377E721437AE0436203650A0428

robert              2 ED2ED377E723437AE0436203650A0428

squelettor          1 ED2ED377E725437AE0436203650A0428

 

Ca marche !

Mais si on force la matérialisation : 

 

WITH u AS (                                  

    SELECT /*+materialize*/c, n, sys_guid() t

    FROM test_rowgen                         

    )                                        

SELECT *                                     

FROM  u                                      

CONNECT BY level <= n                        

    AND c = prior c                          

    AND prior t IS NOT NULL                  

 

... Paf !

ORA-01436: CONNECT BY loop in user data

 

Et moi, les résultats qui varient en fonction du plan d'exécution... hum. (Je dis ça, mais faudrait encore vérifier dans quel ordre les choses se passent...)

 

Par ailleurs la condition "prior sys_guid() is not null" n'empêche en rien les loop...

 

SELECT c, n                                           

FROM test_rowgen                                 

CONNECT BY prior sys_guid() IS NOT NULL

 

... boucle infinie tuée à la main après plusieurs millions de lignes fetched.

 

Donc voilà, cette astuce est démoniaque et plutôt dangereuse.

 

Je propose donc une méthode encore plus nulle, parce que je le vaux bien : 

 

SELECT c,n                                                                     

FROM (                                                                         

    SELECT c, n, coalesce(sum(n-1)                                             

    over(order by c desc rows between unbounded preceding and 1 preceding),0) p

    FROM test_rowgen                                                           

    )                                                                          

CONNECT BY rownum <= n + p                                                     

 

C                   N

---------- ----------

squelettor          1

robert              2

pacmann             3

pacmann             3

robert              2

pacmann             3

 

Pourquoi est-ce que ça marche ?

Parce qu'au moment de connecter, on se base sur rownum pour estimer le nombre de connexion qui ont été faites sur les lignes précédentes, et en déduire combien il faut en faire la ligne présente.


Pourquoi est-ce que c'est lamentable et à ne surtout pas refaire ?

Parce que d'une part, je n'ai pas la moindre idée de ce qu'est sensé être l'exécution de CONNECT BY, et (surtout ?) parce qu'on ne programme pas en fonction de l'algorithme interne d'Oracle, qui peut très bien changer d'un patch à l'autre (l'introduction du hash group by en étant certainement le plus bel exemple)

 

Comme dans tout article nihiliste, il faut finir par la clause MODEL (qui marche, n'est pas officiellement prohibée, mais vous mettra à dos vos collègues et votre hiérarchie) : 

 

SELECT c, n                                     

FROM test_rowgen                                

MODEL                                           

PARTITION BY (c c0)                             

DIMENSION BY (1 i)                              

MEASURES (c,n)                                  

RULES (                                         

    c[FOR i FROM 1 TO n[1] INCREMENT 1] = c[1]  

    , n[FOR i FROM 1 TO n[1] INCREMENT 1] = n[1]

    )                                           

 

C                   N

---------- ----------

robert              2

squelettor          1

robert              2

pacmann             3

pacmann             3

pacmann             3

 

C'est bien la première fois que je trouve MODEL moins obscure qu'une quelconque autre méthode...

Par Pacman - Publié dans : SQL
Ecrire un commentaire - Voir les 0 commentaires
Jeudi 5 décembre 2013 4 05 /12 /Déc /2013 10:53

Pour faire suite à mon article précédent, je vais quand même lâcher une solution viable, spécifique à Oracle : 

 

SELECT ville_etp                                                                

FROM t_entrepot                                                                 

WHERE rayon_ryn IN (SELECT rayon_ryn                                            

                    FROM t_rayon)                                               

GROUP BY ville_etp                                                              

HAVING collect(DISTINCT rayon_ryn) = (SELECT collect(rayon_ryn) FROM t_rayon)   

 

VILLE_ETP

---------

MARSEILLE

TOULOUSE 

 

Collect, à partir de 10g, permet de créer un collection par agrégation. 

Dans une collection, il n'y a pas de problématique d'ordre, par contre il peut bien y avoir des doublons (d'où l'ajout du DISTINCT).


Dans cette requête, on vérifie donc que la collection des rayons proposés par un entrepot (en limitant la liste de ces rayons à ceux présent dans la table t_rayon), correspond exactement à la liste des rayons à fournir.

 

Si on veut la correspondance exacte entre les rayons de t_entrepot et ceux de t_rayon, on peut virer la sous-requête IN.

Par Pacman - Publié dans : SQL
Ecrire un commentaire - Voir les 4 commentaires
Mercredi 4 décembre 2013 3 04 /12 /Déc /2013 16:49

Ma grand-mère me disait toujours, "Pacman, n'oublie jamais le côté artistique du SQL, ni le côté obscur de la force".

Et là, force est de constater que depuis un moment, je m'y crois trop : j'ai oublié les valeurs essentielles dans lesquelles mes ancêtres et moi croyons, et dans lesquelles ma propre descendance devra croire : l'absurdité, l'inutilité, la créativité non applicable à la vraie vie.

 

On va donc aborder aujourd'hui : la division relationnelle avec l'opérateur BITAND !

 

Pour un vrai article sérieux voire boursoufflé sur la division relationnelle, cherchez sur le net.

Par exemple, l'article de SQLPro (vous savez, le mec qui, quand vous lui posez une question sur le forum developpez.net, commence par vous insulter, puis vous recommande d'acheter ses bouquins...), dont nous allons d'ailleurs reprendre le script de tests : 

http://sqlpro.developpez.com/cours/divrelationnelle/

 

1) La division relationnelle

 

Il s'agit de repérer dans une table un ensemble de ligne. 

C'est comme faire un filtre where, mais sur tout un groupe dont les lignes doivent matcher, partiellement ou complètement, un certain nombre de valeurs.

Ces valeurs cibles étant par exemple répertoriées dans une autre table.

 

2) Jeu de données

 

Comme dit, c'est repompé de chez SQLPro. Il s'agit de dépôts (identifiés par leur ville) qui servent des rayons.

Lesquels sont capables de déservir tous les rayons ?

 

/* table des rayons */                                                          

CREATE TABLE T_RAYON                                                            

(RAYON_RYN  CHAR(16));                                                          

/* tables des entrepôts */                                                      

CREATE TABLE T_ENTREPOT                                                         

(VILLE_ETP  CHAR(16),                                                           

RAYON_RYN  CHAR(16));                                                           

/* alimentation de la table des rayons */                                       

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('frais');                               

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('boisson');                             

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('conserve');                            

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('droguerie');                           

/* alimentation de la table des entrepots */                                    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'conserve');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'boisson');       

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'conserve');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'droguerie');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boisson');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'frais');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'conserve'); 

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'droguerie');

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'droguerie');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'boisson');   

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'frais');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'conserve');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'droguerie'); 

/* modif de l'état initial */                                                   

INSERT INTO T_RAYON VALUES ('conserve');                                        

 

3) La méthode qui perd

 

La représentation binaire, c'est comme la représentation "usuelle" décimale, sauf qu'au lieu d'avoir des nombres composés de chiffres de 0 à 9, on a des nombres composés de 0 et de 1.


Par exemple, si on prend le nombre décimal 153, c'est : 

- En décimal 1 * 10^2 + 5 * 10^1 + 3 * 10^0 = 153 [decimal]

- En binaire 1 * 2^7 + 0 * 2^6 + 0 * 2^5 + 1 * 2^4 + 1 * 2^2 + 1 * 2^0 = 100111 [binaire]

 

Pour deux nombres donnés n1 et n2, bitand(n1, n2) est le nombre résultant de l'opération "pour chaque position, si le chiffre correspondant dans n1 et dans n2 vaut 1, on renvoie 1, sinon 0"

Par exemple, 

bitand(153 [décimal], 15 [décimal])

= bitand (100111[binaire], 001111[binaire])

= 000111 [décimal]

= 7

 

Cette représentation colle parfaitement à une liste de flags : si on affecte à : 

- frais la valeur 1

- boisson la valeur 2

- conserve la valeur 4

- droguerie la valeur 8

 

... et qu'on fait la somme, on obtient 15, qui s'écrit 1111 en binaire.

 

Si ensuite on somme ces valeurs pour chaque dépôt, on pourra chercher quels sont les dépôt de somme n2, tels que bitand(n2, 15) = 15.

 

En SQL, ça donne : 

 

WITH t AS (                                                                       

SELECT distinct rayon_ryn, power(2, dense_rank() over(order by rayon_ryn) - 1) flg

FROM t_rayon)                                                                     

, u as (                                                                          

SELECT sum(flg) sumflg                                                            

FROM t)                                                                           

SELECT ville_etp                                                                  

FROM t_entrepot a                                                                 

  JOIN t ON a.rayon_ryn = t.rayon_ryn                                             

  CROSS JOIN U                                                                    

GROUP BY ville_etp, sumflg                                                        

HAVING bitand(sum(distinct t.flg), sumflg) = sumflg                               

 

Voilà voilà, ne pas appliquer ce genre de méthode au boulot, bien entendu...

 

Par Pacman - Publié dans : SQL cosmétique
Ecrire un commentaire - Voir les 2 commentaires
Mercredi 4 décembre 2013 3 04 /12 /Déc /2013 15:02

L'un des moyens d'étudier l'exécution d'une requête SQL, c'est l'autotrace.

L'autotrace s'appuie sur les statistiques mesurées sur la session, pour en sortir un différentiel des grandeurs en question avant / après exécution de la requête.

 

set autot traceonly statistics

SELECT count(*)               

FROM all_objects              

 

Statistics                                                

----------------------------------------------------------

          8  recursive calls                              

          0  db block gets                                

     102739  consistent gets                              

        140  physical reads                               

          0  redo size                                    

        337  bytes sent via SQL*Net to client             

        350  bytes received via SQL*Net from client       

          2  SQL*Net roundtrips to/from client            

        842  sorts (memory)                               

          0  sorts (disk)                                 

          1  rows processed                               

 

Mais ça ne marche pas sur l'exécution d'un bloc ou procédure PL/SQL : 

 

DECLARE            

  i number;        

BEGIN              

  SELECT count(*)  

  INTO i           

  FROM all_objects;

END;               

/                  

 

PL/SQL procedure successfully completed.

 

Il faut donc faire le boulot soit-même !

 

1) Le modèle de données : 

 

A. Le vue v$statname donne la liste des grandeurs mesurées : 

 

STATISTIC# : identifiant de la grandeur

NAME : nom de la statistique

CLASS : identifiant de classe

STAT_ID : identifiant numérique

 

Parmi toutes les grandeurs mesurées, on retrouve celles de l'autotrace (à l'exception du 'rows processed') : 

 

SELECT *                                                  

FROM v$statname                                           

WHERE name IN ( 'recursive calls',                        

                'db block gets',                          

                'consistent gets',                        

                'physical reads',                         

                'redo size',                              

                'bytes sent via SQL*Net to client',       

                'bytes received via SQL*Net from client', 

                'SQL*Net roundtrips to/from client',      

                'sorts (memory)',                         

                'sorts (disk)',                           

                'rows processed');                        

 

STATISTIC# NAME                                    CLASS    STAT_ID

---------- --------------------------------------------- ----------

         7 recursive calls                             1 2656001462

        47 db block gets                               8 1480709069

        50 consistent gets                             8 4162191256

        54 physical reads                              8 2263124246

       134 redo size                                   2 1236385760

       343 bytes sent via SQL*Net to client            1 2967415760

       344 bytes received via SQL*Net from client      1  161936656

       345 SQL*Net roundtrips to/from client           1  159846526

       349 sorts (memory)                             64 2091983730

       350 sorts (disk)                               64 2533123502

 

B. La colonne CLASS quant à elle corresond à une liste des classes suivantes : 

 

1 - User

2 - Redo

4 - Enqueue

8 - Cache

16 - OS

32 - Real Application Clusters

64 - SQL

128 - Debug

 

Comment ça une liste dans une seule colonne, le modèle ne serait-il pas normalisé ??

 

WITH classname AS (                                       

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL     

SELECT 2, 'Redo' FROM DUAL UNION ALL                      

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                   

SELECT 8, 'Cache' FROM DUAL UNION ALL                     

SELECT 16, 'OS' FROM DUAL UNION ALL                       

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL

SELECT 64, 'SQL' FROM DUAL UNION ALL                      

SELECT 128, 'Debug' FROM DUAL                             

)                                                         

SELECT *                                                  

FROM v$statname                                           

WHERE class not in (SELECT class                          

                    FROM classname)                       

 

STATISTIC# NAME                                 CLASS    STAT_ID

---------- ------------------------------------------- ---------

        80 DBWR fusion writes                   40 2313150541   

       146 gc cr blocks served                  40 1075941831   

...                                                             

       328 buffer is pinned count               72 1300470380   

       329 buffer is not pinned count           72 2670431739   

       330 no buffer to keep pinned count       72 3528454686   

 

Les classes 40 et 72 représentes respectivement 32 + 8 et 64 + 8... c'est de l'information aggrégée codée en binaire !

Pour la jointure avec les noms de classe, il suffit donc d'utiliser BITAND :

 

WITH classname AS (                                         

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL       

SELECT 2, 'Redo' FROM DUAL UNION ALL                        

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                     

SELECT 8, 'Cache' FROM DUAL UNION ALL                       

SELECT 16, 'OS' FROM DUAL UNION ALL                         

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL  

SELECT 64, 'SQL' FROM DUAL UNION ALL                        

SELECT 128, 'Debug' FROM DUAL                               

)                                                           

SELECT name, (SELECT wm_concat(class_name)                  

             FROM classname b                               

             WHERE bitand(a.class, b.class) > 0) class_names

FROM v$statname a                                           

WHERE class IN (40,72)                                      

 

NAME                                  CLASS_NAMES                    

------------------------------------- -------------------------------

DBWR fusion writes                    Cache,Real Application Clusters

gc cr blocks served                   Cache,Real Application Clusters

...                                                                  

buffer is pinned count                Cache,SQL                      

...                                                                  

 

Au passage, la représentation binaire + BITAND est un moyen rigolo, illisible et sous performant de faire des opérations ensemblistes, jusqu'à la division relationnelle... mais passons !

 

C. Les valeurs effectivement mesurée sont disponibles via la vue v$mystat (v$sesstat représentant l'ensemble des mesures pour toutes les sessions)


SID : le SID de la session concernée (toujours le même dans v$mystat)

STATISTIC# : le numéro de statistique, pointant sur la colonne homonyme de la table v$statname

VALUE : la valeur mesurée

 

2) Consolider des snapshots : runstats_pkg

 

L'idée est maintenant de prendre une photo de la valeur des statistiques avant exécution, d'en reprendre une autre après, et d'afficher la différence.

Pour cela, reprenons le travail de l'oncle Tom et son package runstats_pkg : 

 

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

 

.. qui s'appuie sur une vue qui lie les vues v$statname et v$mystat, et d'un GTT qui permet de stocker les photos de statistiques.

 

Ce package par contre, est construit pour comparer deux exécutions différentes : 

rs_start : prise de photo initiale

rs_middle : prise de photo après la première exécution

rs_stop : prise de photo après la deuxième exécution et comparaison des deux photos (ou plutôt des différences entre photo 2 et 1, et photo 3 et 2)

 

Pour mesurer les stats sur une seule exécution, on va donc juste ajouter une procédure de fin supplémentaire rs_stop_single : 

 

create or replace package runstats_pkg                              

as                                                                  

    procedure rs_start;                                             

    procedure rs_middle;                                            

    procedure rs_stop( p_difference_threshold in number default 0 );

    procedure rs_stop_single;                                       

end;                                                                

/                                                                   

 

create or replace package body runstats_pkg                         

as                                                                  

[...]                                                               

 

procedure rs_stop_single is                                         

begin                                                               

    rs_middle;                                                      

    dbms_output.put_line                                            

    ( 'Bloc ran in ' || g_run1 || ' cpu hsecs' );                   

 

    dbms_output.put_line( chr(9) );                                 

 

    dbms_output.put_line                                            

    ( rpad( 'Name', 30 ) || lpad( 'Value', 12 ));                   

 

    for x in                                                        

    ( select rpad( replace(a.name,'STAT...'), 30 ) ||               

             to_char( b.value-a.value, '999,999,999' ) data         

        from run_stats a                                            

          join run_stats b on a.name = b.name                       

       where a.runid = 'before'                                     

         and b.runid = 'after 1'                                    

         and replace(a.name, 'STAT...') in ( 'recursive calls',     

                'db block gets',                                    

                'consistent gets',                                  

                'physical reads',                                   

                'redo size',                                        

                'bytes sent via SQL*Net to client',                 

                'bytes received via SQL*Net from client',           

                'SQL*Net roundtrips to/from client',                

                'sorts (memory)',                                   

                'sorts (disk)',                                     

                'rows processed')                                   

    ) loop                                                          

        dbms_output.put_line( x.data );                             

    end loop;                                                       

end;                                                                

[...]                                                               

 

 

Cette procédure est copiée - collée - légèrement modifiée de la procédure rs_stop (au lieu de faire la différence sur 2 runs, simplement afficher les résultas de l'unique run et nettoyer ce qui ne sert à rien).

Allez, on teste : 

 

 

exec runstats_pkg.rs_start;

 

DECLARE                    

  i number;                

BEGIN                      

  SELECT count(*)          

  INTO i                   

  FROM all_objects;        

END;                       

/                          

 

PL/SQL procedure successfully completed.

 

exec runstats_pkg.rs_stop_single;

 

Bloc ran in 46 cpu hsecs

 

Name                          Value

bytes sent via SQL*Net          592

bytes received via SQL*       1,005

SQL*Net roundtrips to/f           4

sorts (memory)                1,344

sorts (disk)                      0

recursive calls                   4

db block gets                    20

consistent gets             112,153

physical reads                    0

redo size                     3,036

 

PL/SQL procedure successfully completed.

 

Voilà !

Bien entendu, une fois que ces résultats sont consolidés dans la GTT, on peut chercher n'importe quelle valeur parmi la tripotée de statistiques mesurées...

 

Et de manière plus générale, le package peut être retouché à souhait. 

Pour ma part, je n'aime pas trop le rs_stop qui affiche les différences selon un seuil exprimé en valeurs absolues... (sachant qu'on mesure des statistiques qui n'ont pas même unité)

Par Pacman - Publié dans : SGBD
Ecrire un commentaire - Voir les 0 commentaires

Catégories

Derniers Commentaires

Recherche

Calendrier

Juillet 2014
L M M J V S D
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
<< < > >>

Présentation

  • : Le blog de Pacman
  • Le blog de Pacman
  • : Yet Another Stupid Oracle Blog
  • Contact
Créer un blog gratuit sur over-blog.com - Contact - C.G.U. - Rémunération en droits d'auteur - Signaler un abus - Articles les plus commentés