Oracle SQL insert grand ensemble de données de manière efficace par l'utilisation du curseur et commit unique

Le SQL suivant génère tous les enregistrements correspondants entre deux tables qui ont les mêmes schémas et procède ensuite à effectuer une itération sur le curseur qui stocke l'ensemble de résultats. - Je faire ligne par ligne insérer une validation à la fin de cette fonction. Ma question est comment puis-je obtenir le maximum de performance de ce type de requête? Code suit: 

BEGIN  
DECLARE    
   CURSOR foo IS  
        SELECT * FROM tableOne to  
        WHERE EXISTS (SELECT * FROM tableTwo tt  
                       WHERE TO.FOO = TT.FOO  
                       AND TO.BAR = TT.BAR);  --THIS TAKES 5 MINUTES (66 MILLION ROWS)
     BEGIN  
           FOR nextFoo IN foo  
     LOOP  
            INSERT INTO tracker t  
               (id,foo,bar,baz)  
            VALUES(trackerSequence.nextval, nextFoo.foo,nextFoo.bar,nextFoo.baz);  
     END LOOP;   
     COMMIT;  
     END;  
END;

Cette requête peut prendre jusqu'à une heure et je suis en train de réduire le coût lié à elle. Je vais être le traitement de 140 millions d'enregistrements en général, donc je m'attends à le double de la quantité de temps cela prend. Toutes les colonnes sont indexées.

Informations de Version:

10g 10.2

  • :Pourquoi vous n'avez pas essayé d'insertion en bloc?
  • la première des cas de test, j'ai été fournis avec, pour les 10k enregistrements. J'ai couru dans un problème d'échelle tout à fait clairement. Merci de poster une réponse qui utilise bulk insert que cela serait bénéfique.
  • :Quelle est votre version d'Oracle que vous utilisez?
  • Oracle 10g 10.2
InformationsquelleAutor Woot4Moo | 2012-08-01
  1. 6

    comment sur 

    INSERT INTO tracker t SELECT trackerSequence.nextVal
                            ,foo
                            ,bar
                            ,baz 
                      FROM tableOne to 
                            INNER JOIN tabletwo tt 
                         ON (to.foo = tt.foo and to.bar=tt.bar);

    Je me demande si ce serait mieux optimisé.

    Assurez-vous également que le dispositif de suivi -index de table sont désactivés lors de l'insertion d'..

    • Comment aurais-je obtenir mon séquence générateur de feu dans cette requête?
    • umm. wouldnt savent. Avez-vous d'avoir dans l'ordre, ou pourriez-vous générer la séquence sur un second passage?
    • Je suppose que vous pouvez simplement utiliser trackerSequence.nextVal comme dans votre exemple
    • au lieu de "id"
    • Il suffit d'inclure les sequnce dans votre instruction select qui se nourrit de l'insert. Rien n'est plus rapide que SQL brut, c'est même plus rapide que PL/SQL en vrac collections ou un FORALL.
    • Le test de cette mise en œuvre maintenant.
    • Cela va causer d'énormes ROLLBACK segments et peut causer de graves journal d'archive de colmatage. En particulier, si le traitement de 140 mn lignes.
    • comment éviter le gigantesque restauration?
    • Voir ma réponse ci-dessus!
    • asktom semble d'accord avec cette réponse. asktom.oracle.com/pls/apex/...

    InformationsquelleAutor Markus Mikkolainen
  2. 3

    OK, je sais que tu voulais le curseur...

    Le seul avantage réel à l'aide du curseur, c'est commettre tous les 10k? les lignes lors du traitement d'une quantité de données pour éviter de remplir les journaux jusqu'.

    À moins que vous vraiment besoin de l'aide du curseur, Éliminer la ligne de traitement. 

    insert into tracker (id, foo, bar, baz)
select trackersequence.nextval, t1.foo, t1.bar, t2.baz
from tableone t1, tabletwo t2 where 
t1.foo = t2.foo and
t1.bar = t2.bar;

    Chemin Direct Insert de pointe, comme l'a suggéré 

    insert /*+ append */ into tracker (id, foo, bar, baz)
select trackersequence.nextval, t1.foo, t1.bar, t2.baz
from tableone t1, tabletwo t2 where 
t1.foo = t2.foo and
t1.bar = t2.bar;
    • :+1 ce sera beaucoup plus rapide
    • :Chemin Direct insérer astuce permettra également d'augmenter le rendement
    • Si je peux éviter la surcharge du curseur, hourra. Ce processus s'exécute plusieurs fois par jour. Je vais tester cela plus tard aujourd'hui / la première chose le matin.
    • essayez avec insert/*+append*/ into tracker....
    InformationsquelleAutor Mike
  3. 1
    DECLARE    

CURSOR foo_cur 
IS SELECT * FROM tableOne TO  
    WHERE EXISTS (SELECT * FROM tableTwo tt  
                   WHERE TO.FOO = TT.FOO  
                   AND TO.BAR = TT.BAR);  --THIS TAKES 5 MINUTES (66 MILLION ROWS)  

TYPE foo_nt IS TABLE OF tableOne%ROWTYPE;
v_foo_nt foo_nt;

 BEGIN

  OPEN foo_cur ;
  LOOP
  FETCH foo_cur BULK COLLECT INTO v_foo_nt LIMIT 1000;

       FORALL  i IN v_foo_nt.FIRST..v_foo_nt.LAST  
         INSERT INTO tracker t  
           (id,foo,bar,baz)  
          VALUES(trackerSequence.nextval, v_foo_nt(i).foo,v_foo_nt(i).bar,v_foo_nt(i).baz);  

EXIT WHEN foo_cur%NOTFOUND;  
END LOOP; 
  CLOSE foo_cur;  
  COMMIT;  
 END;  
END;
    • testé ,mais il ne fonctionnera que si vous avez 11g version ,et bien meilleure réponse que moi dans les 2 autres réponses en termes de performances
    • Ok je vais essayer cela à 10 ans et à voir si fonctionne. Je sais 9 apporté beaucoup de changements.
    • Si vous allez faire un BULK COLLECT vous devriez vraiment avoir un LIMIT. L'extraction de 66 millions de lignes de données dans la base de données de la PGA va créer des problèmes de performances. Si chaque ligne est à seulement 100 octets, par exemple, qui nécessiterait ~6 GO de la PGA de l'espace. La plupart des serveurs de bases de données ne vont pas être configuré pour permettre que beaucoup de la PGA de l'espace pour être consommé. Si vous arrive d'être sur une machine qui a des tonnes de RAM allouée à Oracle, un processus de mastication jusqu'à 6 GO de PGA va avoir un sérieux rendement négatif des répercussions sur d'autres processus dans la base de données.
    • Justin,je vais mettre à jour ma réponse basée sur
    • Comment pouvons-nous déterminer la LIMITE ?,Je veux dire que ça dépend du serveur ,donc en vertu de ce paramètre, qui est la valeur sûre de la LIMITE.
    • Approprié LIMIT n'est pas vraiment dépendre sur le serveur. Le plus efficace LIMIT est généralement quelque part entre 100 et 1000. Vous pouvez essayer différents choix pour votre processus particulier s'il est peu probable que la différence va être énorme. Une "LIMITE" de 100 élimine 99% des contexte des changements, une LIMIT de 1000 élimine 99,9% des le cadre des quarts de travail. Il est peu probable que vous allez obtenir beaucoup plus de gain de performance de l'élimination de ce dernier 0,1% de la contexte de changements et d'augmenter la quantité de RAM que vous utilisez est susceptible d'ajouter plus de vos frais généraux que vous gagnez.
    • justin , j'ai appris quelque chose de nouveau , je vais sûrement le communiquer à mes collègues:)
    • Je dois être en manque de quelque chose que je reçois un volume in-erreur de liaison

    InformationsquelleAutor Gaurav Soni
  4. 0

    Premier - comment pouvez-vous optimiser votre PL/SQL de la performance:

    • Désactiver les index et d'autres contraintes sur les tables cible avant de commencer votre charge et de les réactiver après vous avez terminé
    • Ne pas s'engager à la fin - ont commettre des points afin de libérer des segments d'annulation

    De deuxième ne pas faire l'insert avec PL/SQL. Utilisation BulkLoading (comme l'a déjà suggéré par certains commentaires). vous pouvez facilement trouver beaucoup d'infos sur BulkLoading si vous faites une recherche Google pour "oracle sql loader"

    • Je ne pense pas que sql loader est approprié dans ce cas, mais je vais vérifier.
    InformationsquelleAutor YePhIcK
  5. 0

    J'ai presque toujours reçu de meilleures performances avec ces données en vrac inserts en utilisant une combinaison de BITMAP INDEXES et à l'aide d'un chef de projet (Chemin Direct de la Charge) c'est à dire avec l'utilisation de l'indice de /*+ APPEND+/.

    Je pourrait également supposer que ce serait bon d'index sur les deux TT.FOO, TT.BAR et TO.FOO, TO.BAR.
    Donc somethink comme

    INSERT /*+ APPEND*/
  INTO TRACKER T
SELECT trackerSequence.nextval, to.foo,to.bar,to.baz
  FROM tableOne to  
  WHERE EXISTS (SELECT 'x' 
                  FROM tableTwo tt  
                 WHERE TO.FOO = TT.FOO  
                   AND TO.BAR = TT.BAR);

    Aussi Garder à l'esprit que la EXIST clause peut mordre retour dans certaines situations. Donc, vous souhaiterez peut-être utiliser simple, les jointures externes.

    INSERT /*+ APPEND*/
  INTO TRACKER T
SELECT DISTINCT trackerSequence.nextval, to.foo,to.bar,to.baz
  FROM tableOne to , tableTwo tt
  WHERE TO.FOO = TT.FOO  
    AND TO.BAR = TT.BAR;

    Souviens - chef de projet (chemin Direct de la charge) ne sera pas toujours d'améliorer les performances de votre requête, il peut l'améliorer (ou aide) si votre table est correctement partitionné.

    Essayer d'Expliquer le plan sur ces requêtes afin de trouver la meilleure.
    Aussi, (comme l'un de la réponse mentionne déjà) ne pas s'engager à la fin, mais ne pas s'engager sur chaque dossier soit. Il est suggéré d'utiliser un point de validation quelque chose de similaire lors de l'utilisation LIMIT XXXX tout BULK COLLECTing. Votre commit points régissent la taille de votre ROLLBAK segments sont. Vous pouvez également utiliser votre coutume de Commettre des points (aussi simple que comptoir) de la procédure (c'est à dire dans un PLSQL BLOC).

    Les performances de la requête dépend aussi de la HWM de votre table (pour être précis), il faudrait presque toujours envie de jouer sous le HWM de la table. Alors que TRUNCATE sur le TRACKER table aidera à atteindre cet objectif, les données précédentes, il va être perdu, donc ce ne pouvait guère être une solution ici. Suivre cette AskTom lien pour apprendre comment trouver HWM.

    • Une image bitmap d'index est généralement pas un bon ajustement pour les tables qui sont changés souvent. Une table nommée "tracker" semble indiquer un volume élevé de l'activité d'écriture et ce n'est certainement pas une bonne idée d'avoir une image bitmap d'index sur eux.
    • Un pourquoi serait-elle? Qu'entendez-vous par "les Tables sont bien souvent changé"? j'ai utilisé de la Bri sur les tables depuis longtemps et jamais eu de problèmes. Bien sûr, vous devriez avoir les connaissances de base à propos de l'indexation et de la bitmap d'index avant de créer un juste pour le plaisir de l'amélioration de la performance.
    • Je ne suis pas vraiment sûr où vous êtes l'obtention de la nécessité d'une bitmap index du tout dans cette notice processus. L'OP n'est pas n'importe quelle lit dans le suivi de la table... Oracle de Bitmap d'Index sont principalement utile pour le bitmap à l'opération de fusion pour la sélection d'une liste de rowids pour une utilisation dans un join ou un ensemble de résultats.
    • Le problème est que les insertions simultanées/mises à jour sur la table à souffrir de conflit avec les index bitmap en raison de la mise à jour d'une ligne peut verrouiller 1000 lignes pour d'autres mises à jour (en raison du fait que l'un bloc dans la bitmap d'index couvre beaucoup de lignes). Aussi la bitmap d'index se détériore lorsqu'd'être mis à jour très souvent. La recommandation habituelle est de les utiliser dans un entrepôt de données environmnet et créer après chargement des tables. Dans le manuel: "La représentation interne des bitmaps est le mieux adapté pour les applications à faible niveau de transactions simultanées, telles que l'entreposage de données"
    • Je parlais de la mauvaise table là. La façon dont la question est posée TableOne et TableTwo contient de façon exponentielle de grandes quantités de données, et il ne ressemble à aucun temps réel table transactionnelle (plus comme DW tables), ayant Bitmap d'index sur les tables "pourrait" peut-être augmenter les performances ici. Mais ce sont juste des hypothèses. Même un simple chef de projet pourrait ne pas aider beaucoup de performance sage si bon partitionnement n'est pas là. Mais encore une fois, ce sont des hypothèses. Aussi je ne m'attends pas à ce INSERT procédure pour être placé sur une application en temps réel, de son mieux adaptés que d'une 'nuit' par lot.
    InformationsquelleAutor Annjawn
  6. 0

    J'ai trouvé ce qui suit va faire de 130 millions d'encarts dans environ 49 minutes. 

    INSERT INTO tracker t  
    SELECT * FROM tableOne to  
        WHERE NOT EXISTS (SELECT * FROM tableTwo tt  
                       WHERE TO.FOO = TT.FOO  
                       AND TO.BAR = TT.BAR);
    InformationsquelleAutor Woot4Moo