Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
4 décembre 2013 3 04 /12 /décembre /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é)

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires