Autotrace statistics for PL/SQL

04.12.2013

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