Comprendre la cartographie mémoire du « Buffer Cache » (2ème partie)

La première partie de cet article nous a permis de comprendre les mécanismes du « Buffer Cache » et de disposer de quelques requêtes simples pour afficher le contenu de celui-ci.
Dans cette seconde partie, nous allons décrire quelques colonnes de la vue « X$BH », source d’informations interne du contenu du cache de données, puis nous allons utiliser une requête SQL afin d’extraire les informations et les consulter à partir d’Excel PowerPivot, obtenant ainsi un rapport d’analyse graphique à plusieurs dimensions.
PowerPivot est un outil extrêmement puissant permettant d’extraire un volume important de données et d’en faire des analyses très fines. A noter que dans cet article, nous utilisons la version Excel 2013 car celle-ci intègre PowerPivot de manière native. Si toutefois, vous disposez d’une version 2010, vous pouvez installer PowerPivot manuellement.

Description des colonnes de la vue X$BH utilisées dans la requête

  • BLSIZ : Taille du bloc,
  • FLAG : Cette colonne est renseignée par une valeur qu’il faut décrypter en utilisant la fonction BITAND. Cette fonction réalise un « et » binaire sur la colonne avec un masque passé en argument. Ci-joint les valeurs de masque utilisées dans la requête :
    • 1 : Dirty (bloc modifié),
    • 524288 : provenant d’un accès complet à l’objet (Table Access Full ou Index Full Scan),
  • OBJ : Référence de l’objet (table, index, …),
  • STATUS : Statut du bloc du cache de données :
    • free : Bloc non utilisé, peut être réalloué à une autre zone de la SGA
    • xcur : Bloc présent en mode de verrouillage exclusif pour l’instance courante,
    • scur : Bloc partagé par plusieurs instances,
    • cr : Bloc en mode de lecture consistante, c’est un bloc « clone » d’un bloc au statut « xcur »,
    • read : bloc en cours de lecture à partir des disques,
  • TS# :  Référence du tablespace

 

Lancement d’Excel 2013 et création d’une requête

A partie d’Excel, vous pouvez sélectionner l’onglet « POWERPIVOT« . Celui-ci affiche alors un sous-menu et il convient de sélectionner l’icône « Gérer » :
EXCEL - GERER
Puis dans le sous-menu, vous pouvez sélectionner « A partir d’autres sources« , dans la fenêtre des bases de données, il convient d’utiliser « Oracle » comme source de données :
EXCEL - DATASOURCE
Par la suite, il vous faut saisir les informations de connexion et d’authentification à la base de données, cliquer sur le bouton « Tester la connexion« . En théorie, vous devriez avoir le message de réussite de la connexion :
EXCEL - CONNEXION
Il est possible de sélectionner la source de données sous forme d’objets tels que des vues ou des tables, ou bien de saisir l’ordre SQL lui-même. Vous pouvez écrire une requête SQL comme nous vous le proposons dans l’exemple suivant afin d’extraire les données liées aux « Buffer Cache » présents en mémoire :

  SELECT bh.inst_id INSTANCE_ID,
         pd.bp_name BUFFER_POOL,
         ts.name TABLESPACE,
         DECODE (bh.state,0, 'free',1,'xcur',2,'scur',3,'cr',4,'read','Other') STATE,
         DECODE (ob.name, NULL, TO_CHAR (bh.obj), us.name) USER_NAME,
         DECODE (ob.name, NULL, TO_CHAR (bh.obj), us.name || '.' || ob.name) OBJECT_NAME,
         DECODE (ob.type#,1,'INDEX',2,'TABLE',19,'TABPART',20,'IDXPART',21,'LOB',42,'MVIEW','Other') TYPE_OBJ,
         SUM (DECODE (BITAND (bh.flag, 1), 1, 0, 1)) CLEAN_BLOCKS,
         SUM (DECODE (BITAND (bh.flag, 1), 1, 1, 0)) DIRTY_BLOCKS,
         DECODE (BITAND (bh.flag, 524288), 0, 'N', 'Y') FULL,
         ROUND ( (bh.blsiz * (COUNT (*))) / 1048576, 3) SIZE_MB,
         COUNT (*) TOTAL_BLOCKS
    FROM x$bh bh,
         sys.obj$ ob,
         sys.user$ us,
         sys.ts$ ts,
         x$kcbwds ds,
         x$kcbwbpd pd
   WHERE     bh.inst_id = USERENV ('INSTANCE')
         AND bh.obj = ob.dataobj#(+)
         AND ob.owner# = us.user#(+)
         AND ob.owner# > 5
         AND bh.ts# = ts.ts#
         AND ds.set_id >= pd.bp_lo_sid
         AND ds.set_id <= pd.bp_hi_sid
         AND pd.bp_size != 0
         AND ds.addr = bh.set_ds
GROUP BY bh.inst_id,
         pd.bp_name,
         bh.blsiz,
         ts.name,
         DECODE (bh.state,0, 'free',1,'xcur',2,'scur',3,'cr',4,'read','Other'),
         DECODE (ob.name, NULL, TO_CHAR (bh.obj), us.name),
         DECODE (ob.name, NULL, TO_CHAR (bh.obj), us.name || '.' || ob.name),
         DECODE (ob.type#,1,'INDEX',2,'TABLE',19,'TABPART',20,'IDXPART',21,'LOB',42,'MVIEW','Other'),
         DECODE (BITAND (bh.flag, 1), 0, 'N', 'Y'),
         DECODE (BITAND (bh.flag, 524288), 0, 'N', 'Y')

L’ajout d’un tableau croisé se fait par l’icône « Tableau Croisé dynamique« , puis sélectionner le choix « Graphique croisé dynamique » :EXCEL - TABLEAU
Enfin, il est très facile de fabriquer le graphe désiré avec toutes les fonctionnalités de PowerPivot. Toutes les colonnes de la requête deviennent alors des sources d’analyse très précises :
 
EXCEL - ANALYSE

Exemple d’affichage des données sous forme de graphes

Il est possible par exemple pour l’analyse des « buffer cache », de visualiser la taille mémoire des blocs de données par schéma et par allocation dans les pools de données :
XBH - Image1
Ou encore de visualiser la taille mémoire des blocs de données par type d’objet et par mode d’accès (« table access full » ou bien « fast full scan ») :
XBH - Image2
Si vous souhaitez que je complète cet article sur l’utilisation poussée de PowerPivot, n’hésitez pas à me le faire savoir et à bientôt sur notre Blog Easyteam.

1 réflexion sur “Comprendre la cartographie mémoire du « Buffer Cache » (2ème partie)”

  1. c’est un intéressant article Merci bcq pour changer la connaissance.

Les commentaires sont fermés.