11.20.2011

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.

4.05.2011

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.

3.23.2011

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

Mes items partagés de Google Reader