Créer un plan d’exécution graphique avec les interfaces de programmation (API) google

Par Olivier JOLYTuning

L’affichage d’un plan d’exécution de manière graphique est toujours appréciable et le web propose pléthores d’API pour le faire. Parmis elles google visualization qui permet entr’autres choses de faire de jolis graphiques et d’afficher de manière simple des organigrammes ou pourquoi pas des plans d’exécution de requêtes SQL.

On peut trouver les plans d’exécution dans diverses tables de la base de donnée. Ici, j’ai choisi d’afficher un plan d’exécution pioché dans la table dba_hist_sql_plan et donc sort d’un historique AWR. Je pré suppose que je connais le SQLID et la valeur de hashage du plan à afficher (plan hash value).

Si on s’intéresse à la brique orgchart de google visualization API on s’apperçoit qu’il suffit de lui donner un tableau javascript contenant au minimum des identifiants uniques dans la première colonne et l’identifiant du pêre dans une deuxième colonne pour que l’arbre s’affiche : de jolies cases bleues contant les identifiants uniques … Ok, ce n’est pas transcendant, mais c’est déjà un premier pas : pas besoin de requête hyérarchique pour afficher une hyérarchie et comble de bonheur le travail est partagé entre la base de donnée et le client

Orgchart la brique que l’on utilise

Comme je le disais plus haut, cette brique s’appuie sur un tableau, les deux premières colonnes décrivent affichage et hyérachie, la troisième peut être utilisée pour renseigner le contenu de bulles contextuelles qui s’afficheront lors du passage de la souris sur une case de notre organigramme / plan d’exécution. Si l’outil n’utilise que ces trois colonnes d’autres peuvent être renseignées pour une utilisation ultérieure. Mais avant de remplir notre tableau, préparons déjà notre page html / javascript

Appeler google vsualization dans une page web

Rien de plus simple, l’appel se situe dans l’en-tête (non affichée) d’une page html :

 <script type="text/javascript" src="http://www.google.com/jsapi"></script>
 <script type="text/javascript">google.load('visualization', '1', {packages: ['orgchart']})</script>

Et c’est tout l’interface de programmation n’a plus qu’à être utilisée.

Afficher le tableau

La commande d’affichage du tableau est une fonction javascript définie dans google visualization

new google.visualization.OrgChart(document.getElementById('visualization')).draw(<b>data</b>, <b>{allowHtml: true, size: 'small'}</b>)

On note ici en gras data, qui est le tableau que nous n’avons pas défini encore et un petit objet json qui liste les options que je souhaite appliquer à mon organigramme / plan d’exécution. La liste complète des options est disponible dans la documentation du module orgchart, seul bémol, c’est en anglais.

Alimenter notre tableau pour afficher un plan d’exécution.

La fameuse documentation décrite quelques citée quelques lignes plus haut nous indique que la première colonne (celle qui pilote l’affichage, souvenons-nous) peut contenir un objet json contenant deux champs :

  1. L’identifiant unique sous la forme v: ‘identifiant unique’
  2. Le texte à affiche sous la forme f: ‘texte à afficher’

En outre, la première ligne du tableau doit contenir les en-têtes de colonnes et j’ai pu constater que la première ligne de valeurs du tableau ne peut pas comporter d’objet json. On crée donc le taleau suivant : 

[[ 'id','parent','bloc']
 ,['1316798873',null,null]
,[{v: '0', f: 'SELECT STATEMENT cvhk2j2gymzhd'},'1316798873','']
,[{v: '1', f: 'NESTED LOOPS '},'0','SEL$1']
,[{v: '2', f: 'NESTED LOOPS '},'1','']
,[{v: '3', f: 'HASH JOIN '},'2','']
,[{v: '4', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.OBJ$</b>)'},'3','SEL$1']
,[{v: '5', f: 'NESTED LOOPS '},'4','']
,[{v: '6', f: 'NESTED LOOPS '},'5','']
,[{v: '7', f: 'NESTED LOOPS '},'6','']
,[{v: '8', f: 'NESTED LOOPS '},'7','']
,[{v: '9', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.USER$</b>)'},'8','SEL$1']
,[{v: '10', f: 'INDEX UNIQUE SCAN<br />(<b>SYS.I_USER1</b>)'},'9','SEL$1']
,[{v: '11', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.OBJ$</b>)'},'8','SEL$1']
,[{v: '12', f: 'INDEX RANGE SCAN<br />(<b>SYS.I_OBJ2</b>)'},'11','SEL$1']
,[{v: '13', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.COL$</b>)'},'7','SEL$1']
,[{v: '14', f: 'INDEX UNIQUE SCAN<br />(<b>SYS.I_COL1</b>)'},'13','SEL$1']
,[{v: '15', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.COLTYPE$</b>)'},'6','SEL$1']
,[{v: '16', f: 'INDEX UNIQUE SCAN<br />(<b>SYS.I_COLTYPE2</b>)'},'15','SEL$1']
,[{v: '17', f: 'INDEX RANGE SCAN<br />(<b>SYS.I_OBJ3</b>)'},'5','SEL$1']
,[{v: '18', f: 'TABLE ACCESS FULL<br />(<b>SYS.ASSOCIATION$</b>)'},'3','SEL$1']
,[{v: '19', f: 'TABLE ACCESS BY INDEX ROWID<br />(<b>SYS.OBJ$</b>)'},'2','SEL$1']
,[{v: '20', f: 'INDEX UNIQUE SCAN<br />(<b>SYS.I_OBJ1</b>)'},'19','SEL$1']
,[{v: '21', f: 'TABLE ACCESS CLUSTER<br />(<b>SYS.USER$</b>)'},'1','SEL$1']
,[{v: '22', f: 'INDEX UNIQUE SCAN<br />(<b>SYS.I_USER#</b>)'},'21','SEL$1']]

Tableau que l’on affecte à la variable data de la manière suivante

var data = google.visualization.arrayToDataTable(<b><TABLEAU></b>);

Et on empaquette tout ça dans une fonction pour que l’affichage se fasse après le chargement de la page (comme il est d’usage en javascript). Fonction que l’on appelle donc qu’au bon moment.

function drawVisualization() {
    var data = google.visualization.arrayToDataTable(<b><TABLEAU></b>);
    new google.visualization.OrgChart(document.getElementById('visualization')).draw(<b>data</b>, <b>{allowHtml: true, size: 'small'}</b>)
}

google.setOnLoadCallback(drawVisualization)

Simplissime …

Générer tout ça en SQL ou plus exactement en PL/SQL

Le script utilisé pour générer le tableau est le suivant :

variable sqlid varchar2(13)
variable phv   number

exec :sqlid:='&1'
exec :phv:=&2

spool plan_&&1._&&2..html

begin
 dbms_output.put_line('[[ ''id'',''parent'',''bloc'']
 ,['''||:PHV||''',null,null]');
 for c in ( select id
                 , operation||' '||options|| case when OBJECT_NAME is not null then '<br />(<b>'||OBJECT_OWNER||'.'||OBJECT_NAME||'</b>)'
                                                  when parent_id is null then :sqlid
                                                  else '' end  txt
				             , parent_id
                 , QBLOCK_NAME qbl
                 , rownum
            from dba_hist_sql_plan
            where sql_id=:sqlid
              and plan_hash_value=:phv )
 loop
  dbms_output.put(' ,[{v: '''||to_char(c.id)||'''');
  dbms_output.put(', f: '''||c.txt||'''}');
  dbms_output.put(','''||nvl(to_char(c.parent_id),:PHV)||'''');
  dbms_output.put_line(','''||c.qbl||''']');
 end loop;
 dbms_output.put_line(']);');
end;
/

Reste à gérer l’enveloppe html la sortie de SQL*Plus et la zone d’affichage, ce qui donne le script complet suivant

--
-- olivier joly 16/06/2013 v1.0.1
-- sql_hist_plan.sql
-- 
-- ce script prend 2 parametres
-- 1 => le sqlid de la requête
-- 2 => le phv de la requête que l'on veut tracer
--

store set settings_sqlplus replace

set serveroutput on
set pages 0
set lines 32767
set trimspool on 
set echo off
set head off
set feed off
set verify off
set timi off
set autot off
set termout off
set markup html off

ttitle off
btitle off

variable sqlid varchar2(13)
variable phv   number

exec :sqlid:='&&1'
exec :phv:=&&2

col dirsep new value dirsep


ho mkdir awr&&1.&&2
ho mkdir -p awr/&&1./&&2

spool awr/&&1./&&2./plan.html
prompt <!DOCTYPE html>
prompt <html xmlns="http://www.w3.org/1999/xhtml">
prompt   <head>
prompt     <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
prompt     <title>Plan::&&1.::&&2.</title>
prompt     <script type="text/javascript" src="http://www.google.com/jsapi"></script>
prompt     <script type="text/javascript">
prompt       google.load('visualization', '1', {packages: ['orgchart', 'table']});
prompt     </script>
prompt     <script type="text/javascript">
prompt     var map;
prompt     var table;
prompt     var data;
prompt 
prompt     function drawOrgChartAndTable() {
prompt       var data = google.visualization.arrayToDataTable([


begin
 sys.dbms_output.put_line('[[ ''id'',''parent'',''bloc'']
 ,['''||:phv||''',null,null]');
 for c in ( select id
                 , operation||' '||options|| case when object_name is not null then '
('||object_owner||'.'||object_name||')'
                                                  when parent_id is null then :sqlid
                                                  else '' end  txt
				             , parent_id
                 , qblock_name qbl
                 , rownum
            from sys.dba_hist_sql_plan
            where sql_id=:sqlid
              and plan_hash_value=:phv )
 loop
  sys.dbms_output.put(' ,[{v: '''||to_char(c.id)||'''');
  sys.dbms_output.put(', f: '''||c.txt||'''}');
  sys.dbms_output.put(','''||nvl(to_char(c.parent_id),:phv)||'''');
  sys.dbms_output.put_line(','''||c.qbl||''']');
 end loop;
 sys.dbms_output.put_line(']);');
end;
/

prompt         new google.visualization.OrgChart(document.getElementById('V&&1.&&2')).
prompt             draw(data, {allowHtml: true});
prompt       }
prompt       google.setOnLoadCallback(drawVisualization);
prompt     </script>
prompt   </head>
prompt   <body style="font-family: Arial;border: 0 none;">
prompt     <div id="V&&1.&&2"></div>
prompt   </body>
prompt </html>

spool off

undef 1
undef 2

@settings_sqlplus

Ce qui donne :

plan d'exécution graphique

Plan plus classique

Depuis la version 10g plus besoin de requête hyérarchique pour effectuer un affichage sympathiqe du plan d’exécution. Les tables qui peuvent stocker des plan d’exécution (sys.dba_hist_sql_plan certes mais aussi plan_table ou v$sql_plan) contiennent la colonne DEPTH qui permet l’indentation pour créer vos propres requêtes de lecture en mode texte. L’utilisation du package dbms_xplan restant encore la manière le plus simple d’exploiter ces tables.