INSERT SELECT en NOLOGGING & APPEND

Bonjour les Experts ,
J'ai une table partitionnée que je truncate (uniquement une partition à truncater) au début de mon traitement batch.
A un certain moment du traitement un INSERT ...SELECT lourd vient alimenter cette partition de la table.
Puisque ma base est en mode NOARCHIVE et que ce traitement est rejouable si erreur , est-ce que je pourrais envisager :

1/ un INSERT ...SELECT en mode NOLOGGING.
Les redo logs ne sont pas utiles dans mon cas ? risque ?

2/Utiliser aussi le mode APPEND dans cette insertion qui normalement ne devrait locker que la partition en question (Les autres partitions restent accessibles en dml ?).
En plus aucun souci de fragmentation causé par l'append car re-truncate de la partition au prochain passage du même batch.

Bref , le nologging et append est-il dangereux dans mon contexte sachant que la base est sauvegardé chaque soir par une sauvegarde BCV et que en cas de crash matériel un retour à cette sauvegarde est envisagé ...

3/ Pour finir quand une partition d'une table est "truncaté", que se passe-t-il pour l'index local ? est-ce que la partition équivalente de l'index est aussi "truncaté" automatiquement ou je risque d'avoir un index très fragmenté suite à plusieurs truncate de la partition de table ?
merci :ccool:

Bonjour,

La base n'est pas en archivelog mode. Donc pas besoin de préciser NOLOGGING: tous les chargements en direct-path se feront en NOLOGGING.

La raison: en direct-path on écrit directement dans les datafiles, sans passer par la SGA. Donc pas besoin de redo log en cas de crash d'instance.

Par contre, un insert conventionnel ( INSERT ... SELECT s'il n'y a pas le hint APPEND ) va générer du redo: les modifs se font en mémoire, il faut que ces modifs soient protégées en cas de crash d'instance.

Citation:

---

Utiliser aussi le mode APPEND dans cette insertion qui normalement ne devrait locker que la partition en question

---

Seulement si la partition est précisée dans l'insert avec le nom de la table.

Oui le truncate d'une partition vide l'index aussi.

Donc en bref:

Code:

---

1 2 3

ALTER TABLE nom_table TRUNCATE PARTITION nom_partition; INSERT /*+ APPEND */ INTO nom_table PARTITION(nom_partition) SELECT ...

---

Cordialement,
Franck.

Bonjour,

Pour répondre à ta question n°3, c'est oui.

L'avantage d'un index local, c'est que tu n'as pas à t'en soucier. Que tu fasses un TRUNCATE, mais aussi un SPLIT ou un MERGE de partitions sur une table, Oracle effectue la même chose sur les partitions de tous les index locaux de ta table.

Par contre, si ta table possède en plus des index globaux, ou des index non partitionnés, c'est à toi de les gérer.



Sinon je suis d'accord avec les propos de Pachot, à un détail près.

De manière générale, pour faire de l'insertion en Bulk sur une table, il ne faut pas qu'il y ait d'index, ou bien il faut le rendre inutilisable (UNUSABLE).

Dans ton cas donc, avant de faire du Bulk Insert dans la partition de la table, il faut donc penser, sur les index locaux, à passer la partition concernée par le chargement en UNUSABLE.

Une fois le chargement fait, il ne reste plus qu'à rebuilder cette partition.


Sauf erreur de ma part, voilà ce que cela doit donner avec 2 index locaux :

Code:

---

1 2 3 4 5 6 7 8 9 10

ALTER TABLE nom_table TRUNCATE PARTITION nom_partition;   ALTER INDEX nom_index_1 MODIFY PARTITION nom_partition UNUSABLE ; ALTER INDEX nom_index_2 MODIFY PARTITION nom_partition UNUSABLE ;   INSERT /*+ APPEND */ INTO nom_table PARTITION(nom_partition) SELECT ... COMMIT ;   ALTER INDEX nom_index_1 REBUILD PARTITION nom_partition ; ALTER INDEX nom_index_2 REBUILD PARTITION nom_partition ;

---

Deux remarques s'imposent à mon humble avis

1) il faudrait rappeler que le direct path load peut être silencieusement ignoré si la table possède un trigger ou une contrainte d'intégrité.

2) par rapport à la remarque de rouardg concernant la mise dans un statut UNUSABLE des indexes locaux (uniquement ceux correspondants à la partition touchée par l'insert) c'est exactement ce que j'ai fait et cela a plus ou moins bien fonctionné. Par contre, comme la maintenance des indexes lors d'un direct path load est très performante (bulk insert à la fin), il serait intéressant de comparer les deux cas:

(a) direct path load avec indexes locaux "unusable"
(b) direct path load avec tous les indexes en place.

Bonjour Mohamed,

L'approche unusable/rebuild est à mon avis toujours plus rapide sur une partition vide, d'autant plus qu'on évite de devoir recalculer les stats de la partition d'index.

Cordialement,
Franck.

bonjour
le tablespace , la table et la partition sont en mode logging.
par contre la base est en mode noarchivelog.
un dml sur la table effectue t il une ecriture dans les redo log ?
comment verifier cela ?
merci

bonjour

Admettons que mon traitement est REJOUABLE en cas de crash de la base(en noarchivelog) ou du système ou du disque (media failure). Puis-je dans ce cas mettre toutes les partitions de mes tables en mode nologging. Mon traitement risque-t-il d'être altéré (erreurs ...) à cause de cette désactivation surtout le fait qu’il est parallélisé (auto dop) : Effets inattendus en mode nologging ?

Merci pour votre aide

Citation:

---

le tablespace , la table et la partition sont en mode logging.
par contre la base est en mode noarchivelog.
un dml sur la table effectue t il une ecriture dans les redo log ?
comment verifier cela ?

---

Oui, les commandes DML provoquent bien une écriture dans les Redo Log Files.

Le fait que ta base soit en NOARCHIVELOG implique simplement que la sauvegarde (par RMAN ou par copie des fichiers au niveau OS) ne peut se faire que base à froid. Il ne peut donc y avoir d'activité sur ta base pendant ce temps.

Citation:

---

Admettons que mon traitement est REJOUABLE en cas de crash de la base(en noarchivelog) ou du système ou du disque (media failure). Puis-je dans ce cas mettre toutes les partitions de mes tables en mode nologging. Mon traitement risque-t-il d'être altéré (erreurs ...) à cause de cette désactivation surtout le fait qu’il est parallélisé (auto dop) : Effets inattendus en mode nologging ?

---

Si tu es sur que ton traitement est parfaitement rejouable, alors oui tu peux mettre les tables concernées par ce traitement en NOLOGGING.

En cas de crash, il y a 2 cas de figure :

1) crash d'instance : pas de pb, au redémarrage de la base, Oracle va faire un Recover automatique pour récupérer les dernières données commitées qui n'ont pas été écrites dans les Datafiles. Il ne te reste plus ensuite qu'à rejouer ton traitement.

2) crash avec Media Failure : là tu dois déjà restaurer ta base avec la dernière sauvegarde dont tu disposes. Toutes les données que tu as saisies dans ta base (je ne parle donc pas des données insérées par ton traitement) sont donc perdues.

Es-tu capable de les ressaisir ?

De plus, tu dois rejouer tous les traitements qui ont été passés depuis la dernière sauvegarde. Il faut donc disposer de tous les jeux de données qui ont été traités. Je pense que c'est la cas ?

Bonjour,

Citation:

---

Admettons que mon traitement est REJOUABLE en cas de crash de la base(en noarchivelog) ou du système ou du disque (media failure). Puis-je dans ce cas mettre toutes les partitions de mes tables en mode nologging. Mon traitement risque-t-il d'être altéré (erreurs ...) à cause de cette désactivation

---

Le recovery sera automatique en case de crash d'instance ou du système car les opérations affectées par NOLOGGING écrivent directement dans les fichiers.

En cas de panne disque, là les traitements en NOLOGGING on marqué les blocs en question comme corrompus. Si tu peux faire un tuncate de la partition et refaire le traitement, alors tu récupères tout.

Attention, ce n'est pas que le chargement qu'il faut rejouer: toutes les modifications qui ont touché à ces blocs aussi !

Citation:

---

le tablespace , la table et la partition sont en mode logging.
par contre la base est en mode noarchivelog.

---

Sur une base en NOARCHIVELOG mode, c'est exactement comme si tous les objets étaient en NOLOGGING

Citation:

---

un dml sur la table effectue t il une ecriture dans les redo log ?

---

Oui. seul le redo sur les insert en direct-path (APPEND ou PARALLEL) est évité. Mais les opérations conventionnelles, les blocs d'undo et la maintenance des index génère du redo.

Citation:

---

comment verifier cela ?

---

En regardant dans V$MYSTAT la valeur de 'redo size'. Comme j'ai fait ici: http://ora-demo.pachot.net/redosize.html par exemple.

Cordialement,
Franck.

Citation:

---

Sur une base en NOARCHIVELOG mode, c'est exactement comme si tous les objets étaient en NOLOGGING

---

C'est faux !

Exemple : je crée une table en LOGGING, et je la peuple par un script SQL injectant 10000 lignes, en commitant toutes les 1000 lignes.

Imaginons une coupure électrique alors que l'on insérait la ligne n° 2500.

Supposons aussi que DBWR n'a jamais fait son travail, c'est-à-dire écrire les blocs de données de la table (blocs sales) depuis le Buffer Cache vers les Datafiles.

Au redémarrage de la base, il y a un RECOVER automatique :
- les 1000 premières lignes sont réinsérées dans la table et commitées,
- les 1000 lignes suivantes sont réinsérées dans la table et commitées,
- les 500 lignes suivantes sont réinsérées dans la table, puis rollbackées.

La phase de RECOVER est maintenant terminée, la base est réouverte et est cohérente, et la table ne contient que les 2000 premières lignes.


Si la table avait été créée en NOLOGGING, on n'aurait récupérée aucune ligne, et les blocs de la table seraient effectivement à l'état corrompu.

Citation:

---

Envoyé par rouardg

Si la table avait été créée en NOLOGGING, on n'aurait récupérée aucune ligne, et les blocs de la table seraient effectivement à l'état corrompu.

---

Non non. On récupère exactement la même chose. Instance recovery fonctionne toujours, et de manière automatique et il n'est pas possible de le désactiver (sauf si on fusille les redo logs online...)

Comme tu parles de dbwriter, il s'agit d'insert conventionnel. Ni NOLOGGING ni NOARCHIVELOG ne changent quoi que ce soit sur le redo généré par des modifs en buffer cache.

Si l'insert est direct-path, moins de redo est généré car il est inutile (écriture directe sur disque) donc on récupère la même chose.

Cordialement,
Franck.

devkais, si votre problème est le risque de perte de données, vous pouvez faire votre insert dans une table de travail, puis échanger cette table avec la partition à recharger à la fin du chargement :
http://docs.oracle.com/cd/E18283_01/...2.htm#i1107555

Même si ça plante en plein milieu, votre table finale n'est pas impactée (pas à jour, mais pas impactée).

bonjour
j'ai mis toutes les tables en nologging.
Je n'ai constaté aucune amélioration au niveau du temps d'exécution de mon traitement batch !!!
Est-ce normal ?
merci

Sur une base en NoArchivelog oui c'est normal que nologging ne change rien.
Rien aux performances,rien au volume de redo, rien aux possibilités de recovery.

Bonjour ,
OK c'est clair.
merci