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

 
Curseur B

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.

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:

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 ?

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!!

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

(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

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

 
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é

Il index quoi ton index IDX_AGR_AN_AB ?

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-

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

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

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 :

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.

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.

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???!!??!!

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.

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é ?

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?

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

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

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é)

Ca n'arrange rien avec le Bulk Collect ?

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.

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

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

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

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!!!!!!!