PL-SQL - Performance - Insertion en masse. (FORALL)

Bonsoir,

L'autre soir, lorsque j'ai  crée ma "big table" de 125 millions de lignes, j'ai eu quelques soucis.
http://www.lao-dba.com/article-24006948.html

• Au final, le serveur est parti en vrille, et je n'ai inséré que 115 millions de lignes.
• Cela a pris plusieurs heures (au moins 4 je crois me souvenir).

Finalement, cette durée était prévisible. Et pourtant le oracle par l'intermédiare du PL-SQL permet des choses impressionnantes.

J'ai donc décidé ce soir revisiter mon code d'insertion afin que cela ne prenne plus qu'une heure, et qu'il aille jusqu'au bout => 125 millions de lignes.

---

declare 
   type t_reponse IS TABLE of Reponse_3%ROWTYPE;
   l_reponse  t_reponse := t_reponse();
BEGIN
   FOR z IN 1..625 LOOP
      FOR i IN 1..200000 LOOP
         l_reponse.extend; /* On étend  la taille du tableau */
       /* Remplissage du table */
         l_reponse(l_reponse.last).Q1      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q2      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q2      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q4      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q5      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q6      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q7      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q8      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q9      :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).Q10     :=Round(DBMS_RANDOM.VALUE(0,10));
         l_reponse(l_reponse.last).ID_PAYS :=Round(DBMS_RANDOM.VALUE(1,25));
         l_reponse(l_reponse.last).ID_FORMULAIRE       :=Round(DBMS_RANDOM.VALUE(1,100));                              l_reponse(l_reponse.last).DATE_QUESTIONNAIRE  :=Round(DBMS_RANDOM.VALUE(20071201,20071231));
      END LOOP;
    /* Insertion en masse */  
    FORALL i IN l_reponse.first..l_reponse.last
        INSERT INTO Reponse_3 VALUES l_reponse(i);    
      COMMIT;
      l_reponse.TRIM(200000); /*On vide le tableau,... sinon risque de problème de mémoire */
   END LOOP;
END;

---

Tout d'abord, j'ai commencé par créer une structure identique à ma table Reponse_1. Pas très compliqué.

---

CREATE TABLE Reponse_3 AS SELECT * FROM Reponse_1 WHERE 1=0;

---

Ensuite, on déclare un Type qui correspond à un tableau de ligne de la table Reponse_3
Enfin on boucle pour alimenter notre tableau par paquet de 200000, et on  appelle la commande FORALL, qui execute une insertion de masse qui sera beaucoup plus efficace qu'une insertion ligne à ligne.


Moralité de l'histoire, avant de foncer et de pondre du code, il peut être utile de se poser et de réfléchir aux différentes solutions possibles.


LAO.


 

Comments

[daniel NGEA NA MOUEN]

Avec une dose millimétrée d'humour, des explications courte, concise et claire. DBA et DMA de métier, mais je suis tombé par hasard sur votre travail et je me suis accroché.
Je souhaite partagé vos projets si possible!

[Laurent ALLIO]

Bonjour,

Merci pour ce commentaire... Voir que 6 ans après certains articles trouvent grâce est plutôt sympa..

[Franceska]

Merci Lao pour tes commentaires car je me suis rendu compte que mon tableau n'etait pas initialisee mais ce que je veux faire :c'est prendre des donnees dans une table A qui contient 200 millions de rows et les inserer dans CALLLOG.Car quand j'utilise l'insertion classique ca prend un temps fou pour terminer.
Merci

[LAO]

Bonjour,

La table CALLOG, c'est une copie de la table A ? Quelle est la fréquence du traitement. 200 Millions de lignes ca commence à faire, et effectivement le faire par lot permet une reprise sur echec.
Pour ce qui est des performances, je te conseille également de vérifier le nombre de redo et leur taille. Egalement (si cela est possible) d'éviter d'être en archive log pendant ce traitement.
Ensuit, il y également la piste des indexes. Si la table CALLLOG est indexée, alors un Insert /*+APPEND+*/ serait le bienvenu ou alors une suppression des indexes avant insertion et reconstruction
à la fin de l'insertion. Si ce traitement est fréquent et dépend de critère (date par exemple), les options de partitionnement et parallélisme seraient également bienvenu. Attention le partitioning
est une option payant et nécessite une version enterprise.

LAO.

[Franceska]

bon article mais chaque fois que j'execute ce bout de code ca me retourne une erreur PLS-00103: Encountered the symbol "IN" when expecting one of the following:

:= . ( @ % ; not null range default character


declare
type t_dumpjune is TABLE of DUMPJUNES%ROWTYPE
l_response t_dumpjune:=t_reponse();
FORALL i in l_reponse.first ..l_reponse.last
insert into calllog values l_reponse(i);
commit;
l_reponse.TRIM(200000);
end loop;
end;

[LAO]

Bonjour Franceska,

Dans ton bout de code, tu sembles reprendre l'exemple à un cas concret mais après la déclaration du tableau l_response, il n'y a aucune initialisation (la partie avec les random dans mon
exemple).  A quoi ressemble la table DUMPJONES et que veux tu inserer dans callog. Il faut peut être définir un curseur avec le SELECT qui va bien et parcourir le curseur pour initialiser le
tableau. Si aucun champ calculé, je doute qu'il y ai un intéret. Il faut mesurer et comparer.

LAO.

[Virginie]

Ca semble tellement simple et, en même temps, ça ne nous traverse même pas l'esprit ! Merci de nous réveiller les neurones :)
J'avais pris du retard à la lecture des derniers posts. J'ai donc imprimé le tout et lu tout ça dans mon ptit RER préféré et, franchement, ça se lit super bien. Y a même du suspens :P

[LAO]

Merci, et bonne lecture...

[Franck]

C'est beau et c'est simple en plus d'être efficace.
Merci d'avoir réfléchi pour nous :)

[LAO]

Je pense même qu'on peut encore descendre, car contrairement à la première fois (insertion classique), ma table Reponse_3 était en mode logging et du coup a généré pas mal de redo.

LAO.