11.14.2010

Tracer du Pl/Sql avec DBMS_PROFILER

Même si cette facilité existe depuis Oracle 8i, c'est dernièrement que j'ai découvert qu'on pouvait tracer du code Pl/Sql ligne par ligne avec Oracle 10g. En effet, Oracle a mis à la disposition des développeurs un package DBMS_PROFILER qui permet, entre autre, de comptabiliser le nombre d'occurrences et la durée d'un appel précis dans une procédure/fonction/package.

Les prérequis:
  1. Se connecter en tant que SYS
  2. Exécuter le script $ORACLE_HOME/rdbms/admin/profload.sql
  3. Pour chaque compte/schéma désirant utiliser ce service, exécuter le script $ORACLE_HOME/rdbms/admin/proftab.sql
    *Ce dernier créera des tables pour le profiler dans votre schéma.
Il existe 3 procédures principales pour utiliser le package DBMS_PROFILER:
  • dbms_profiler.start_profiler('Début du code à tracer de la procédure TOTO');
    Marque le début de la section à tracer.
  • dbms_profiler.stop_profiler();
  • Marque la fin de la trace.
  • dbms_profiler.flush_data();
    Enregistre les données recueillies dans les tables créées par le script proftab.sql
Un exemple: on veut déterminer s'il est plus avantageux d'utiliser la fonction modulo pour faire un commit à toutes les 1000 itérations, ou s'il est préférable d'incrémenter un compteur et le remettre à zéro à chaque fois.

-- Création de la procédure qui utilise le modulo
create or replace procedure commit1000_modulo as
i number := 0;
begin
dbms_profiler.start_profiler('Commit au 1000 selon modulo');
for j in 1 .. 500000 loop
i := i + 1;
if mod(i,1000) = 0 then
commit;
end if;
end loop;
dbms_profiler.stop_profiler;
end;
/

-- Création de la procédure qui utilise un compteur en le remettant à zéro
-- à tous les 1000
create or replace procedure commit1000_compteur as
i number := 0;
begin
dbms_profiler.start_profiler('Commit au 1000 selon compteur');
for j in 1 .. 500000 loop
i := i + 1;
if i = 1000 then
commit;
i := 0;
end if;
end loop;
dbms_profiler.stop_profiler;
end;
/

-- Exécution des deux procédures et flush du data dans les tables du profiler
exec commit1000_modulo;
exec commit1000_compteur;
exec dbms_profiler.flush_data;

-- Obtenir la durée d'exécution pour chacune des options
set lines 132
select to_char(runid,'9999') "ID",
to_char(run_date,'yyyy-mm-dd hh24:mi:ss') "EXECUTION",
substr(run_comment,1,40) "DESCRIPTION",
round(run_total_time/1000000000,2) "DURÉE (secs)"
from plsql_profiler_runs
order by 1;

ID EXECUTION DESCRIPTION DURÉE (secs)
----- ------------------- ---------------------------------------- ------------
1 2010-11-14 19:49:53 Commit au 1000 selon modulo ,56
2 2010-11-14 19:49:54 Commit au 1000 selon compteur ,27

On remarque tout de suite que l'utilisation du compteur s'avère plus avantageuse à celle du modulo. Maintenant, regardons en détail:


-- Obtenir le détail de l'exécution pour chacune des options
break on "UNIT NAME" skip 1;
select to_char(a.runid,'9999') "ID",
substr(a.unit_name,1,20) "UNIT NAME",
b.line# "LIGNE",
to_char(b.total_occur,'999999999') "NB OCCUR",
round(b.total_time/1000000000,3) "DURÉE (secs)",
substr(c.text,1,70) "SOURCE"
from plsql_profiler_units a,
plsql_profiler_data b,
user_source c
where a.runid = b.runid
and a.unit_name = c.name
and b.line# = c.line
order by 1,2,3;

ID UNIT NAME LIGNE NB OCCUR DURÉE (secs) SOURCE
----- -------------------- ---------- ---------- ------------ ---------------------------------------------------------------
1 COMMIT1000_MODULO 1 0 0 procedure commit1000_modulo as
1 3 0 0 i number := 0;
1 7 0 0 dbms_profiler.start_profiler('Commit au 1000 selon modulo');
1 9 500001 ,027 for j in 1 .. 500000 loop
1 11 500000 ,063 i := i + 1;
1 13 500000 ,268 if mod(i,1000) = 0 then
1 15 500 ,013 commit;
1 21 1 0 dbms_profiler.stop_profiler;
1 23 0 0 end;

2 COMMIT1000_COMPTEUR 1 0 0 procedure commit1000_compteur as

ID UNIT NAME LIGNE NB OCCUR DURÉE (secs) SOURCE
----- -------------------- ---------- ---------- ------------ ---------------------------------------------------------------
2 COMMIT1000_COMPTEUR 2 0 0 i number := 0;
2 4 0 0 dbms_profiler.start_profiler('Commit au 1000 selon compteur');
2 5 500001 ,023 for j in 1 .. 500000 loop
2 6 500000 ,047 i := i + 1;
2 7 500000 ,03 if i = 1000 then
2 8 500 ,009 commit;
2 9 500 0 i := 0;
2 12 1 0 dbms_profiler.stop_profiler;
2 13 0 0 end;


On remarque que le "if mod(i,1000) = 0 then" du modulo prend 0.268 secondes, alors que l'utilisation du compteur ("if i=1000 then") prend 0.047 secondes.

Donc, l'utilisation du compteur est définitivement plus performante. Ceci n'était qu'un exemple pour vous montrer jusqu'où vous pouvez aller dans votre tuning de code Pl/Sql.

Note sur les plateformes: sur Linux/AIX, la valeur des champs temporels (run_total_time et total_time) est en nanosecondes alors que sur la plateforme Windows, elle est en picosecondes (on divise par 1000 de plus).
nanosecondes (10^-9): run_total_time/1000000000 "Durée (secs)"
picosecondes (10^12): run_total_time/1000000000000 "Durée (secs)"

9.21.2010

Encoder facilement son code Pl/SQL

Comme Oracle le fait pour certains de ses packages (ex: dbms_system), il peut être intéressant d'encrypter la source d'une procédure, fonction ou package (signature et/ou corps). Pour ce faire, Oracle fournit un utilitaire nommé "wrap.exe". On le retrouve dans le répertoire %ORACLE_HOME%\BIN d'une installation du SGBD Oracle ou encore avec le client 10g.

Dans le cas d'un package, une pratique courante est de laisser la signature visible et de ne cacher que son implantation. De plus, il est crucial de toujours conservé la source originale du code puisqu'on ne peut éditer du code encodé. Le fichier encrypté (.plb) ne peut à son tour être décodé.

Son utilisation est simple:
%oracle_home%\bin\wrap iname=code_source_lisible.sql oname=code_source_encrypte.plb
où iname est le fichier en entré (claire) et oname le fichier en sortie (encodé).

Par la suite, il ne reste qu'à exécuter le script dans Sql*Plus:
sql> @code_source_encrypte.plb

À noter que wrap n'encode pas le code d'un trigger. Si vous voulez palier ce problème, simplement appeler une procédure encodé à l'intérieur du trigger.

2.15.2010

Retour sur ma visite du silo CLUMEQ

Tel que mentionné dans mon article précédent, j'ai eu la chance vendredi dernier de visiter le méga ordinateur du consortium CLUMEQ à l'Université Laval. Ce silo est adjacent au pavillon des sciences et de génie (Vachon). Accessible via un des nombreux sous-terrains du pavillon, il y a eu une légère confusion entre moi et mon contact pour notre point de rencontre. Je croyais qu'on entrait par une des portes extérieures. Moi qui a l'habitude d'être à l'heure :S

C'est donc à 11h35 que j'ai débuté ma visite. Au premier étage, on retrouve essentiellement le système de refroidissement et le UPS. Le UPS ne peut maintenir les machines plus de 5-6 minutes. C'est pourquoi lors d'une panne, le UPS averti les nœuds primaires (machines maîtres) d'arrêter tout travail et de se fermer (shutdown). Denis Gauthier, mon contact de la DTI, m'a expliqué que le système de refroidissement aspire la chaleur (elle monte naturellement...) vers le bas à l'aide de gigantesque ventilateurs. Celle-ci réchauffe une conduite d'eau qui sert entre autre à chauffer la piscine du PEPS. Le système de refroidissement me fait penser à un immense radiateur automobile. La majorité des décibels provient de cet endroit.






















Au 2e étage, nous retrouvons le stockage et les switchs InfiniBand de SUN. Celles-ci ont une vitesse de transmission avoisinant les 40 gigabits/s. Depuis l'acquisition de Sun par Oracle, le modèle de switchs utilisé au silo n'est plus vendus directement par Oracle. Voilà pourquoi la documentation se fait rare. Le système de stockage est un Sun Lustre. Actuellement, le système a plus de 500 téraoctets de données et il atteindra bientôt le pétaoctets (1 millions de gigaoctets).

Enfin, au 3e étage, nous retrouvons le coeur essentiel du centre de données soit les unités de traitement. Ce sont des serveurs Sun Blade X6275 ayant des processeurs Intel® Xeon de la série 5500. Tous les serveurs ont la distribution linux CentOS version 5.x comme système d'exploitation. C'est à cet étage que la chaleur est la plus insoutenable. Il faut dire que d'entrer à l'intérieur du coeur de ce méga ordinateur donne des sueurs. Les décibels était également au rendez-vous (92 décibels exactement). J'imagine le technicien faire ses opérations dans le petit bureau avec des caches oreilles et du Gatorade hihi.

Fait étonnant, nous avons vu un mac mini caché sous les planchers du 3e étage. Il est utilisé pour le réseau de sécurité du silo.

2.06.2010

Vérouiller une table en mode exclusif pour effectuer des opérations spéciales

Hier j'ai eu l'occasion d'utiliser le verrouillage de table pour un problème précis en production. Je devais insérer dans une table X et mettre à jour une table Y en lien avec la table X. Je devais également désactiver un trigger d'insertion et un trigger de mise à jour sur cette table X. J'ai fait ceci dans un bloc pl/sql anonyme.

À la première idée, l'analyste et moi nous nous sommes dit que l'opération durerait une seconde et qu'il faudrait manquer de chance pour qu'une autre personne inscrive un enregistrement dans la table X avec les triggers de désactivés. Toutefois, un doute demeurait. Il y avait une chance que cela se produise si petite soit elle. C'est alors que mon collègue DBA m'a parlé d'une instruction appelé "LOCK TABLE" que Oracle nous offre pour une situation du genre. Il y a plusieurs options pour verrouiller une table. Nous avons choisi le mode exclusif (exclusive mode). Dans ce mode, personne ne peut insérer (insert), mettre à jour (update) ou effacer (delete) des enregistrements de cette table. Seule la lecture (select) est permise. Ce mode est le plus restrictif.

Ainsi, la commande que nous avons utilisé à l'intérieur du bloc pl/sql est la suivante:
lock table schema.table_X in exclusive mode;
Lorsque notre traitement est fini et que nous faisons un commit ou un rollback (dans le cas d'une exception), le verrou sur la table se libère automatiquement. De plus, par défaut, l'instruction de verrou attend que la table soit libre pour en prendre possession. Si une autre transaction est en cours, il va attendre pour l'acquérir. De l'autre côté, pendant que la table est verrouillée par mon bloc pl/sql, les autres utilisateurs sont en mode wait sur cette table. Rien n'est perdu. Un principe similaire aux mutex et sémaphores.

Pour plus d'information:
Documentation officielle de Oracle sur la commande
Détail de la commande

1.28.2010

Visite du méga ordinateur de l'Université Laval

Il y a déjà quelques mois, j'ai appris sur un article de Slashdot que l'Université Laval a fait construire un méga ordinateur ou data center dans un ancien accélérateur de particules (Van de Graaf). Le consortium CLUMEQ est derrière ce fabuleux projet. Un projet innovateur auquel des spécialistes de Sun Microsystem ont participé.

En quelques mots: le silo abritant ce data center comporte 3 étages. Cet ordinateur est l'un des plus puissants au Canada. La puissance de calcul sera utilisé principalement pour les chercheurs. 3800 ordinateurs personnels à la fine pointe de la technologie équivalent à ce méga ordinateur. Selon les dires, c'est surtout au point de vue des I/O (opérations entrées/sorties) que cet ordinateur se distingue. Il peut écrire l'équivalent de 4 disques dvd à la seconde. Comme les technologies évoluent très rapidement, on peut affirmer que ce super calculateur se place dans les 75 machines les plus performantes de la planète.

Et ce qui est merveilleux dans toute cette histoire c'est que j'aurai prochainement l'occasion de visiter cette infrastructure. Je vais tenter de prendre quelques clichés et de vous raconter quelques faits surprenants.

Mes items partagés de Google Reader