Problème de performances suite au passage de Oracle 10R2 à Oracle 11R2

Bonjour,

J'ai une requête (qui était déjà pas bien rapide) qui est devenue totalement inutilisable.

J'ai identifié quelle partie coince :

Tables impliquées :
EVE
=> INDEX UNIQUE sur (CODSOC, ACHVTE, TYPEVE, NUMEVE)

EVL
=> INDEX UNIQUE sur (CODSOC, ACHVTE, TYPEVE, NUMEVE, NUMPOS, NUMLIG)

PRO
=> INDEX UNIQUE SUR (CODSOC, CODPRO)

Code:

---

1 2 3 4 5 6 7 8

select * from eve inner join evl on evl.codsoc = eve.codsoc and evl.achvte = eve.achvte and evl.typeve = eve.typeve and evl.numeve = eve.numeve inner join pro on pro.codsoc = 1 and pro.codpro = evl.codpro where eve.codsoc = 8001 and eve.achvte = 'A' and eve.typeve = 'PLA'

---

Sur le serveur de DEV, cette requête fait un RANGE SCAN sur les index uniques de EVE et EVL, et un UNIQUE SCAN sur PRO, pour un coût de 167. La requête est instantanée (moins de 0,1 seconde)

Sur le serveur de REC, cette requête fait un RANGE SCAN sur les index uniques de EVE et EVL, mais un FAST FULL SCAN sur PRO, pour un coût de plus de 90000. J'ai stoppé la requête au bout de 5 minutes.

Les index sont les mêmes sur les deux serveurs, même version (c'est deux instance sur un même serveur).

La volumétrie est cependant passablement différente :

DEV :
- EVE : 30 383
- EVL : 67 970
- PRO : 136 419

REC :
- EVE : 25 015 883
- EVL : 84 233 838
- PRO : 428 292

Ok, des rapports de 1 pour 1000, c'est loin d'être anodin.
Mais pourquoi passer d'un UNIQUE SCAN à un FAST FULL SCAN ?
Si j'enlève la jointure sur PRO, la requête est immédiate. La requête retourne alors moins de 50 lignes. Comment 50 lectures sur PRO, en se basant sur un INDEX UNIQUE peuvent être aussi lentes ?

Soit la requête est fausse (codsoc =1 et codsoc=8001), soit elle fait un produit cartesien avec PRO, dans ce cas, vu la volumétrie de la table, les temps de réponses ne m'étonnent pas.

Combien y a -t- il de valeurs distinctes pour vos colonnes index sur la table PRO, si très peu, votre index n'est pas éligible

Se qui evp dans

Code:

---

1 2	and pro.codpro = evp.codpro

---

Postez la bonne requête ainsi que les informations du plan d'exécution réel si possible sinon on perte notre temps.

Excusez-moi, c'est pas EVL, mais EVL qu'il faut lire dans la jointure de PRO.

Le modèle des données ne contient pas de PK ni de clé étrangères.

Et la colonne "CODSOC" permet d'avoir un niveau de gestion (société) différent d'une entité à l'autre (table).

Ici, les produit (PRO) sont gérés dans la société 1 (groupe), et les événements (EVE, EVP, EVL, EVT) sont gérés en société juridique (8001 dans l'exemple).

J'ai simplifié le code en mettant ces valeurs en dur. Il y a une table MEV qui permet de faire la jointure "proprement" (autant faire se peut). Mais qu'elle soit présente ou non, ne change rien.

Quant à la question sur l'éligibilité de l'index sur PRO, je comprends pas la question : les index que j'ai indiqué sont des index UNIQUES, donc il y a 428 292 lignes dans la table PRO, et 428 292 valeurs uniques dans l'index (même si presque toutes les lignes ont le même CODSOC).

Sinon, les performances n'ont rien de normal, avant passage à Oracle 11, c'était la même base, un serveur vieillissant moindrement configuré, et on n'avait pas du tout de problème avec cette table. Je le répète, historiquement, ce type de jointure ne change pas un millième de seconde en temps normal.

Il n'y a qu'une seule ligne PRO par EVL, donc avec la jointure sans PRO, on a 50 lignes, c'est pas comme si on ramenait des millions de lignes.

EVE : Commandes
EVL : Ligne de cadencement de la commande (un produit/un dépot de livraison/une date de livraison/un lot/un emplacement)
PRO : Produit

On a donc N cadencements pour une commande. Mais 1 produit pour un cadencement.

Les index uniques que j'ai indiqué sont l'équivalent des clés primaires (qui n'existent pas dans la base). Donc vu la nature des jointures, il ne peut pas y avoir de meilleur candidat.

Citation:

---

Envoyé par StringBuilder

Quant à la question sur l'éligibilité de l'index sur PRO, je comprends pas la question : les index que j'ai indiqué sont des index UNIQUES, donc il y a 428 292 lignes dans la table PRO, et 428 292 valeurs uniques dans l'index (même si presque toutes les lignes ont le même CODSOC).

---

OK en ce cas on oublie la question ;)

Oracle10 != Oracle11 (très différente!)

Citation:

---

Postez la bonne requête ainsi que les informations du plan d'exécution (réel si possible) sinon on perte notre temps.

---

Ca sert à rien que je poste la requête, elle est imbittable, et je vais passer plus de temps à devoir justifier telle ou telle condition qu'à avoir des réponses.

La requête, en elle-même, est parfaitement bonne.
Elle a toujours fonctionné ainsi, y compris quand on était sous Oracle 9.

Il y a une merde (et pas qu'un peu) avec la configuration de Oracle 11. Seulement, on n'arrive pas à comprendre laquelle. Le souci ne vient pas des requêtes.
La moitié d'entre elles sont simples à bouffer de la paille, et Oracle ne s'y retrouve pas.

Un autre exemple, plus simple, avec requête complète :
table (index unique, en lieu et place de la PK qui n'existe pas)
PRO (CODSOC, CODPRO)
TBL (CODSOC, CODTBL, CLETBL)
ZOD (CODSOC, TYPZOD, CLEZOD, NUMZOD, DATZOD)

Sémantiquement, on recherche tous les produits dont la date de retrait du marché est comprise entre la date du jour et les 30 prochains jours (30 étant obtenu par la sous-requête sur TBL qui ne nous intéresse absolument pas : lecture d'une seule ligne, pas de souci de ce côté là)

PRO = Produit
ZOD = Table fourre-tout dans laquelle on stocke des propriétés (numzod) étendues pour une table donnée (typzod)
TBL = Table fourre-tout qui stock les catalogues de l'application (codtbl) et leur valeur (cletbl)

TBL 997 = Catalogue de paramétrage. La clé DELLGL comporte la valeur 30.
TBL tpr = Catalogue des types de produits. Sert juste à récupérer dans le résultat le type du produit pour chaque ligne retournée.

ZOD PRO 154 = Date de retrait du marché, au format (c'est un varchar2) DDMMYYYY

Toutes ces informations sont stockées au niveau groupe, donc CODSOC = 1 partout, en dur.

Pas de commentaire sur le modèle de données, il est livré tel quel par l'éditeur, on ne peut pas y toucher.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT pro.CODPRO, pro.nompro, tbl.lib1, valzod, to_date(valzod, 'DDMMYYYY') - trunc(SYSDATE) AS dif FROM pro, zod, tbl WHERE pro.codpro = clezod AND zod.codsoc = 1 AND pro.codsoc = 1 AND tbl.codsoc = 1 AND typzod = 'PRO' AND numzod = 154 AND datzod = ' ' AND codtbl = 'tpr' AND cletbl = pro.typpro AND lpad(zod.valzod, 1) <> ' ' AND (to_date(valzod, 'DDMMYYYY') <= (trunc(SYSDATE) + (SELECT dec1 FROM tbl WHERE codsoc = 1 AND codtbl = '997' AND cletbl = 'DELLGL'))) AND (trunc(SYSDATE) <= to_date(valzod, 'DDMMYYYY')) ORDER BY to_date(valzod, 'DDMMYYYY') ASC

---

=> 55 secondes depuis passage sous Oracle 11. La requête était instantannée sous Oracle 10. 6 lignes retournées.

Ok, la requête est mal écrite.
On la passe gentillement en SQL89, on préfixe correctement les colonnes, c'est toujours ça de gagné en lisibilité :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

select pro.codpro, pro.nompro, tbl.lib1, valzod, to_date(valzod, 'DDMMYYYY') - trunc(SYSDATE) as dif from zod inner join pro on pro.codsoc = 1 and pro.codpro = zod.clezod inner join tbl on tbl.codsoc = 1 and tbl.codtbl = 'tpr' and tbl.cletbl = pro.typpro where zod.codsoc = 1 and zod.typzod = 'PRO' and zod.numzod = 154 and zod.datzod = ' ' and lpad(zod.valzod, 1) <> ' ' and (to_date(zod.valzod, 'DDMMYYYY') <= (trunc(SYSDATE) + (select tbl.dec1 from tbl where tbl.codsoc = 1 and tbl.codtbl = '997' and tbl.cletbl = 'DELLGL'))) and (trunc(SYSDATE) <= to_date(zod.valzod, 'DDMMYYYY')) order by to_date(zod.valzod, 'DDMMYYYY') asc

---

Ok, la syntaxe est différente, mais en soit, la requête est absolument identique.
Pourtant, Oracle 11 tombe à 5 secondes.

C'est pas assez, avant c'était moins de 1 seconde avec les mêmes données et un serveur moins puissant.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select pro.codpro, pro.nompro, tbl.lib1, valzod, to_date(valzod, 'DDMMYYYY') - trunc(SYSDATE) as dif from zod inner join pro on pro.codsoc = 1 and pro.codpro = zod.clezod inner join tbl on tbl.codsoc = 1 and tbl.codtbl = 'tpr' and tbl.cletbl = pro.typpro where zod.codsoc = 1 and zod.typzod = 'PRO' and zod.numzod = 154 and zod.datzod = ' ' and trim(zod.valzod) is not null -- plus joli qu'un LPAD, et certainement plus performant and to_date(zod.valzod, 'DDMMYYYY') between trunc(SYSDATE) and trunc(SYSDATE) + (select tbl.dec1 from tbl where tbl.codsoc = 1 and tbl.codtbl = '997' and tbl.cletbl = 'DELLGL') -- un between c'est toujours mieux qu'un >= and <= order by to_date(zod.valzod, 'DDMMYYYY') asc

---

Ok, à part le LPAD, rien de follichon, le between c'est juste pour la lisibilité.
On tombe à 4 secondes. On va accorder à Oracle le bénéfice du doute avec des données déjà en cache.

Mais bon, 4 secondes, c'est toujours plus de 4 fois plus lent qu'avant...

Alors le plan d'exécution...

Ok, alors il commence par PRO... Avec un FULL SCAN (forcément, je vois pas ce qu'il peut filtrer de mieux s'il commence par ça).
Ensuite... On, INDEX SEEK sur l'index unique de TBL...
Et un INDEX RANGE sur ZOD... Ok, vu comme il était parti, il risquait pas de faire mieux.

Bon, mon voisin, dinosaure d'Oracle, qui bosse sur cette base depuis l'époque de Oracle 7 (et peut-être même avant) me donne un coup de coude "met un hint !"

Ok, le truc qui me fait vomir rien qu'à l'idée d'imaginer qu'un jour un développeur ait pu se dire "bon, je fais de la merde, c'est à l'utilisateur de faire le boulot de mon optimiseur pourri". Et surtout, sur une requête aussi simple, j'imagine pas une seconde que ça puisse servir à quoi que ce soit... Une jointure sur 3 table... Merde quoi !

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select /*+ORDERED*/ pro.codpro, pro.nompro, tbl.lib1, valzod, to_date(valzod, 'DDMMYYYY') - trunc(SYSDATE) as dif from zod inner join pro on pro.codsoc = 1 and pro.codpro = zod.clezod inner join tbl on tbl.codsoc = 1 and tbl.codtbl = 'tpr' and tbl.cletbl = pro.typpro where zod.codsoc = 1 and zod.typzod = 'PRO' and zod.numzod = 154 and zod.datzod = ' ' and trim(zod.valzod) is not null -- plus joli qu'un LPAD, et certainement plus performant and to_date(zod.valzod, 'DDMMYYYY') between trunc(SYSDATE) and trunc(SYSDATE) + (select tbl.dec1 from tbl where tbl.codsoc = 1 and tbl.codtbl = '997' and tbl.cletbl = 'DELLGL') -- un between c'est toujours mieux qu'un >= and <= order by to_date(zod.valzod, 'DDMMYYYY') asc

---

On tombe à 0,2 secondes.

Forcément, il commence par un RANGE SCAN sur ZOD, qui fait quelques milliards de lignes, mais vu que c'est la seule table où on a un point d'entrée digne de ce nom... Mais une fois filtré sur CODSOC, CLEZOD, NUMZOD, DATZOD, on tombe à un nombre bien plus faible... moins de 1000 lignes... Évidement qu'on pourra pas mieux filtrer sur PRO où 100% des produits sont en CODSOC = 1 et qu'on n'a pas d'autre information au départ !
Puis un INDEX SEEK sur PRO... Evidement, quand on a CODSOC et CODPRO, on a l'index unique...
Et enfin un INDEX SEEK sur TBL... Bah ouais, CODSOC, CODTBL, CLETBL, qui sont maintenant tous connus, c'est l'index unique.

J'aimerais juste comprendre comment Oracle peut passer à côté d'un truc aussi élémentaire ! Je suis sûr que même un MySQL mal configuré s'en sortirait mieux ! Comment est-ce possible ?

J'ai reforcé un rebuild des index sur les différentes tables, un recalcul des stats avec analyse complète des données... C'est donc pas ça nom plus.

Y'a un paramètre pour dire à Oracle d'allumer son cerveau ?

J'ai ajouté les plan d'exécution.

A 14 heures, ils étaient différents, et les temps d'exécution aussi. (ma dernière requête met maintenant autant de temps que l'originale, sans aucune raison !)

Pièce jointe 190861
Pièce jointe 190862
Pièce jointe 190863
Pièce jointe 190860

Bonjour,

Bref laissez à coté les considérations sur les requêtes et Oracle vous ne maîtrisez pas assez le sujet. Exemple vous perdez votre temps pour écrire des requêtes au sauce ANSI qu'Oracle ne fait que le réécrire à son sauce donc comme elles été à l'origine pour générer le même plan d'exécution. N'utilisez pas de hint pour optimiser une requête si vous n'avez pas compris pourquoi elle rame et comment le hint en question va intervenir sur la requête.

Commencez par lister les paramètres de l'optimiseur. Pour cella connectez-vous sous sqlplus comme dba et tapez
SQL>show parameter optimizer

Postez par la suite le résultat.

Ensuite prenez la requête tel qu'elle est écrite actuellement et exécutez-la via sqlplus en ajoutant le hint gather_plan_statistics comme dans l'exemple:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> set linesize 300 SQL> set pagesize 0   SQL>SELECT /*+ gather_plan_statistics */ pro.CODPRO, pro.nompro, tbl.lib1, valzod, to_date(valzod, 'DDMMYYYY') - trunc(SYSDATE) AS dif FROM pro, zod, tbl WHERE pro.codpro = clezod AND zod.codsoc = 1 AND pro.codsoc = 1 AND tbl.codsoc = 1 AND typzod = 'PRO' AND numzod = 154 AND datzod = ' ' AND codtbl = 'tpr' AND cletbl = pro.typpro AND lpad(zod.valzod, 1) <> ' ' AND (to_date(valzod, 'DDMMYYYY') <= (trunc(SYSDATE) + (SELECT dec1 FROM tbl WHERE codsoc = 1 AND codtbl = '997' AND cletbl = 'DELLGL'))) AND (trunc(SYSDATE) <= to_date(valzod, 'DDMMYYYY')) ORDER BY to_date(valzod, 'DDMMYYYY') ASC /

---

suivi de

Code:

---

1 2	select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

---

et postez le plan d'exécution réel ainsi que les notes qui le suivent.
Celui que vous posté est assez difficile à lire et manque des informations vitales.
Nous allons avec vous essayer de comprendre ce qui se passe.

Le hint en question permet de collecter des statistiques lors de l'exécution de la requête au niveau des sources des lignes et surtout de comparer les estimations d'Oracle lors de l'élaboration du plan d'exécution par rapport à son exécution réel.

Merci pour votre réponse.

Demain, je suis en déplacement pour un autre client. Mais mercredi, je regarde ça.

Vous dites que je perds mon temps en réécrivant la requête en ANSI 92.
=> Pourtant, quand je réécris la requête, en ne changeant que ça (ajout des INNER JOIN, et passage des critères de jointure dans le ON) le plan est modifié et la vitesse d'exécution aussi (même si, pour une raison que je ne comprends pas, les plans que j'ai refais au moment du post, n'étaient plus les mêmes !)

Lien ou pas, je l'ignore, mais toujours est-il que j'ai constaté un changement (???)

Quand vous avez réécrite la requête pour utiliser la norme ANSI vous avez également changé le texte de la requête. Pour le serveur c'est une nouvelle requête désormais pour laquelle il doit faire le hard parsing donc l'optimisation. Mais Oracle change vos jointure ANSI en non AINSI (il y quelques exceptions non applicables pour cet exemple) et il optimise par la suite. Donc vous est à la case départ. Mais les choses sont assez complexes dans le monde d'Oracle donc il est possible parfois que l'exécution de la nouvelle requête passe plus vite mais a terme même plan d'exécution vaut même temps d'exécution.