Utilisation d'un "resumable timeout" dans nos scripts.

N'est-ce pas enrageant d'avoir un rebuild d'index qui échoue après 30 minutes car il a manqué d'espace disque dans un tablespace? Si vous êtes comme moi, ça vous enrage! Il y a 2 solutions possibles à ce genre de problème: être très rigoureux et toujours calculer/vérifier ses espaces disques (tablespaces, undo, temp) avant les opérations ou encore utiliser un "resumable timeout" pour nous soutenir.

Avec le "resumable timeout", vous pouvez, au niveau de la session ou de la base de données, déterminer le nombre de secondes à attendre avant que le script/programme ne sorte en erreur si aucune action n'est faite pour corriger le problème. Vous serez averti dans l'alert log d'Oracle du manque d'espace pour un tablespace particulier. Ainsi, à la lecture du message, vous agrandissez le tablespace et quelques secondes plus tard, votre script/programme continuera son exécution.

L'utilisateur doit pouvoir exécuter le package dbms_resumable:
grant execute on dbms_resumable to ORACLE;

Enuite, dans sa session, on modifie le paramètre pour avoir un délais de une heure (3600 secondes) et on donne un nom à ce timeout:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'TEST_ORACLE';

On peut voir les détails dans la vue DBA_RESUMABLE:

20:12:47 ORACLE@orcl > desc dba_resumable
Nom                                                   NULL ?   Type
----------------------------------------------------- -------- ----------------
USER_ID                                                        NUMBER
SESSION_ID                                                     NUMBER
INSTANCE_ID                                                    NUMBER
COORD_INSTANCE_ID                                              NUMBER
COORD_SESSION_ID                                               NUMBER
STATUS                                                         VARCHAR2(9)
TIMEOUT                                                        NUMBER
START_TIME                                                     VARCHAR2(20)
SUSPEND_TIME                                                   VARCHAR2(20)
RESUME_TIME                                                    VARCHAR2(20)
NAME                                                           VARCHAR2(4000)
SQL_TEXT                                                       VARCHAR2(1000)
ERROR_NUMBER                                                   NUMBER
ERROR_PARAMETER1                                               VARCHAR2(80)
ERROR_PARAMETER2                                               VARCHAR2(80)
ERROR_PARAMETER3                                               VARCHAR2(80)
ERROR_PARAMETER4                                               VARCHAR2(80)
ERROR_PARAMETER5                                               VARCHAR2(80)
ERROR_MSG                                                      VARCHAR2(4000)

20:12:55 ORACLE@orcl > select user_id, status, timeout from dba_resumable;

 USER_ID STATUS       TIMEOUT
---------- --------- ----------
      90 NORMAL          3600

20:13:13 ORACLE@orcl >
 

Pour plus d'informations sur les autres utilisations du "resumable timeout", consultez la documentation officielle.

Suivre les modifications sur une table avec DBA_TAB_MODIFICATIONS

Avec Oracle, il est possible d'avoir une mesure approximative des modifications ayant eu lieu sur une table depuis la dernière prise des statistiques. La vue SYS.DBA_TAB_MODIFICATIONS (et ses déclinaisons ALL_* et USER_*) est alimentée par la méthode DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. Cette procédure est invoquée à certains moments clés par Oracle (10g et 11g). Le niveau de statistique (paramètre statistics_level) doit être à TYPICAL ou ALL pour que le monitoring s'effectue.
*On doit préfixer la vue de type DBA_... par SYS puisque le synonyme public est absent en 10g.

Même si les mesures sont approximatives, elles permettent de constater la charge que peut avoir une table en quelques jours/semaines/mois. Et comme Oracle désigne une table comme étant STALE lorsque 10% de ces données ont été modifiées (Insert, Update, Delete), on peut prévoir une prochaine collecte de statistiques pour cet objet par la job interne GATHER_STATS_JOB.

Afin de simplifier, voici un exemple avec la vue USER_TAB_MODIFICATIONS:

19:27:16 ORACLE@orcl > create table test_tab_modif(id number, nom varchar2(30));

Table créée.

19:27:28 ORACLE@orcl > select * from user_tab_modifications;

aucune ligne sélectionnée

19:27:55 ORACLE@orcl > exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

Procédure PL/SQL terminée avec succès.

19:28:21 ORACLE@orcl > select * from user_tab_modifications;

aucune ligne sélectionnée

19:28:23 ORACLE@orcl > insert into test_tab_modif(id,nom)
19:28:38   2  select rownum, 'nom ' || to_char(rownum) from dual connect by level<10001;

10000 lignes créées.

19:28:44 ORACLE@orcl > commit;

Validation effectuée.

19:28:47 ORACLE@orcl > select * from user_tab_modifications;

aucune ligne sélectionnée

19:28:52 ORACLE@orcl > exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

Procédure PL/SQL terminée avec succès.

19:28:56 ORACLE@orcl > select * from user_tab_modifications;


TABLE_NAME                     PARTITION_NAME                                
------------------------------ ------------------------------                
SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES              
------------------------------ ---------- ---------- ----------              
TIMESTAMP           TRU DROP_SEGMENTS                                        
------------------- --- -------------                                        
TEST_TAB_MODIF                                                               
                                 10000          0          0              
2011-11-20 19:28:56 NO              0                                        
                                                                          

19:32:13 ORACLE@orcl > exec dbms_stats.gather_table_stats(ownname=>'ORACLE',tabname=>'TEST_TAB_MODIF');

Procédure PL/SQL terminée avec succès.

19:32:22 ORACLE@orcl > select * from user_tab_modifications;

aucune ligne sélectionnée

19:32:22 ORACLE@orcl >

N.B. Les transactions annulées de type "rollback" incrémentent tout de même les différents compteurs de cette vue. Ainsi, si vous modifiez 10 000 rangées et ensuite annulez le tout (rollback), vous aurez 10 000 UPDATES de plus lors du prochain appel de DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.

Pour plus d'informations sur la vue ALL_TAB_MODIFICATIONS, consultez la documentation officielle.

Oracle : Vérifier l'utilisation d'un index

Dans un schéma/application, il se peut que certains index soient inutilisés et ce, pour différentes raisons. Par exemple, il peut s'agir d'un index redondant (un autre index similaire à celui-ci) dont l'utilisation est inexistante ou encore parce que le prédicat (clause WHERE) ne fait jamais référence aux colonnes de cet index. Il ne faut jamais oublier qu'il y a un coût (overhead) pour Oracle à entretenir chacun de ces index lors d'opérations DML.

Toutefois, ce n'est pas parce qu'un index n'est pas utiliser pendant une journée ou une semaine qu'il est non nécessaire. Un rapport mensuel peut effectuer des sommaires sur certaines périodes et appeler cet index en apparence inutile. Sans cet index, un « full scan » de la table pourrait ralentir de beaucoup la progression du rapport.

Avec Oracle, il est possible de « monitorer » i.e. suivre l'utilisation d'index précis. Le document [ID 136642.1], accessible via MyOracleSupport (anciennement Metalink), explique une technique que je vais résumer ici.

-- Mettre l'index désiré en mode surveillance
alter index MON_INDEX monitoring usage;

-- Vérifier si l'index en cours de surveillance a été utilisé.
-- Ce sont les colonnes USED et MONITORING qui nous intéressent.
select * from v$object_usage where monitoring='YES' and used='YES';

-- Retirer l'index du mode surveillance
alter index MON_INDEX nomonitoring usage;

Prendre note qu'en activant cette surveillance, il y a une taxe (overhead) pour l'engin Oracle tant que la surveillance demeure active. On peut laisser cette surveillance pendant environ un mois et retirer les index au fur et à mesure qu'on réalise qu'ils sont utilisés. Je suis conscient que nous n'avons pas plus de détail sur l'utilisation (quelle fréquence? par qui? quels énoncés?) mais c'est tout de même un départ.

---

Et maintenant dans Oracle 11g, il est possible de rendre invisible un index aux yeux de l'optimizer d'Oracle et des utilisateurs. Ainsi, si on veut rendre visible à nouveau un index, c'est très rapide et ça ne demande pas de reconstruire l'index (peut être très long pour de gros volumes de données). Par contre, il est important de mentionner que les instructions DML continuent de mettre à jour les index invisibles.

-- Pour rendre invisible/visible un index, voici la commande à exécuter :
alter index MON_INDEX invisible;
alter index MON_INDEX visible;

-- Pour afficher la visibilité d'un index :
SELECT index_name, visibility
FROM user_indexes
WHERE index_name = 'MON_INDEX';

Par défaut, les plans d'exécution n'utiliseront pas les index invisibles. À noter qu'il existe un paramètre Oracle (également valide au niveau de la session) rendant « visible » les index invisibles pour l' « optimizer » :

OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE

Cela vous permet donc de tester dans un environnement de simulation l'effet de l'ajout ou d'une suppression d'un index. Ainsi, vous devriez être plus confiant le moment venu de supprimer un index en production.

Inscrire une information dans l'alert.log

Lors d'un traitement critique, il peut être intéressant d'écrire un message de succès ou d'échec dans le fichier alert<SID>.log. Par exemple, si vous avez un trigger « before shutdown » et qu'une exception est levée, il serait intéressant de l'inscrire dans le fichier d'alertage. C'est à l'aide de procédures non documentées d'Oracle du package DBMS_SYSTEM que nous y arriverons.

KSDWRT(<n>,<message>) permet d'écrire un message dans le fichier des alertes ou dans un fichier de trace selon le paramètre <n>.

N = 1 : Écrire dans le fichier de trace.
N = 2 : Écrire dans le fichier des alertes.
N = 3 : Écrire dans les deux fichiers.

Un exemple concret :

CREATE OR REPLACE PROCEDURE XYZ_P_SET_ABC
IS
…
BEGIN
…
… TRAITEMENT XYZ
…
 EXCEPTION
    WHEN OTHERS THEN
    SYS.DBMS_SYSTEM.KSDWRT(2, SQLERRM || '    - ERREUR PROC XYZ_P_SET_ABC');
END;

Générer des données bidons en SQL

En programmation, il est souvent utile d'être capable de générer des données non persistantes (i.e. non sauvegardées physiquement) afin de réaliser un traitement en mémoire. Par exemple, nous avons une séquence number(5) qui alimente un identifiant unique dans une colonne avec des valeurs allant de 1 à 99 999. Lors d'insertions, il est possible qu'il y ait des « trous de séquence » dû à une annulation ou une erreur système. Si nous voulons récupérer ces identifiants non utilisés, nous avons quelques options dont l'utilisation des ensembles.

L'idée est d'utiliser les valeurs de l'ensemble A (1 à 99 999) moins les données de l'ensemble B (la plage de valeurs de notre identifiant unique allant jusqu'à 99 999). Ainsi, la résultante de cette opération sera l'ensemble des valeurs non utilisées. Dans notre exemple, nous supposons une table d'employés (EMP) avec une colonne ID comme identifiant unique.

select rownum
from all_objects
where rownum < 100000
minus
select id
from emp;

Le problème avec l'utilisation d'une table bidon telle que ALL_OBJECTS est qu'on est limité par le nombre d'entrés dans la table. Si ALL_OBJECTS contient 50 000 entrés et que notre écart de valeurs se situe en-dessous de cette limite, alors nous pouvons utiliser une table de ce genre. Toutefois, si notre écart de valeurs se situe au-dessus de cette limite, nous devons trouver une autre solution. C'est dans cette optique que je suggère l'utilisation de la table DUAL avec un « connect by level < n ».

select rownum
from dual
connect by level < 100000
minus
select id
from emp;

Prendre note que l'utilisation de la table DUAL avec la clause « connect by level » peut occuper un espace mémoire important dans le UGA (dans le PGA si la session est en mode dédiée ou dans le large pool en session partagée).

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

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.

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.

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