Discussion :

[LEK]

Bonsoir,
je viens de parcourir la traduction d'un article de ce monsieur sur le site de Pachot concernant les traitements batch pour les mises à jour de données en masse... J'ai envoyé quelques questions à l'auteur restées sans réponses (pour l'instant) aussi je voulais vous les soumettre :
L'article affirme :

Les update (et delete) de masse sont à proscrire

La question que je me pose est donc comment faire sans.

Les techniques utilisées par l'auteur (d'après son blog) :

Instead of bulk UPDATES/DELETES, use set theory to create the compliment via CTAS (or similar). That is, for DELETE recreate what you want to keep, for UPDATE, recreate with the modifications.

There are a few reasons for this:
1) Compression – UPDATES/DELETES are more expensive with compression and they generally leave the data in a much less compressed format
2) Logging – Both UPDATE and DELETE are logging operations and a NOLOGGING CTAS or INSERT /*+ APPEND */ are not.

As a result, it is often faster and better (from a compression point of view) to recreate the segment.

Savez vous comment mettre en oeuvre ces techniques ?
Mais si on a une base opérationnelle de production avec potentiellement un utilisateur loggué
peut-on se permettre ce genre de manoeuvre...
Ou trouver un exemple de ces utilisations pour mises à jour en masse ?

" j’ai codé complètement un flux de donnée qui travaille de manière ensembliste, avec juste une poignée de requêtes SQL (et pas de PL/SQL). "

J'imagine qu'il se retrouve avec un seul upsert au final, mais comment traite-t-il ses erreurs potentielles ?
Comment récupère-til les lignes en erreur ?

Merci pour votre avis sur la question,
Lek.

[pachot]

Bonjour,

Le mieux est de mettre un commentaire dans le blog

J'essaierai de détailler plus tard, mais les idées sont:

Au lieu d'un delete ou d'un update -> créer une nouvelle table avec seulement les données voulues (CREATE TABLE AS SELECT)

Au lieu d'inserts ligne à ligne -> inserts en bulk (INSERT /*+ APPEND */)

Pour la gestion d'erreur: soit faire un select avant pour vérifier les données, soit utiliser DML error logging ( INSERT ... LOG ERRORS INTO )

Mais si on a une base opérationnelle de production avec potentiellement un utilisateur loggué

Non. Personne ne doit lire dans la base cible pendant qu'on charge avec ces techniques.

Cordialement,
Franck.

[mnitu]

...
Pour la gestion d'erreur: soit faire un select avant pour vérifier les données, soit utiliser DML error logging ( INSERT ... LOG ERRORS INTO )
...

Vous devez être très à la bourre ce matin pour dire ça.

[pachot]

@mnitu
Pas compris la dernière remarque.

Pour préciser: le contexte est le chargement massif batch/datawarehouse.
Il vaut parfois mieux lire les données une première fois en bulk pour les valider, et/ou pour mettre à jour les dimensions avant de charger les faits, plutôt que de faire du traitement ligne à ligne avec rejets, rollbacks, etc.
Cordialement,
Franck.

[ojo77]

Savez vous comment mettre en oeuvre ces techniques ?
Mais si on a une base opérationnelle de production avec potentiellement un utilisateur loggué
peut-on se permettre ce genre de manoeuvre...
Ou trouver un exemple de ces utilisations pour mises à jour en masse ?

Quelques exemples dans cet article : http://blog.easyteam.fr/2011/11/22/p...ng-sql-update/

[skuatamad]

Mais si on a une base opérationnelle de production avec potentiellement un utilisateur loggué

Non. Personne ne doit lire dans la base cible pendant qu'on charge avec ces techniques.

Ou utiliser le package DBMS_REDEFINITION

[LEK]

Bonsoir, et désolé pour ce retour tardif.
En tous cas merci pour vos retours nombreux.

@pachot :

Le mieux est de mettre un commentaire dans le blog

Je n'hésiterais pas à l'occasion ;-)

Personne ne doit lire dans la base cible pendant qu'on charge avec ces techniques.

Vu que l'on crée une seconde table en parallèle de la première et qu'au dernier moment on lui ajoute les contraintes et qu'on la renomme : qu'est ce que l'on risque vraiment si ce n'est la possibilité de perdre des données qui auraient pû être insérées/modifiées pendant notre opération... En fait si je me pose ces questions c'est que personnellement j'ai une base opérationnelle standard qui doit permettre des imports relativement massif (en journée) mais sans pour autant s'arrêter de fonctionner (au moins en lecture car comme dans la plupart des cas ma base et plus utilisée en lecture qu'en écriture)... Il doit bien y avoir de tels systèmes en production de part le monde : ont-ils tous des systèmes plus ou moins complexes de redondance/conciliaton de données ou que sais-je ? et ne pourrais-je pas vraiment avec ces techniques alimenter ma base "à chaud" ?

@mnitu : je n'ai pas compris non plus : le select serait-il à éviter dans ce cas?

@ojo77, @skuatamad :
mille merci pour ces liens : je me plonge dans la lecture de suite ;-) et reviens vers vous en cas de questions.

Merci encore,
Lek.

[mnitu]

Lire les données avant le chargement pour les valider implique soit un niveau d'isolation serializable, soit une garantie que les données ne bougent pas pendant la transaction. Si le traitement batch s'exécute dans une fenêtre de non utilisation de l'application autrement dit mono-utilisateur ça devrait ne pas poser des problèmes.

[LEK]

Merci je comprends mieux la remarque.
Donc si je te suis bien tu renforce l'idée qu'on ne peut en aucun cas utiliser ce type de traitements dans un environnement multi-utilisateurs même si les transactions de mises à jours de ces mêmes utilisateurs sont plus que rare ? (je sais que ce n'est pas en insistant qu'on me dira oui mai j'essayes de trouver une solution ..)

[LEK]

@skuatamad, merci pour l'info : cette solution pour mise à jour à chaud me semblait assez séduisante malheureusement je ne l'ai pas précisé mais je travailles exclusivement sous Orale 10gr2 en standard edition (j'ai cru comprendre qu'il fallait être en Enterprise Edition )

@ojo77 : merci pour le lien mais l'exemple pointé reste assez simpliste : une seule table en jeux, sans index, ni contraintes référentielles... Je vais voir si je trouve d'autres articles sur la toile.

[pachot]

Bonjour,

j'ai une base opérationnelle standard qui doit permettre des imports relativement massif (en journée) mais sans pour autant s'arrêter de fonctionner (au moins en lecture

Ce qu'il y a, c'est que les 2 problématiques sont opposées: L'optimisation du chargement massif se fait justement en évitant tout le travail de garder les données consistantes lorsque d'autres interrogent les données qu'on est en train de charger. c'est à dire:
- soit on maximise la concurrence d'accès avec des petites transactions protégées par de l'undo, avec la maintenance des indexes en même temps, avec le partage des données dans le buffer cache,...
- soit on maximise la performance avec chargements massif, reconstruction d'index à la fin, accès direct aux fichiers sans passer par le buffer cache... mais du coup, on bloque l'accès à toute la table pendant qu'on la charge.

Bien sûr il y a quelques solution pour mixer les deux. Si on peut se permettre de double le volume, on peut tout charger dans d'autres tables (en laissant les autres ouvertes en lecture). Puis on renomme en masse (ou on pointe des synonymes, ou on fait des exchange partition,... ) pour pointer sur les nouvelles tables.

Mais bien sûr, c'est un peu plus complexe à gérer. Et en Standard Edition, encore un peu plus.

Cordialement,
Franck.

[LEK]

OK je comprends la différence des approches.
Lorsque je parle de chargement massif : en fait j'ai des tables qui font au max 2 milliers de lignes et seulement une qui pourrait frôler un jour le million de lignes.
Lorsque je fais les tests en CTAS, mes tables sont recréées en 1,2 secondes avec reconstruction d'index... Donc cela me semblait jouable à prime abord : je peux effectivement doubler le volume de la base sans problème.
Je vais me renseigner sur les techniques de renommage/synonymes... Ca revient à simuler ce que fais le package dmbs_redefinition in fine, non ?

Merci pour la réponse.

[pachot]

Je vais me renseigner sur les techniques de renommage/synonymes... Ca revient à simuler ce que fais le package dmbs_redefinition in fine, non ?

Non. dbms_redefinition permet de faire des réorganisations online. Ici le but est de charger des nouvelles données. C'est différent.

[LEK]

Bonsoir,
j'avance un peu sur le sujet : pour ceux qui aimerais en savoir plus sur cette problématique, j'ai trouvé ce site qui donne un petit aperçu d'un script pour éviter un delete en masse : http://allappsdba.blogspot.fr/2012/1...from-very.html

Lek.

[mnitu]

Comment dirais-je: il y a une inflation des (mauvaise) blogs sur Oracle ce qui rend les choses difficiles.
Je ne ferais pas confiance à un dba qui extrait la défintion de la table via dbms_metadata pour la créer en suite par un create table as select. Il y a des pièges :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> desc hr.employees
Name           Type         Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
EMPLOYEE_ID    NUMBER(6)                              
FIRST_NAME     VARCHAR2(20) Y                         
...  
SQL> create table t_empt as select * from hr.employees where 1=2;
 
Table created
 
SQL> desc t_empt
Name           Type         Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
EMPLOYEE_ID    NUMBER(6)    Y                         
FIRST_NAME     VARCHAR2(20) Y                         
... 
SQL>

Cherchez mieux la réponse à ces questions sur les sites des experts reconnues comme ceux qui font l’objet des traductions faites par pachot.
Chechez sur le site de Tom Kyte "How to Update millions or records in a table" par exemple.

[LEK]

Merci pour les précisions. L'article de Tom, je l'ai parcouru aussi... en fait avant même d'ouvrir ce post : par contre il utilise lui même le ctas avec le piège potentiel que tu précises (je souhaitais aussi voir des exemples d'import utilisés dans le cas de datawarehouse pour voir comment d'autres s'y prenaient)...
Pour le blog que je pointe, effectivement l'opération décrite est bizarre, jai supposé qu'il devait y avoir une opération finale pour recréer les index/triggers...
Pour ma part je travaille sur une problématique similaire : je reçois quelques fichiers csv que j'interroge via des tables externes et je met à jour plusieurs tables de mon schéma soit en efface et remplace, soit via des soft delete suivant les cas... Mes deux problèmes majeurs sont justement les suivants :
- je ne peux arrêter ma base de production pendant mon chargement
- je dois mettre à jour plusieurs table de mon schéma et même s'il n'y a pas de retraitement/transformation particuliers de l'information recueillies dans mes fichiers csv; j'ai dû pour l'instant coder mes chargements en PL... Du coup pour les plus gros chargements que j'ai eu à réaliser (environ 3 fichiers de 10 à 60 000 lignes) le chargement se fait en 5/7 minutes et j'aiemerais si possible réduire ce temps de chargement....
En faisant un create table et insert avec un hint /*+ append */, sans logging, juste pour tester l'insert dans une seule de mes tables pour 1 fichier de 60 000 lignes, le temps de chargement descend effectivement a 2/3 secondes, c'est pour moi fortement encourageant : malheuresement je dois être capable de réinterroger cette table pour pouvoir en renseigner d'autres ayant une FK vers elle, du coup je ne peux déjà plus utilisé le hint append car il verouillerai la lecture dans la même session, si j'ai bien suivi...
En bref l'ensemble des exemples que je trouve permette de charger une seule table de maniere massive alors que moi, j'aimerais pouvoir renseigner plusieurs tables apparentées...

[Waldar]

Il faut aussi que vous compreniez que les points que vous abordez sont intéressant quand on recharge des millions de lignes.

Pour 60.000 lignes, vous ne verrez pas de différence très significatives, ça reste "petit" sans vous offenser.

Commencez par alimenter les tables de références, puis celles qui ont des dépendances.
Vous pouvez utiliser la même méthode pour toutes vos alimentations :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
MERGE into <table_cible>
using (<table_externe> minus <table_cible>)
...

Je ne vois pas l'utilité des delete si vous devez conserver la base interrogeable pendant les chargements.

[mnitu]

Bref, maintenant le problème que vous avez est pour ainsi dire le suivant : j’ai un traitement en PL/SQL qui charge/modifié à partir des fichiers csv d’environ 600000 lignes, des tables relativement grosses et qui prend 5 à 7 minute. Je souhaite l’optimiser !
Pour cela commencer par faire une trace SQL étendu du votre traitement PL/SQL. Le fichier trace brut ainsi obtenu passez-le dans un outil de profilage come tvd$xstat ou autre. C’est ainsi que vous allez avoir deux informations vitales pour l’optimisation : quelles partie du traitement prends le plus de temps et dans quelles proportion. Une fois avoir identifié ces point chauds vous pouvez toute simplement envisager les solutions et toute aussi important quantifier les impacts des vos optimisations !

[funoracle]

je souhaitais aussi voir des exemples d'import utilisés dans le cas de datawarehouse pour voir comment d'autres s'y prenaient...

Personnellement j'essayerai de ne pas trop mélanger l'oltp et le décisionnel, Il y aura toujours quelqu'un qui sera lésé, et c'est bien normal.
Puisque vous n'avez pas de problème de place je créerai une deuxième base plus orientée décisionnelle (style infocentre). Avec pourquoi pas une réplication actif-passif goldengate ou stream.

Franck.