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

Mercredi 29 octobre 2008 3 29 /10 /Oct /2008 21:09

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.

Plus
- Email
- Lien
- Imprimer

Par LAO - Publié dans : PERFORMANCE - Communauté : ORACLE
Ecrire un commentaire - Voir les 4 commentaires

Précédent : Administration - ORA-01653 -... Retour à l'accueil Suivant : ORA-01653 - Timeout_Resumable (suite)

Commentaires

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

Commentaire n°1 posté par Franck le 30/10/2008 à 09h33

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.

Réponse de LAO le 30/10/2008 à 10h05

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

Commentaire n°2 posté par Virginie le 30/10/2008 à 10h15

Merci, et bonne lecture...

Réponse de LAO le 30/10/2008 à 10h20

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;

Commentaire n°3 posté par Franceska le 17/06/2009 à 21h59

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.

Réponse de LAO le 17/06/2009 à 22h21

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

Commentaire n°4 posté par Franceska le 23/06/2009 à 21h25

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.

Réponse de LAO le 24/06/2009 à 09h52