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.

Mes items partagés de Google Reader