A moins de travailler avec des tables vides et d’assurer que les modifications ne sont pas journalisées via l’attribut NOLOGGING ou l’option UNRECOVERABLE, combiner index et chargement SQL*Loader en mode direct est en général une assez mauvaise idée… C’est, au moins, ce que suggère la documentation Oracle à plusieurs endroits à travers des petites phrases comme celles-ci :
- [ici] « Avoiding index maintenance is quite reasonable when the number of rows to be loaded is large compared to the size of the table. But if relatively few rows are added to a large table, then the time required to resort the indexes may be excessive. In such cases, it is usually better to use the conventional path load method […] »
- [ici] « A record that violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected) »
- [ici] « SQL*Loader leaves indexes in an Index Unusable state when […]«
Cet article présente plusieurs cas de mise en œuvre d’index avec SQL*Loader en mode direct qui donnent des résultats relativement mitigés… Souvenez-vous en avant d’utiliser ce mode.
Erreurs de chargements
Le risque le plus important -quoique-, lié au fait d’utiliser des index avec des chargement en mode direct, est de laisser les index inutilisables (UNUSABLE) :
- Dans le meilleur des cas, les temps de réponse se dégradent significativement.
- Dans le pire, lorsqu’il s’agit d’un index unique, la contrainte ne peut plus être vérifiée et les chargements consécutifs échouent.
Exemple 1 : crash d’un processus
La liste des cas d’erreurs qui rendent un index inutilisable pendant un chargement en mode direct est disponible dans Oracle® Database Utilities 11g Release 2 (11.2) – Indexes Left in an Unusable State. Nous allons créer un exemple qui montre à quel point il est facile d’obtenir un index invalide :
Dans ce premier exemple, nous allons créer une table X avec un index sur une de ses colonnes :
sqlplus demo/demo
create table X(
id number,
col2 number) logging;
create index X_IX on X(col2) logging;
Nous allons ensuite créer un fichier de contrôle et un fichier de données :
cat /home/oracle/x.ctl
load data
infile '/home/oracle/x.dat'
append
into table X
fields terminated by "," optionally enclosed by '"'
( id, col2 )
for i in `seq 10000`; do
echo "$i,1" >>/home/oracle/x.dat
done
Pour rendre l’index invalide, il suffit par exemple de lancer dans un terminal la commande ci-dessous :
for i in `seq 20`; do
sqlldr userid=demo/demo
control=/home/oracle/x.ctl
log=/home/oracle/x.log
direct=true
done
Alors que dans un autre, vous lancerez celle-ci :
ps -ef |grep sq[l]ldr |awk '{print "kill -9",$2}'|sh
Le résultat est un index invalide comme le montre la requête ci-dessous :
select status
from user_indexes
where index_name='X_IX';
STATUS
--------
UNUSABLE
Exemple 2: Insertion de doublons
Dans le cas où un index est unique, le rendre invalide est encore plus simple en mode direct, il suffit d’y insérer une valeur en double ; pour cet exemple, nous allons modifier le schéma précédent :
sqlplus demo/demo
truncate table x;
drop index x_ix;
create unique index x_uq on x(id);
Exécutez ensuite 2x la commande ci-dessous :
sqlldr userid=demo/demo
control=/home/oracle/x.ctl
log=/home/oracle/x.log
direct=true
Le fichier log de la 2nd exécution montre :
The following index(es) on table X were processed:
index DEMO.X_UQ was made unusable due to:
ORA-00001: unique constraint (DEMO.X_UQ) violated
Pourtant les données sont, malgré tout chargées dans la table :
Table X:
10000 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Si vous exécutez encore le chargement, ça devient n’importe quoi puisque une partie des données est chargée mais le fait que l’index est invalide interdit le reste du chargement :
ORA-26026: unique index DEMO.X_UQ initially in unusable state
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Load completed - logical record count 5000.
Et évidemment, impossible de recréer l’index simplement ; il vous faudra passer par un grand nettoyage :
sqlplus demo/demo
alter index x_uq rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Ressources nécessaires au chargement
En outre, même quand les opérations sont en mode NOLOGGING, maintenir un index depuis le loader peut coûter plus cher que de l’alimenter en mode conventionnel surtout si le volume des données modifiées représente une petite partie de la taille de la table. Pour vous donner une idée, recréer la table de l’exemple précédent :
sqlplus demo/demo
drop table x purge;
create table X(
id number,
col2 number) nologging;
create index X_IX on X(col2) nologging;
Exécutez un millier de chargements préalables pour que la taille de l’index devienne important. Vous pourrez alors comparer les temps de chargement en mode direct et en mode conventionnel. Vous découvrirez, sans parler des contraintes sur le verrouillage par exemple, que les meilleurs temps de réponse ne sont pas forcément pour le scénario direct ; voici 2 extraits assez collectés sur mon serveur Linux 32bit avec une base 11.2.0.3 :
- en mode direct
time sqlldr userid=demo/demo
control=/home/oracle/x.ctl
log=/home/oracle/x.log
direct=true
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Apr 29 11:24:39 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Load completed - logical record count 10000.
real 0m0.304s
user 0m0.024s
sys 0m0.028s
- en mode conventionnel avec quelques optimisations sur les tailles de buffers et tableaux
time sqlldr userid=demo/demo
control=/home/oracle/x.ctl
log=/home/oracle/x.log
direct=false rows=10000 bindsize=10000000
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Apr 29 11:24:32 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
specified value for readsize(1048576) less than bindsize(10000000)
Commit point reached - logical record count 10000
real 0m0.136s
user 0m0.052s
sys 0m0.016s
Et ce n’est rien si vous comparez à une base de données pour laquelle la journalisation est activée. Evidemment, si vous supprimer les index…
Note:
Pour certains cas d’utilisation, vous pouvez utiliser l’option SINGLEROW pour alimenter une table en mode direct tout en maintenant l’index comme pour un chargement conventionnel.
Conclusion
Le mode DIRECT de SQL*Loader est une optimisation possible pour réduire les temps de chargement. Avant de l’activer, pensez-y bien :
- Avez-vous vraiment besoin d’accélérer les temps de chargement ? de combien ?
- Quelle est l’activité transactionnelle sur la table ?
- Pouvez-vous réellement réaliser des opérations en mode NOLOGGING sur la table ?
- La table est-elle partitionnée ? Quel est le pourcentage de données que vous chargez à nouveau dans la table ?
- Quels sont les index sur la table ? Pouvez-vous les désactiver le temps du chargement et les reconstruire ensuite ? Quel impact si vos index deviennent invalides ?
Et dans tous les cas, ne jouez pas à pile ou face, faites des tests et valider l’intérêt du mode direct. Dans le doute, restez en mode conventionnel…
