Optimisation d'une procédure

[StringBuilder]

Bonjour,

Nous avons une procédure de nuit qui dure plus de 5 heures.
Elle commence à empiéter sur les horraires de travail, ce qui n'est pas là pour arranger sa durée...

J'ai tenté de l'optimiser, mais contre toute attente, chaque tentative d'optimisation ne fait qu'alourdir les requêtes !

Je ne sais plus quoi faire.

Voici un exemple, avec la première requête (elle sert pour faire un curseur qui englobe l'ensemble des traitements) :

La version originale :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT cnt.codsoc, cnt.sigtie, cnt.datapp, cnt.datech ,cnt.numcnt, cnt.libcnt, cnp.codpro, cnp.coduni, cnt.codzn5, pru.coefuv
FROM cnt, cnp, pru 
WHERE   cnt.CODSOC = 100
AND     cnt.achvte = 'V'
AND     cnt.typeve = 'CNT'
AND     cnt.codzn4 = 'PDL'
AND     cnt.datrev <= to_char(sysdate, 'YYYYMMDD')
AND     TO_CHAR(TO_DATE(cnt.datech,'YYYYMMDD') +15,'YYYYMMDD') >= to_char(sysdate, 'YYYYMMDD')
AND     EXISTS(SELECT 1 FROM cnt cnt1 WHERE cnt1.codsoc = 100 AND cnt1.achvte ='V' AND cnt1.typeve = 'CNT' AND cnt1.numcnt = cnt.codzn5)
AND     cnp.codsoc = cnt.codsoc
AND     cnp.achvte = cnt.achvte
AND     cnp.numcnt = cnt.numcnt
AND     pru.codsoc = cnp.codsoc
AND     pru.codpro = cnp.codpro
AND     pru.coduni = cnp.coduni
ORDER BY cnt.codzn5, cnp.codpro

La version "optimisée" :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT c1.codsoc, c1.sigtie, c1.datapp, c1.datech, c1.numcnt, cnp.codpro, c1.codzn5, pru.coefuv
FROM cnt c1
INNER JOIN cnt c2 ON c2.codsoc = c1.codsoc AND c2.achvte = c1.achvte AND c2.numcnt = c1.codzn5 AND c2.codzn4 = 'ENS'
INNER JOIN cnp ON cnp.codsoc = c1.codsoc AND cnp.achvte = c1.achvte AND cnp.numcnt = c1.numcnt AND cnp.tqoi = '501'
INNER JOIN pru ON pru.codsoc = cnp.codsoc AND pru.codpro = cnp.codpro AND pru.coduni = cnp.coduni
WHERE c1.codsoc = 100
AND c1.achvte = 'V'
AND c1.typeve = 'CNT'
AND c1.codzn4 = 'PDL'
AND c1.datrev <= to_char(sysdate, 'YYYYMMDD')
AND c1.datech >= to_char(to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD') - 15, 'YYYYMMDD')
ORDER BY c1.codzn5, cnp.codpro;

Voici les optimisation que j'y ai déjà apporté :
-- Optimisation 1 : Réécriture de la requête au format SQL-92
-- Optimisation 2 : Utilisation un "in" à la place d'un "exists", car "in" est plus performant
-- Optimisation 3 : On ne fait pas de cast sur cnt.datech (n fois) mais sur V_date (1 fois)
-- Optimisation 4 : Ajout de cnp.tqoi dans la requête : index primaire sur CNP
-- Optimisation 5 : Remplacement du EXISTS par une jointure INNER JOIN
-- Optimisation 6 : Suppression de champs inutilement retournés par la requête

La requête originale met 3,86 secondes à s'exécuter, et environ 21 secondes pour fetcher l'ensemble des lignes (comptage de tête, je sais, c'est super précis...)

La requête "optimisée" met 3,02 secondes à s'exécuter, et environ 23 secondes pour fetcher l'ensemble des lignes.

Selon la charge du serveur, les temps ont tendance à s'inverser.

Bref, "optimisation" ou pas, ça met grossomodo le même temps à s'exécuter...

Les plans d'exécution sont en pièces attachées.

Ce qui est ensuite bizarre, c'est que j'ai modifié l'ensemble du script qui contenait :

Curseur sur la requête ci-dessus
Curseur sur une autre requête
Update
Loop
Curseur sur une autre requête
Update
Loop
Update
Appel a une procédure
Loop

Et maintenant :

Curseur sur la requête ci-dessus
Appel a une procédure
Select sum()
execute immediate 'Update'
Select sum()
execute immediate 'Update'
execute immediate 'Update'
Loop
Appel a une procédure

Donc je n'ai plus de boucles imbriquées, mais des requêtes qui retournent un sum() dans une variable.
Je n'ai plus d'update inline, mais des execute immediate à la place.
J'ai moins d'update (au lieu de les faire ligne par ligne, je les fais pour un lot de lignes).

Pourtant, le temps d'exécution est plus long qu'avant !!!

J'ai pris une à une toutes mes requêtes, j'ai vérifier que j'utilisais au maximum les champs des index uniques. Elles sont toutes mieux optimisées que les originales.

Je ne sais plus quoi faire pour optimiser...

Si vous voulez, je peux vous poster l'ensemble du script, mais c'est un peu long...

[Golgotha]

Bonjour,

Il y a un TABLE ACCESS (FULL) sur la table pru. Possibilité de mettre un index sur cette table pour éviter ça ?

[Yanika_bzh]

Code :

1
2
 cnt.datrev <= to_char(sysdate, 'YYYYMMDD')
AND     TO_CHAR(TO_DATE(cnt.datech,'YYYYMMDD') +15,'YYYYMMDD') >= to_char(sysdate, 'YYYYMMDD')

C'est obligé tout ces casts ??

[StringBuilder]

C'est dans l'ancien plan d'execution qu'il y a ce full access.

Dans le nouveau plan (le second attachement), il y a un full access à... cnt.

Qui est bien plus grosse

Cependant, je ne sais pas si c'est vraiment gênant, puisqu'il y a des "access predicate" qui eux, font partie notamment de l'index unique.

[StringBuilder]

Envoyé par Yanika_bzh

Code :

C'est obligé tout ces casts ??

Oui, car les dates sont stockées en varchar2(8) sous la forme YYYYMMDD

Et en réalité, j'utilise une variable V_Date qui contient to_char(sysdate, 'YYYYMMDD')

C'est donc :

cnt.datrev <= V_Date
AND TO_CHAR(TO_DATE(cnt.datech,'YYYYMMDD') +15,'YYYYMMDD') >= V_Date



PS : Mais à nouveau, là vous regardez l'ancienne requête, dans la nouvelle, j'ai viré les to_char(to_date()) sur cnt.datech pour améliorer les performances (enfin... faut le dire vite )

[Golgotha]

Envoyé par StringBuilder

C'est dans l'ancien plan d'execution qu'il y a ce full access.

Dans le nouveau plan (le second attachement), il y a un full access à... cnt.

Qui est bien plus grosse

Cependant, je ne sais pas si c'est vraiment gênant, puisqu'il y a des "access predicate" qui eux, font partie notamment de l'index unique.

Ha oui, je n'avais pas fait gaffe, je pense qu'en évitant cet access full, vous aurez un meilleur temps, il fraudait indexer cette table ci possible.

[Yanika_bzh]

3sec pour s'executer, 23 sec pour recuperer l'ensemble des données, 5h de traitement, ce n'est pas forcément cette requete qui est le goulot d'etranglement, si ? vous avez reussi a analyser l'ensemble de votre traitement

Curseur sur la requête ci-dessus
Appel a une procédure
Select sum()
execute immediate 'Update'
Select sum()
execute immediate 'Update'
execute immediate 'Update'
Loop
Appel a une procédure

?

[StringBuilder]

Envoyé par StringBuilder

Dans le nouveau plan (le second attachement), il y a un full access à... cnt.

Qui est bien plus grosse

Je ne comprends pas bien d'ailleurs...

J'ai un index sur CNT CNT_IDX1(CODSOC, ACHVTE, NUMCNT)

Je force l'utilisation de l'index sur C1 :

Code :

1
2
3
4
5
 
SELECT c1.codsoc, c1.sigtie, c1.datapp, c1.datech, c1.numcnt, cnp.codpro, c1.codzn5, pru.coefuv
FROM cnt c1 /*+INDEX(C1 CNT_IDX1)*/
INNER JOIN cnt c2 ON c2.codsoc = c1.codsoc AND c2.achvte = c1.achvte AND c2.numcnt = c1.codzn5 AND c2.codzn4 = 'ENS'
...

Pourtant, dans l'explain plan, j'ai toujours un full access.

J'ai essayé sur une autre table de structure similaire : EVE avec EVE_IDX1 (codsoc, achvte, typeve, numeve) et si je fais :

Code :

SELECT * FROM eve WHERE codsoc = 100 AND achvte = 'V'

Alors j'utilise bien l'index

[StringBuilder]

Envoyé par Golgotha

Ha oui, je n'avais pas fait gaffe, je pense qu'en évitant cet access full, vous aurez un meilleur temps, il fraudait indexer cette table ci possible.

Elle est pourtant indexée, c'est bien là que je pige plus rien :

CNT_IDX1 (CODSOC, ACHVTE, NUMCNT)
CNT_IDX2 (CODSOC, ACHVTE, SIGTIE, NUMCNT)

=> L'index CNT_IDX1 est donc parfaitement utilisable en RANGE... D'autant que c'est la PK ! (le second aussi est utilisable d'ailleurs)

[StringBuilder]

Envoyé par Yanika_bzh

3sec pour s'executer, 23 sec pour recuperer l'ensemble des données, 5h de traitement, ce n'est pas forcément cette requete qui est le goulot d'etranglement, si ? vous avez reussi a analyser l'ensemble de votre traitement ?

C'est justement là que je ne comprends pas pourquoi ça dure aussi longtemps :

Les autres requêtes, même si elles sont exécutées près de 10 000 fois, durent toutes moins de 1 secondes, que ce soient les select comme les insert.

Donc 10 000 * 6 = 17 minutes environ.

Même pire, sur le serveur de développement, où je traite 6000 lignes, ça ne dure que 201 secondes (ancien script) et 315 secondes (nouveau script pourtant censé être optimisé)

[Yanika_bzh]

Avoir remplacé le Exists par une jointure n'est pas equivalent fonctionnellement. En effet le Exists vous renverra VRAI ou FAUX, tandis que la jointure vous renverra tous les tuples dont la jointure est VRAI.

Je vous propose donc d'enlever l'autojointure et de remettre la clause Exists.

Pouvez vous poster le plan d'execution apres cette modification ?

[StringBuilder]

En pièce jointe, le script complet.

Pour moi, l'ensemble des autres requêtes sont optimisées à bloc.

Je ne vois pas ce qui déconne pour que ça dure aussi longtemps en production.

[StringBuilder]

Envoyé par Yanika_bzh

Avoir remplacé le Exists par une jointure n'est pas equivalent fonctionnellement. En effet le Exists vous renverra VRAI ou FAUX, tandis que la jointure vous renverra tous les tuples dont la jointure est VRAI.

Je vous propose donc d'enlever l'autojointure et de remettre la clause Exists.

Pouvez vous poster le plan d'execution apres cette modification ?

Dans mon cas, si, car le exists porte sur une donnée d'"entête".

En fait, il s'agit de contrat magasins (cnt.typtie = 'PDL')
Et tous les contrats magasin sont rattachés à un contrat enseigne (contrat global) (cnt.typtie = 'ENS') qui est stocké en cnt.codzn5

Donc le fait de rechercher s'il existe un contrat enseigne, revient à faire une jointure inner en prenant comme critère c1.typtie = 'PDL' and c2.numcnt = c1.codzn5 and c2.typtie = 'ENS'

Le plan d'exécution n'a d'ailleurs pas changé lorsque j'ai fait cette modification : le premier CNT (C1) était déjà en full access, tandis que le second (C2) utilisait déjà l'index CNT_IDX1 qui est unique.

[Yanika_bzh]

Quelle est la volumétrie de retour de cette requete ?

[StringBuilder]

Envoyé par Yanika_bzh

Quelle est la volumétrie de retour de cette requete ?

J'obtiens exactement 8526 lignes en retour de cette requête.

Cela reste donc tout à fait correct.

La table CNT contient 103 964 lignes (dont 50 781 pour CODSOC = 100 et ACHVTE = 'V')
=> A nouveau, ça reste honnête

CNP est à peine plus grosse, avec respectivement 121 163 et 68 125 lignes.

PRU a 63 839 lignes

PRO a 12 972 lignes

Donc franchement, c'est une base de Mickey à ce niveau là... Même sur un PC portable, je ne m'explique pas une telle lenteur.

En revanche, les tables EVE, EVP et EVT sont très volumineuses :
EVE : 9 838 936 lignes
EVP : 43 639 222 lignes
EVT : 63 527 152 lignes

Cependant, les requêtes et mises à jour tapent dans leurs index unique à chaque fois, et leur durée n'est pour ainsi dire pas mesurable.

En revanche, j'ai un sérieux doute quand à la véracité de leurs statistiques...

En effet, "select count(*) from eve" a mis... facilement 5 minutes. Idem pour les autres requête sur EVP et EVT, dont j'attends toujours la réponse (????)

[Golgotha]

Oui mais, avec un table access full sur une table à 100.000 et une jointure sur une autre, ça te fait des milliard de réponse

Est ce que tu peux virer les inner join un par un et lancer la requête à chaque fois ?
Le but étant de voir quel élément de ta requête provoque la lenteur de celle ci.

[StringBuilder]

Envoyé par Golgotha

Oui mais, avec un table access full sur une table à 100.000 et une jointure sur une autre, ça te fait des milliard de réponse

Est ce que tu peux virer les inner join un par un et lancer la requête à chaque fois ?

L'auto-jointure passe sur un index unique.
Donc non.

Je ne pense pas que ma requête soit réellement à remettre en cause.
En termes de performances, comme l'a souligné Yanika_bzh, elle est trop rapide pour être la cause de la lenteur.

Je l'avais postée pour voir si quelqu'un avait une idée de pourquoi mes optimisations ne marchaient pas du tout (puisqu'elle est plus lente que le torche cul initial ).

Je pense que mon souci se situe dans le script lui-même (que j'ai mis en pièce jointe un peu plus haut).

Mais je ne vois pas où.
=> J'ai tenté de limiter les curseurs (un seul contre 3 initiallement, dont deux à l'intérieur du premier)
=> J'ai tenté au maximum d'utiliser les index les plus performants (index unique ou celui avec la plus petite cardinalité)
=> J'ai tenté d'utiliser des execute immediate car c'est plus rapide que des update inline
=> J'ai tenté de faire des gros update plutôt que des update ligne à ligne
=> J'ai tenté de faire des SUM() plutôt que de ramener toutes les lignes puis faire des additions dans un curseur
=> J'ai viré une chiée de substr, lpad, trim inutiles au niveau des jointures

Je sais plus quoi faire...
Et je comprends pas pourquoi c'est plus lent qu'avant !

[ORA-007]

Bonjour,

Tu devrais mettre un output de sysdate avant et juste apres tes 3 updates , a mon avis ton temps vient de là.

Verifie qu'il n'y a pas de trigger sur les tables updatées, puis que les stats sont bien à jour, ainsi que les index sur les FK .

Pas de traitements en paralleles avec possibilité de locks sur tes 3 table ?

Pourrais tu poster les explain plan de tes requetes et notamment des tes updates comme ceci :

Code :

1
2
3
4
EXPLAIN PLAN FOR 
UPDATE ...
 
SELECT * FROM TABLE(Dbms_Xplan.display());

[Golgotha]

Tu dit plus haut que sur ton poste de DEV il n'y à pas de problème, juste pour confirmer, tu as donc bien lancé les même jeux de test avec les même requêtes et c'est beaucoup plus lent sur la prod que sur le DEV,

si oui : cela ne vient il pas de la machine ? manque de cache ? mal paramétré ? manque de perf ? processus en parallèle qui tourne ?

[StringBuilder]

=> J'ai fini par réussi à mettre un hint pour utiliser un index sur ma première requête...

C'est plus lent que le full scan, et le coût du plan d'exécution passe de... 649 à 4778 grmpf !

En ce qui concerne le update, je vais vérifier de nouveau.
Mais lorsque je le fais à la main (pour certes, je ne le fais que sur des tuples saisis à la main) j'ai des temps de mise à jour de l'ordre de 0,1 seconde.

Pour les traitements concurrents, c'est là que ça se corse...

La procédure tourne à 3h du matin.
A 5h du matin, on met à jour massivement la table CNT et CNP (alors qu'un curseur tourne dessus et qu'on est en train de faire des update dessus...)

D'un point de vue fonctionnel, c'est bancal, mais bon...

En journée, vu qu'il s'agit de la base d'un ERP, oui, il y a des tas d'accès concurrents sur ces tables, mais les données mises à jour ne sont pour ainsi dire pas présentes dans les écrans, donc il y a peu de chance qu'on ait des locks dessus (données à des fins de stat principalement)