Discussion :

[JeanYvette]

Bonjour bonjour

J'aurais besoin de votre avis sur une optimisation de procèdure stockée. Celle-ci est assez longue donc je n'ai pas pris le temps de l'anonymiser mais ma demande est plus sur le théorique :

Je dois récupérer une procédures qui fait plusieurs actions mais à chaque fois du même type :

- 4 Curseurs différents avec, à chaque fois, une requête sur au moins 3 tables. Des filtres utilisant les index.
- 4 Boucles (spoiler alert, une par curseur) parcourant le curseur, faisant au moins un IF sur une colonne du curseur pour y changer des données
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
     for ligne in curseur1
     loop
           IF ligne.macolonne = 'FI' 
           THEN
                   ligne.registre = 0
           END IF

- Dans chacun des curseurs, un update puis un delete.
L'update met à jour des tables produits, le delete supprime le produit mis à jour d'une table "d'attente de mise à jour".


Le souci c'est que le code est bof provoquant ainsi des millions de delete/update alors qu'il n'y a clairement pas autant de lignes.

Pour l'optimisation, et ca va être là où j'ai besoin d'un avis, j'imaginais 3 étapes :

1) Revoir les requêtes SELECT qui sont dans les curseurs
2) Virer les clauses IF pour les mettre dans des CASE directement dans les SELECT
3) Virer les curseurs et les loop pour passer à un update avec select et la petite option RETURNING comme sur l'exemple suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  UPDATE t1
  SET    description = description
  RETURNING id BULK COLLECT INTO l_tab;
 
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;

Et me servir de la boucle pour la clause DELETE.

C'est sur le 3éme point que j'ai besoin de votre avis. Sur le papier, virer les curseurs pour passer par une requête directe me semble bien, surtout qu'en utilisant le RETURNING ensuite je peux enchainer le delete associé sans (normalement) de difficulté.


Est-ce une idée à la noix ?

Bisous bisous

[Ikebukuro]

Salut,

Je note
"le code est bof provoquant ainsi des millions de delete/update alors qu'il n'y a clairement pas autant de lignes."

Pour l'optimisation, et ca va être là où j'ai besoin d'un avis, j'imaginais 3 étapes :
1) Revoir les requêtes SELECT qui sont dans les curseurs"

Avant d'aller plus loin avec la clause Returning, le bulk collect, je te conseille de te focaliser sur le point 1 car si tu fais sauter des millions d'Update et de Delete, ton code devrait déjà être fortement plus rapide.
Reviens vers nous une fois le point 1 éclairci mais ça devrait déjà répondre à ton besoin.

[vanagreg]

Bonjour,

Oui c'est la bonne démarche que tu veux entreprendre. Les parcours de curseur pour la mise à jour de données c'était à la mode au siècle dernier. C'est en effet des instructions DML directes qu'il faut utiliser, avec des CASE dans les select pour filtrer/calculer des valeurs. Tu peux utiliser un RETURNING BULK COLLECT pendant l'update pour stocker des id. Cependant s'il y a des millions de lignes attention à ne pas exploser la PGA lors du parcours de la collection.
Sinon si tes 4 curseurs utilisent plus ou moins les mêmes données, tu peux utiliser une table temporaire (un global temporary table) pour y stocker les informations source qui serviront aux updates des données cibles.

[JeanYvette]

Merci pour vos réponses,

En effet, juste modifier les selects ne suffit pas même si j'ai pu bien améliorer les performances de la procédures stockée. Je voulais donc virer les curseurs pour faire des updates sauf que là, tout de suite, soit mon cerveau a fondu soit je suis débile…

Mon update concerne plusieurs colonnes et, dans la clause where, la jointure entre la table updaté et les colonnes du curseurs sont multiples du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
UPDATE maTable set macolonne = 'O', maColonneDeux = 'traitement', dateJour = sysdate
WHERE idcols = curseurboucle.id AND colonneTruc = curseurboucle.truc [...]

Je pourrais remplacer cela par la clause MERGE mais mon souci est le suivant :
Si je fais un merge avec l'update dans la clause WHEN MATCHED, il faut que j'appelle une procédure stockée pour gérer en même temps le delete que j'ai suite à l'update => Niveau perf, j'ai peur que ça pue

J'essaye de faire dans un update comme je l'avais imaginé au début, avec l'exemple que j'avais donné mais là, je sais pas, je coince. Comment je peux faire un update où je joins plusieurs colonnes de la clause WHERE de l'update aux colonnes d'un select (le select me sert en fait à savoir quelles lignes modifier en fonction de diverses colonnes qui servent de clés multiple)

J'espère avoir été assez clair dans mes explications, j'ai un petit doute mais on verra...

Bisous bisous

[vanagreg]

Bonjour,

Avec un UPDATE on utilise souvent l'opérateur EXISTS pour sélectionner les lignes à mettre à jour:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
UPDATE maTable t set macolonne = 'O', maColonneDeux = 'traitement', dateJour = sysdate
WHERE EXISTS (select 1 from tablesource
                      where t.idcols = tablesource.id 
                         AND t.colonneTruc = tablesource.truc
                      ...
                    ) ;

Sinon quand l'update nécessite des valeurs des données sources, alors j'utilise MERGE. La syntaxe est beaucoup plus souple. Puisque tu veux faire un DELETE, sache qu'il est possible d'ajouter une clause DELETE à la clause MATCHED d'un MERGE. Ceci te permet de supprimer des lignes pré ou post UPDATE (tu ajoutes une clause WHERE à la clause DELETE pour sélectionner les lignes que tu veux).

[JeanYvette]

Ah mais oui mais c'est bien sûr... J'avais pas pensé au EXIST, je me déteste si fort

Oui pour le merge, totalement d'accord. Mais la limite c'est que dans le MATCHED, je ne peux pas faire un update ET un delete, à moins que j'ai loupé quelque chose depuis ?

[vanagreg]

Si, tu peux faire le UPDATE et le DELETE. Par exemple j'avais écrit une procédure qui utilisait un MERGE pour fusionner des intervalles de dates qui se chevauchent. La clause UPDATE mettait à jour la date de fin, et la clause DELETE supprimait les lignes devenues superflues (je mettais une date spéciale pour les identifier pour le DELETE).

[JeanYvette]

Oh, tu saurais me montrer un exemple ? J'ai pas su trouver et la dernière fois que j'ai voulu faire j'ai dû appeler un package dans l'update pour faire ces deux types d'actions en même temps.. Alors ca fonctionne mais ca tue une mouche avec un bazoka...

[Waldar]

Le delete du merge a lieu sur la même table que l'update, hors il semble que JeanYvette cherche à supprimer dans une table secondaire.

[vanagreg]

Bonjour,

Comme le dit Waldar, le DELETE du MERGE va bien sûr s'effectuer sur la table cible. Si ce n'est pas le but recherché alors il faut faire un DELETE séparément. Sinon voici mon exemple d'utilisation de DELETE avec un MERGE:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
MERGE INTO tablecible t
USING (
       ....
      ) s  
ON (t.id = s.id)    
 WHEN MATCHED THEN UPDATE SET t.start_date = s.min_date,
                              t.end_date = case when s.rn = 1 then s.max_date else to_date('31/12/1999', 'DD/MM/YYYY') end
DELETE WHERE (t.end_date = to_date('31/12/1999', 'DD/MM/YYYY'));

Je mets à jour les colonnes start_date et end_date. Vu que je fusionne des intervalles qui se chevauchent, j'aurais des lignes superflues. Donc si ma colonne rn vaut 1 (colonne qui me trie les lignes), alors je mets max_date, sinon je mets une date arbitraire mais non existante dans les lignes, soit 31/12/1999. Maintenant avec la clause DELETE je supprime les lignes mises à jour ayant cette date de fin au 31/12/1999.

[JeanYvette]

Oui en effet, c'est sur une autre table que le delete doit se faire donc le merge n'est pas possible dans ce cas.
Par contre, merci pour l'exemple car j'avais cherché, par le passé, à faire cela et je n'avais rien trouvé, maintenant, j'ai la syntaxe et ca sera toujours utile


En tout cas, merci pour votre aide, je vais faire le dév et je clôturerais le post si tout se passe bien

[JeanYvette]

Bonjour bonjour,

Bon, j'ai fait les développements, je suis passé de 60h à 30h... Chouette (pas du tout)
J'ai cherché, du coup, a regardé plus en détails les select car ce que j'ai fait n'a pas suffit. J'ai vu quelque chose et je ne sais pas quoi en penser pour le coup :

Dans mon select, j'appelle 4 tables. Sur une de celles-ci, en regardant le plan d'exécution, j'ai un beau petit produit cartésien (on aime)...
En regardant plus en détail encore :

Sur cette table, j'ai deux colonnes qui sont présentent dans les jointures avec les autres tables. Sauf que ces deux colonnes sont présentent dans 3 index différents... Sur le papier, je me dis que c'est bien à scier mais... Ai-je raison ? Suis-je le seul "choqué" d'avoir 3 index différents, dans la même tables, dans lesquelles des colonnes sont présentes trois fois ?


J'ai voulu tenter d'utiliser un hint dans mon select pour forcer a utiliser l'index qui me semblait le mieux et là... bye bye le produit cartésien (Je suis si fort)
Du coup, là, deux questions en plus de celle au dessus :

Est-ce bien normal qu'en forçant l'utilisation d'un index le produit cartésien saute ? (J'ai un doute)

La deuxième, la plus importante car pour le coup je suis vraiment pas sûr de moi : Comme j'ai forcé l'utilisation d'un index précis sur une des tables de ma requête. Dois-je, du coup, forcer l'utilisation des autres index nécessaire ou est-ce que le hint ne concerne QUE la table ciblée et les autres tables utiliseront les index qui sont nécessaires


Je n'ai pas mis ma requête car il me semblait, ici, que c'est plus des questions générales mais si il y en a besoin, je modifierais ce message pour la rajouter

Merci d'avance

Bisous bisous

[vanagreg]

Bonjour,

Forcer un hint d'utilisation d'index est dans la grande majorité un mauvais signe. Si tu as un produit cartésien c'est qu'il doit manquer des jointures.
Oui il nous faut la requête, avec les pk/fk et index et le plan d'exécution.
30h de traitement ça me parait énorme. Tu traites des milliards de lignes?

[JeanYvette]

Voici la requête avec le hint (et oui, je sais, c'est pas très bien mais je voulais faire le test) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT /*+INDEX(table3 table3_IDX8)*/ distinct 'DEP' typtie,table1.codsoc,table1.codpro,table1.codzn4 assort, TO_NUMBER(NVL(TRIM(table3.sigtie), 0)) profil,table3.clequi site , codent,soc
FROM table1
    JOIN table2
        ON table2.codsoc = 1 and table2.typtie = 'PRO' and table2.niveau = 4 and table2.codefam = table1.fampro and table2.codesfa = table1.sfapro and table2.codessf = table1.ssfpro and table1.codesssf1 = table2.sssfp1
    JOIN table3
        ON table3.CODSOC = 1 AND table3.ACHVTE = 'A' AND table3.TYPLIE = 'NAS' AND table3.TQUI   = '1' AND table3.TQOI = '714' AND table3.codsoc = table2.codsoc AND table3.NUMORD = 1 AND table3.CLEQOI = table2.clefam
    JOIN table4
        ON table4.codent = 'PRO' and ta.codsoc =11 AND CALIDIS_ASSORT.DUP_CODCLE = pro.codpro
WHERE TO_NUMBER(NVL(TRIM(table3.sigtie), 0)) <= TO_NUMBER(NVL(TRIM(table1.codzn4), '0')) AND TO_NUMBER(NVL(TRIM(table3.sigtie), 0)) <> 0 AND NVL(TRIM(table1.codzn4),'CF') <> 'CF'
AND pro.codsoc = '11';

Il manque, en effet, surement des jointures mais pour le moment (je découvre un peu les tables en même temps que vous) je ne vois pas beaucoup de jointures qui puissent être ajoutées

Voici les plans d'éxecution, le premier avec le hint, le deuxième sans :


Sans :


J'ai essayé d'anonymé au mieux alors c'est moche, désolé, mais voici les correspondances couleurs :
Noir = table4
Rouge = table1
Orange = table2
Vert = table3

Si vraiment ca ne suffit pas, je remettrais sans mes montages (de toute beautée)

Pour ce qui est du temps : Il y a, dans la procédures, 8 nuances de la requête que j'ai montré. Il y a, au total, plus de 200M d'update...

J'ai voulu tenter de forcer avec cet index en particulier car sans le hint, Oracle va chercher l'index 1 qui utilise 7 colonnes alors que l'index 8 en utilise 3 et que deux des trois servent dans la jointure..

[vanagreg]

Pas besoin d'anonymiser, j'ai reconnu cette bouse de Générix
Je compatis, j'ai travaillé dessus pendant un an et c'était déjà trop! PK composées de plusieurs colonnes avec mix de number et varchar2, aucune FK, redondances de colonnes, stockage de date sous forme de varchar2, stockage de valeur numériques dans des colonnes varchar2, bref le modèle de données de merde par excellence.
Tu dis que tu as 200M d'updates mais je vois une cardinalité finale de 1? Tu ne fais pas cette requête 200M de fois en boucle?

[JeanYvette]

Ahaha... no comment

Non, j'ai cette requête en 10 exemplaires (les clauses qui changent et pour certaines j'ai besoin d'appeler d'autres tables) mais au total des 10, il y a jusqu'à 200M d'update pour toutes ces requêtes

[vanagreg]

Bonjour,

Il faut prendre les requêtes les plus couteuses car les cardinalités de celle que tu as montrée ont l'air faibles, donc la requête devrait être rapide. Ou alors les stats ne sont pas à jour.
Combien de lignes ramène cette requête?

[JeanYvette]

C'est cette requête là pourtant, qui est la plus couteuse à l'heure actuelle. J'ai tenté de la comparer à une autre qui a été faite qui fait et qui fait appel aux même 4 tables, avec moins de filtres et d'autres qui changent un peu (Bref, tu connais la base je fais pas de dessin )
La différence que j'ai vu était que la table 3 qui pose souci au niveau du plan d'exécution avec le produit cartésien était qu'elle était en left join (avec la petite syntaxe avec le (+) qu'on aime bien). J'ai voulu reporter le left join sur cette requête qui est la plus longue et là, j'arrive enfin à avoir un résultat que je n'avais pas avant...
Pour le coup, je ne pensais pas qu'un left join pourrait faire ça, je ne comprends pas vraiment j'ai l'impression de louper quelque chose


ps : c'est long, je ne peux donner le nombre de lignes associé au temps car la requête tourne toujours mais dès que la requête sera fini, je modifierais la réponse pour donner les résultats

[JeanYvette]

Bonjour bonjour,

Cela fût long mais l'optimisation est faite (youpi). Pour le process d'optimisation, d'un coup, la personne qui a fait le script initial se rend compte que certaines requêtes ne servaient à rien (génial...) donc forcément, moins de traitement = moins de temps.

Finalement, passer par une requête du style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE matable SET macolonne = mavaleur WHERE EXISTS (SELECT blabla FROM balbla)

améliorait un peu le temps de traitement mais c'était pas la folie. Je passais, en gros, de 60h de temps de traitements à une vingtaine d'heure.

Finalement, j'ai tenté de passé par un merge à la place, et, tant qu'à faire comme on est des fous (non) j'ai mis un alter session en début de procédure stockée pour autoriser les traitements DML en pararell => Je suis passé à 1h de temps de traitement (trop fort) pour plus de 2M de lignes mises à jour

Merci en tout cas pour vos aide

Vive géné... ouais, non

Bisous bisous

[Waldar]

Bravo, ce genre d'optimisation est extrêmement gratifiant.
2M en 1h, ça pourrait probablement être amélioré encore, mais le bon en avant étant déjà très significatif le boulot est (bien) fait et il faut savoir s'arrêter quelque part.