Forum |  HardWare.fr | News | Articles | PC | Prix | S'identifier | S'inscrire | Shop Recherche
919 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [oracle PL/SQL] Problème de mis à jour

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[oracle PL/SQL] Problème de mis à jour

n°1587749
glwad
Posté le 17-07-2007 à 16:41:56  profilanswer
 

Bonjour à tou(te)s,
 
Environnement :  Oracle 8i/Toad/Unix
 
J'essaye de mettre à jour environ 800.000 lignes dans une table de 17.000.000 de lignes environ. La mise à jour s'effectue suivant deux champs différents mais, sur des lignes comprises dans le même intervalle de temps. Pour le faire, j'utilise 2 curseurs A et B : le curseur A met à jour plusieurs champs et le curseur A un seul champ (déjà mis à jour par le curseur A => écrasement le cas échéant).  
Le curseur A retourne plus de ligne que le curseur B; les deux curseurs retournent plus de lignes que le nombre de ligne de la plage à mettre à jour. J'ai plusieurs index (Bitmap et B-tree) sur la table; dont des index sur les deux clés de mise à jour.  Le curseur B est beaucoup plus complexe que le curseur A
 
Mon problème est le suivant : la mise à jour avec le curseur A prend entre 18mn et 1h30 en fonction de la charge du réseau. Tandis qu'avec le curseur B, j'en ai pour 18h minimum!!!  
Toutes les pistes pour comprendre d'où vient le problèmes sont les bienvenues.
 
Merci pour votre aide
 
Curseur A

Citation :

SELECT VR, PA, VK,
     AN,VER, SEQ_P,
     SEQ_V,  LPAD(SUBSTR(SIR,1,14),14,'0') SIR,
     IND,ABREGE,  SUBSTR(NIVEAU,1,4) NIVEAU,
     AUS
    FROM  CORR  
    ORDER BY VER


 
Curseur B

Citation :

SELECT AN,ABREGE
    FROM   CORR C
    WHERE  LOE IS NULL
    AND    AUS=(SELECT MAX(AUS)
             FROM CORR C2
        WHERE C2.ANLAGE=C.ANLAGE
       )

mood
Publicité
Posté le 17-07-2007 à 16:41:56  profilanswer
 

n°1587796
MagicBuzz
Posté le 17-07-2007 à 17:39:19  profilanswer
 

Est-ce que tu peux nous détailler ton problème "de plus haut niveau" ?
 
En gros, pourquoi utilises-tu des curseurs ? Effectivement, les curseurs sur de tels volumes c'est un perf killer monstrueux.

n°1587801
glwad
Posté le 17-07-2007 à 17:45:05  profilanswer
 

C'est une méthodologie générale adoptée à cause des volumetries et des espaces disponibles pour les tablespaces. Ainsi, il est possible de faire des commit intermédiaires pendant l'update.
J'ai cherché une doc qui explique le mécanisme des curseurs (comment oracle les gère : création d'une table temporaire (vue) ou reexécution du script pour chaque ligne lue), si tu as des liens à ce propos, ça m'interesse.
 
merci.
 
Au cas où, mon code ressemble à ceci:

Citation :

OPEN A();
      LOOP
         BEGIN
            FETCH A INTO ListVer;
            EXIT WHEN A%NOTFOUND;
            lu := lu + 1;
            traite := lu;  
 
         UPDATE AGR SET VR=ListVer.VR,
        PA=ListVer.PA,
         VK=ListVer.VK,
        SEQ_P =ListVer.SEQ_P ,
        SEQ_V=ListVer.SEQ_V,
        SIR=ListVer.SIR,
        IND=ListVer.IND,
        ABREGE=ListVer.ABREGE,
        NIVEAU=ListVer.NIVEAU
      WHERE VER=ListVer.VER
               AND   APPL IN ('S','Q')
               AND ( ( MOIS_R >= pMOIS  AND  ANNEE_R = pANNEE)
           OR
     ( ANNEE_R > pANNEE )
        )
       AND ( (ANNEE_R < 2050)
             OR
            (ANNEE_R = 2050) AND (MOIS_R < 99)
           );
 
 ecrit := ecrit + 1;
        IF MOD (ecrit, 10) = 0 THEN COMMIT; END IF;
   EXCEPTION
            WHEN OTHERS
            THEN
               alim_generale.msg (nom2, phase, 'LOOP', 'SG'||SQLCODE, ListVer.vertrag);
         END;
   END LOOP;
CLOSE A;
 
   lu:=0;
 
   
OPEN B();
      LOOP
         BEGIN
            FETCH BINTO ListB;
            EXIT WHEN B%NOTFOUND;
            lu := lu + 1;
            traite := lu;
 
         UPDATE G_AGR SET   ABREGE=ListB.ABREGE
     WHERE AN=ListB.AN
               AND ( ( MOIS_R >= pMOIS  AND  ANNEE_R = pANNEE)
           OR
     ( ANNEE_R > pANNEE )
        )
       AND ( (ANNEE_R < 2050)
             OR
            (ANNEE_R = 2050) AND (MOIS_ < 99)
           );
 
           lu := lu + 1;
            IF MOD (lu, 100) = 0 THEN COMMIT; END IF;
  EXCEPTION
            WHEN OTHERS
            THEN
               alim_generale.msg (nom2, phase, 'LOOP', 'AB'||SQLCODE, ListB.AN);
      END;
   END LOOP;
CLOSE B;


Message édité par glwad le 17-07-2007 à 17:46:17
n°1587846
MagicBuzz
Posté le 17-07-2007 à 20:16:14  profilanswer
 

Etrange ton truc.
 
Juste comme ça, je vois que tu n'update pas les mêmes tables, mais que tu utilises pour ainsi dire les mêmes critères.
 
N'aurais-tu pas tout simplement un problème d'indexes ?
 
En tout cas, je trouve ça très étrange que ça mette autant de temps, même pour le premier.
 
Certes, la volumétrie est conséquente, mais tout de même :/
T'as beaucoup d'accès concurrents ?
 
PS : Que se passe-t-il pour les accès concurrents lorsque vous commitez ? Parceque commiter en plein milieu d'un traîtement, c'est se retrouver avec une base incohérente... Vous ne risquez pas d'avoir de problèmes ?

n°1587950
glwad
Posté le 18-07-2007 à 09:36:42  profilanswer
 

une petite erreur s'est glissée dans mon code: j'update la même table. le but est de donner une valeur par défaut au champ "ABREGE"; et ensuite, lorsque c'est possible, lui donner sa valeur finale.
 
A priori, je n'ai pas d'acces concurrents à la BDD pendant les traitements. Les scripts sont séquenciels et pour mes tests, je demande à avoir la base à mon unique disposition.
 
J'ai pensé aux bitmaps index mais la piste ne m'a mené nulle part:
- suppression des bitmaps index avant insertion=>aucun résultat
- comment expliquer cette différence de temps entre les deux mises à jour :/
 
Je n'y comprend pas grand chose!! :( :( :( :(

n°1587955
couak
Posté le 18-07-2007 à 09:44:20  profilanswer
 

les index bitmaps sont performants pour les colonnes à faible cardinalité (i.e. peu de valeurs changeantes, genre champ sexe avec H/F)
 
regarde plutôt le plan d'exécution des requêtes

n°1587978
MagicBuzz
Posté le 18-07-2007 à 10:16:22  profilanswer
 

(oui, moi je parlais d'index tout courts surtout, genre est-ce que la requête tape bien dans les bons indexes ? ne peux-tu pas rajouter un filtre -même inutile et redondant- afin d'aider Oracle à utiliser le bon index ? etc.)
 
Exemple pratique :
 
Table EVE (codsoc, achvte, typeve, numeve, sigtie)
Index PK_TIE(codsoc, achvte, typeve, numeve)
 
Le TYPTIE "FAV" forcément de ACHVTE "V'.
Je peux donc être tenté de rechercher :
 
SELECT NOMTIE
FROM EVE
WHERE CODSOC = 2
AND TYPEVE = 'FAV'
 
=> 544 ms
 
Si je rajoute un filtre redondant sur ACHVTE = "V" afin de m'assurer qu'Oracle va bien taper dans l'index unique, et ne pas faire de nested loops dedans
 
SELECT NOMTIE
FROM EVE
WHERE CODSOC = 2
AND ACHVTE = 'V'
AND TYPEVE = 'FAV'
 
=> 7 ms

Message cité 1 fois
Message édité par MagicBuzz le 18-07-2007 à 10:26:07
n°1587982
glwad
Posté le 18-07-2007 à 10:21:19  profilanswer
 

Le plan d'exécution (celui que TOAD retourne) est le suivant:

Citation :


Operation Object Name                            Rows   Bytes   Cost Object Node In/Out PStart PStop
 
UPDATE STATEMENT Optimizer Mode=CHOOSE     1               1                            
  UPDATE PILOT.AGR                                
    TABLE ACCESS BY INDEX ROWID PILOT.AGR     1      18      1                            
      INDEX RANGE SCAN PILOT.IDX_AGR_AN_AB    1               2


 
Il me semble qu'il est plutôt bon. C'est porquoi je regardais vers les acces disque. Les champs sur lesuel on trouve les bitmap index ont effectivement peu de cardianlité mais, lors de l'insertion de données est ce qu'il ne prend pas plus de temps pour recalculer ces index?
 
J'essaye de rajouter des filtres redondants afin de voir si le plan est modifié


Message édité par glwad le 18-07-2007 à 10:23:18
n°1587989
MagicBuzz
Posté le 18-07-2007 à 10:27:51  profilanswer
 

Il index quoi ton index IDX_AGR_AN_AB ?

n°1587992
MagicBuzz
Posté le 18-07-2007 à 10:30:40  profilanswer
 

Genre il te faut ici un index sur :
 
G_AGR (AN, ANNEE_R, pANNEE, MOIS_R, pMOIS);
 
-en me basant sur ta requête que tu annonces comme foireuse, donc à la base rien n'est moins sur-

mood
Publicité
Posté le 18-07-2007 à 10:30:40  profilanswer
 

n°1588019
glwad
Posté le 18-07-2007 à 10:53:27  profilanswer
 

IDX_AGR_AN_AB es un index double sur (AN,AB) de la table AGR
pANNE et pMOIS sont des variables qui me permettent de modifier la période (c'est un script reccurent => 18h d'exécution ça ne le fait pas trop)
Il existe un index bitmap sur le couple (ANNEE_R,MOIS_R). J'ai testé en créant un index sur ANNEE_R et MOIS_R, mais aucun n'est pris en compte pendant la requête

n°1588032
couak
Posté le 18-07-2007 à 11:05:57  profilanswer
 

MagicBuzz a écrit :

(oui, moi je parlais d'index tout courts surtout, genre est-ce que la requête tape bien dans les bons indexes ? ne peux-tu pas rajouter un filtre -même inutile et redondant- afin d'aider Oracle à utiliser le bon index ? etc.)


on peut aussi utiliser des "hints" pour forcer oracle à utiliser des index
exemple :

Code :
  1. select /*+ index(nom_schema.nom_index) */ ... from nom_schema.nom_table

n°1588040
couak
Posté le 18-07-2007 à 11:09:33  profilanswer
 

glwad a écrit :

IDX_AGR_AN_AB es un index double sur (AN,AB) de la table AGR
pANNE et pMOIS sont des variables qui me permettent de modifier la période (c'est un script reccurent => 18h d'exécution ça ne le fait pas trop)
Il existe un index bitmap sur le couple (ANNEE_R,MOIS_R). J'ai testé en créant un index sur ANNEE_R et MOIS_R, mais aucun n'est pris en compte pendant la requête


si tu veux forcer l'utilisation de l'index, cf post précédent : utilises un hint
 
pour ton index, regarde si tu n'as pas besoin de le reconstruire, la recommandation étant :

Rebuild the index when :
- deleted entries represent 20% or more of the current entries
- the index depth is more then 4 levels.
Possible candidate for bitmap index :
- when distinctiveness is more than 99%

n°1588042
MagicBuzz
Posté le 18-07-2007 à 11:09:59  profilanswer
 

glwad a écrit :

IDX_AGR_AN_AB es un index double sur (AN,AB) de la table AGR
pANNE et pMOIS sont des variables qui me permettent de modifier la période (c'est un script reccurent => 18h d'exécution ça ne le fait pas trop)
Il existe un index bitmap sur le couple (ANNEE_R,MOIS_R). J'ai testé en créant un index sur ANNEE_R et MOIS_R, mais aucun n'est pris en compte pendant la requête


une même requête ne peut utiliser qu'un index par table.
 
crée donc un index dédié à ton batch (unique si c'est possible) sur AGR (AN, ANNEE_R, MOIS_R)
 
Parceque là effectivement, vu le volume, seule une partie de ton index est utilisée (j'imagine que "AN" n'est pas du tout significatif) donc c'est normal que ce soit très lent, vu que la plupart des filtres portent ensuite sur des champs non indexés.

n°1588044
couak
Posté le 18-07-2007 à 11:10:47  profilanswer
 

ah euh je viens de penser à un truc : qu'est ce qui prend le plus de temps ? la recherche des informations via les select ou la mise à jour des données avec l'update ?

n°1588054
glwad
Posté le 18-07-2007 à 11:20:08  profilanswer
 

couak a écrit :

ah euh je viens de penser à un truc : qu'est ce qui prend le plus de temps ? la recherche des informations via les select ou la mise à jour des données avec l'update ?


 
Je ne maitrise pas le fonctonnement des curseurs mais, je dirai que c'est l'update qui met du temps. En fait, lorsque j'exécute le "select" du curseur sous TOAD, j'ai le résultat très rapidement. Comme l'update est la combinaison  de la boucle sur le curseur et de l'update, je me demande si  cette boucle ne prend pas aussi du temps.
 
Sinon, je vais créer l'index unique sur AN,ANNEE_R et MOIS_R, forcer son utilisation avec un hint. Je teste ça des tout de suite.

n°1588063
couak
Posté le 18-07-2007 à 11:27:14  profilanswer
 

bon bah voilà un début de piste :)
- combien as-tu d'index pour ta table ? si tu en as 58, faire un update imposerais de faire des mises à jours dans 58 index, ca peut prendre du temps :)
- essaie de commiter moins souvent, si t'as les moyens d'avoir un tablespace d'undo suffisamment large, pourquoi s'amuser à tout le temps commiter ?

n°1588076
glwad
Posté le 18-07-2007 à 11:44:32  profilanswer
 

couak a écrit :

bon bah voilà un début de piste :)
- combien as-tu d'index pour ta table ? si tu en as 58, faire un update imposerais de faire des mises à jours dans 58 index, ca peut prendre du temps :)
- essaie de commiter moins souvent, si t'as les moyens d'avoir un tablespace d'undo suffisamment large, pourquoi s'amuser à tout le temps commiter ?


LOL; non, je n'ai pas 58 mais presque : 21 (dont 8 bitmap). Avant, le script supprimait tous les index pour les recreer après l'insertion (truncate/insert); mais la création des index à elle seule prend 5h.
Malheureusement, je n'ai pas la main sur le paramétrage de la BDD pour augmenter la taille des tablespaces (de plus, je ne connais pas du tout le sujet). Par contre, j'ai encore de la marge pour la fréquence des commit. je vais les augmenter avant de faire le test.
 
Question : pourquoi le premier update (qui se fait sur la même table) ne réagit pas pareil???!!??!!

n°1588225
casimimir
Posté le 18-07-2007 à 14:15:03  profilanswer
 

il faut voir le contexte dans lequel tu travailles mais je vais t'expliquer la manière dont je fais cela dans des cas de grosses volumétries dans le cadre d'un datawarehouse.
pour ce genre de cas la premiere chose que je fais généralement c'est juste créer une table avec la primary key de ma table, et les champs dont j'aurai besoin de critère pendant l'update, puis je fais l'update a mon aise dans cette table non pas avec un curseur mais juste un update classique avec éventuellement des sous select a la pelle ou bien un appel a une stored procedure mais ce sera toujours mieux. des que j'ai mes valeurs je remet a ma table d'origine avec les valeurs de ma sous-table.
il faut voir ton enrinnement mais je fais cette méthode actuelelment pour des update de 4000000 sur une table de 50000000 et cela me prend +/- 10mins.
perso les curseur j'évite a moins de devoir vraiment faire de la programmation derrire ou bien pour l'aspect transactionnel.

n°1588236
MagicBuzz
Posté le 18-07-2007 à 14:21:35  profilanswer
 

Poste les deux updates "réels".
 
Là dans ce que t'as posté, c'est absolument pas les mêmes fitres, donc pas du tout le même index utilisé.
 
T'as créé l'index que je t'ai indiqué ?

n°1588288
glwad
Posté le 18-07-2007 à 15:32:19  profilanswer
 

Je ne peux pas poster le code réel (mon CP ne le voit pas d'un très bon oeil). La seule modification apportée au code postée concerne le nom des tables et des champs.
Effectivement, le filtre n'est pas exactement le même : la mise à jour se fait suivant deux champs différents (ver et AN). Dans la période considérée, APPL est toujours dans la liste ('S','Q')
 
J'ai crée l'index. le programme est en cous d'exécution. Je ferai un retour sur le gain de temps.
 
Merci Casimir. vu que ta volumetrie est à peu près 10 fois suprieure à la mienne, je crois que tous les scripts sont à revoir entierement :( . Est ce que l'utilisation d'une table temporaire permet de s'affranchir du problème de taille du  tablespace?

n°1588329
MagicBuzz
Posté le 18-07-2007 à 16:23:51  profilanswer
 

En gros, pour ton index, tu dois reprendre l'intégralité des champs des filtres qui se trouvent dans le WHERE du UPDATE.

n°1588618
glwad
Posté le 19-07-2007 à 09:41:50  profilanswer
 

J'ai fait l'index sur les 3 champs dont dépendent le filtre (AN,ANNEE_R,MOIS_R). je reste toujours sur des bases de 18h (j'ai stoppé l'exécution ce matin).
J'ai aussi essayé de modifier le curseur B afin de pouvoir mettre à jour suivant le champ VER; la requête s'exécute en ce moment mais, je ne suis vraiment pas optimiste :( :( :(

n°1588633
casimimir
Posté le 19-07-2007 à 10:24:52  profilanswer
 

et tu dois faire ca souvent ou c'est du one shot?

n°1588636
glwad
Posté le 19-07-2007 à 10:41:03  profilanswer
 

casimimir a écrit :

et tu dois faire ca souvent ou c'est du one shot?


Malheureusement, c'est un traitement hebdomadaire qui s'insère dans la procédure d'alimentation de la BDD (d'où le besoin de rapidité)

n°1588642
Profil sup​primé
Posté le 19-07-2007 à 10:57:39  answer
 

Ca n'arrange rien avec le Bulk Collect ?

n°1588661
couak
Posté le 19-07-2007 à 11:24:36  profilanswer
 

glwad a écrit :

J'ai fait l'index sur les 3 champs dont dépendent le filtre (AN,ANNEE_R,MOIS_R). je reste toujours sur des bases de 18h (j'ai stoppé l'exécution ce matin).
J'ai aussi essayé de modifier le curseur B afin de pouvoir mettre à jour suivant le champ VER; la requête s'exécute en ce moment mais, je ne suis vraiment pas optimiste :( :( :(


je ne pense pas qu'ajouter des filtres aiderait : si le select est rapide c'est qu'il n'y a pas de pb pour retrouver les données (et donc suffisamment d'index)
là c'est un pb d'update, donc j'aurais tendance à dire qu'il y a trop d'index à mettre à jour durant la phase de mise à jour

n°1588731
glwad
Posté le 19-07-2007 à 12:14:56  profilanswer
 

couak a écrit :


je ne pense pas qu'ajouter des filtres aiderait : si le select est rapide c'est qu'il n'y a pas de pb pour retrouver les données (et donc suffisamment d'index)
là c'est un pb d'update, donc j'aurais tendance à dire qu'il y a trop d'index à mettre à jour durant la phase de mise à jour


Est ce que lors d'une mise à jour, les index sont recalculés même si ce n'est pas le champ indexé qui est mis à jour?
 
Je vais faire le test en supprimant tous les index (et plus seuleument les index bitmap). Je me documente aussi sur le Bulk Collect afin de voir comment l'implementer.


Message édité par glwad le 19-07-2007 à 12:15:18
n°1589134
glwad
Posté le 20-07-2007 à 09:27:03  profilanswer
 

Impossible d'utiliser le BULK COLLECT: problème d'espace. Après 7h de traitement, il a planté avec le message d'erreur:

Citation :

ORA-04030: mémoire de traitement manquante lors d'affectation de 96716 octets (callheap,DARWIN)

n°1589285
casimimir
Posté le 20-07-2007 à 12:50:05  profilanswer
 

decoupe ton update en plus petite partie pour pouvoir commiter comme tu le sens

n°1589532
glwad
Posté le 20-07-2007 à 17:04:39  profilanswer
 

casimimir a écrit :

il faut voir le contexte dans lequel tu travailles mais je vais t'expliquer la manière dont je fais cela dans des cas de grosses volumétries dans le cadre d'un datawarehouse.
pour ce genre de cas la premiere chose que je fais généralement c'est juste créer une table avec la primary key de ma table, et les champs dont j'aurai besoin de critère pendant l'update, puis je fais l'update a mon aise dans cette table non pas avec un curseur mais juste un update classique avec éventuellement des sous select a la pelle ou bien un appel a une stored procedure mais ce sera toujours mieux. des que j'ai mes valeurs je remet a ma table d'origine avec les valeurs de ma sous-table.
il faut voir ton enrinnement mais je fais cette méthode actuelelment pour des update de 4000000 sur une table de 50000000 et cela me prend +/- 10mins.
perso les curseur j'évite a moins de devoir vraiment faire de la programmation derrire ou bien pour l'aspect transactionnel.


Finalement, je vais m'appuyer sur ta méthode afin de faire un delete/insert dans la table cible.  Je stocke la ligne entière (avec la donnée à mettre à jour modifiée) dans une table temporaire; je supprime les lignes de la table principale qui sont listées dans la table temporaire. Et j'insère toutes les lignes de la table temporaire dans la table principale. J'ai fait un test et j'ai des résultâts au bout de 2h. Ca reste long mais, c'est mieux que 18h. Ca sera une solution de rechange en attendant mieux (A condition que mon CP accepte).
 
Merci à tou(te)s pour votre aide. et Bon week end

n°1609999
glwad
Posté le 10-09-2007 à 11:54:42  profilanswer
 

Bonjour,
 
Je reviens sur ce problème après quelques semaines de tests.
J'ai eu une autre explication pour le second curseur trop long : ce sont les mêmes lignes qui sont mises à jour par les deux curseurs. Du coup, oracle n'a pas eu le temps de mettre ses fichiers de controle à jour après le premier update => il rame pour le second parceque pour chaque ligne, il met d'abord l'environnement à jour. J'ai donc modifié ma procédure afin d'effectuer un seul update. Pour le moment, les résultâts sont aléatoires. La premiere exécution a pris 10mn et la seconde (à une semaine d'écart) 21h!!!!!!!

mood
Publicité
Posté le   profilanswer
 


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [oracle PL/SQL] Problème de mis à jour

 

Sujets relatifs
résoluProbleme de chargement de dll
Problème affichage FF / IE7Problème sur "€" et" "-" avec FPDF (génération PDF)
Données tronquées dans SPOOL SQLProblème de variable dans un ActiveChart.SetSourceData Source
problème lecture d'un fichier textePlusieurs flash dans une page html - probleme avec swfObject
Problème boucle à droite!Probleme utilisation pointeur et reference
Plus de sujets relatifs à : [oracle PL/SQL] Problème de mis à jour


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR