Optimisation d'une procédure

**StringBuilder** · 30/08/2011, 11h21

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 :

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

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 :

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

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** · 30/08/2011, 11h32

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** · 30/08/2011, 11h40

Code :

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

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** · 30/08/2011, 11h47

Envoyé par Yanika_bzh

Code :

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

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

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

)

**Yanika_bzh** · 30/08/2011, 11h55

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** · 30/08/2011, 12h07

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

**StringBuilder** · 30/08/2011, 11h42

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.

**Golgotha** · 30/08/2011, 11h50

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.

**StringBuilder** · 30/08/2011, 12h04

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)

**Yanika_bzh** · 30/08/2011, 12h10

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** · 30/08/2011, 11h59

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 :

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

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 :

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

select * from eve where codsoc = 100 and achvte = 'V'

Alors j'utilise bien l'index

Optimisation d'une procédure

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager