[Oracle] Requête très lente. Que faire ?
Requête très lente. Que faire ? [Oracle] - SQL/NoSQL - Programmation
Marsh Posté le 16-06-2004 à 17:15:29
désolé, c'est pas super lisible 
Mais la requête est très simple (sisi, même si ca se voit pas)
Marsh Posté le 16-06-2004 à 17:17:10
Sinon, pour voir le plan d'execution, on fait comment 
Parceque j'ai fait un explain, mais voilà quoi... SQL+ m'a répondu très poliment "Explained." et avec ça je vais aller au moins jusqu'à l'arrêt de bus en bas de la rue...
Message édité par Arjuna le 16-06-2004 à 17:17:18
Marsh Posté le 16-06-2004 à 19:09:27
explain plan for select ...
enfin si je me souviens bien.
Cherche sur le web
PS : ta requête est vraiment illisible, comme tu le dis, et surtout énorme ... alors pour peu qu'il te manque un petit index à un endroit, c'est sûr que ça peut prendre des heures 
Marsh Posté le 16-06-2004 à 21:14:48
Comme j'ai dis pour le plan, pas de problème, j'ai fini par trouver.
Sinon, la requête est au contraire simplissime.
Une fois que tu vires les lignes occupées par le select et le group by, c'est juste une table (en fait une vue) qui est liée trois fois de suite a une autre table, en utilisant un index).
Le plan d'éxécution est OK, je tape bien dans tous les indexes voulus (y compris dans la vue, qui par contre renferme une requête bien plus grosse).
En fait, je ne comprends pas la lenteur de la requête pour une raison surtout.
Si je lie la vue seulement deux fois a la table "cap", c'est très rapide (autant se faire se peu vu le nombre de données a traîter et le bordel qu'il y a dans la vue). Mais dès que je lie une troisième fois, ca part en couille.
En plus de me fier au plan d'éxécution, j'ai vérifié au niveau des résultats, et je n'ai pas de doublon ni modification du nombre de ligne entre avant et après les jointures, ce qui implique que mes jointures sont correctes.
En gros, pour resumer la situation :
La vue "soc1.wv_supplier_span" regroupe toutes les commandes d'approvisionnement sur 6 ou 12 mois, je ne sais plus, avec diverse informations :
- Date de création de la commande de réappro
- Produit en question
- Quantité commandée
- Numéro de la ligne dans la commande
- Le fournisseur
- La date de confirmation de la commande
- Date promise par le fournisseur
- Délais accepté pour la gamme du produit
- Délais négocié avec le fournisseur (sans rapport avec les dates relatives à la commande)
- Date de réception des produits
- Business Team chargée de passer la commande
- Date de livraison demandée (si différente de confirmed + leadtime)
- Plus quelques autres fioritures (notamment lorsqu'on a demandé l'échelonnage des livraisons dans le temps)
J'ai fait cette vue qui doit taper dans une quinzaine de tables il y a quelques mois afin d'écrire une macro Excel qui permet de suivre la performance des différents fournisseurs de la boîte, et lever des alertes si un fournisseur livre régulièrement trop tôt ou trop tard, afin d'en faire un palmarès (et savoir auxquels il faut remonter les bretelles), mais aussi des BT pour voir celles qui tirent trop sur la corde en imposant des délais en violation avec ce qui a été négocié avec le fournisseur.
Seulement, les informations telles que le "supplier_leadtime" (délais de réaprovisionnement "normal" du fournisseur) sont exprimées en jours ouvrés.
Hors mes calculs permettant de confronter ce chiffre avec la réalité ne tiennent pas compte des week-end et jours fériers.
Pour chaque calcul, il faut donc que j'aille chercher la somme des jours fermés dans l'intervalle imparti afin de les soustraires de la différence des dates, et c'est le rôle de mes jointures avec la table "cap" en prenant le calendrier 'FER' qui correspond au jours fermés.
En somme, la requête manipule beaucoup de données, mais vue mise a part, le traîtement est simplissimme.
D'ailleurs, on le voit tout de suite dans les conditions de la requête :
|
On a évidement essayé la syntaxe forçant Oracle à utiliser les index voulus, mais après étude du plan d'éxécution, il s'avère qu'il les utilisait déjà (à savoir qu'un index sur "cap" porte sur les champs "codsoc", "codcal" et "datfin", qui sont les seuls utilisés pour les jointures.
Pour ce qui est de la vue, je n'ai pas le code sous la main, mais elle fait un range scan inévitable (mais qui porte sur un faible nombre de lignes - moins de 10 000), et tout le reste on passe par des index uniques. Dans tous les cas, la vue elle-même ne dure que quelques secondes, donc le problème ne vient pas de là.
En bref, je dis pas que ma requête est "parfaite", mais j'ai effectué toutes les vérification classiques, et rien ne me vient... J'ai deux collègues (un soit-disant expert Oracle qui ne m'a pas avancé) et mon CP, surnommé le bidouilleur fou, encore pire que moi... Et ils n'ont rien trouvés non plus
Au niveau du serveur, pn est très loin de le mettre a genoux, ca bouffe même pas 2% du CPU (faut dire que c'est un monstre le serveur). Clairement, j'ai tout retourné dans tous les sens, je vois vraiment pas
Pour simple rappel, la requête ne retourne qu'environ 17 000 lignes (et la vue aussi) donc les jointures avec CAP (dont le nombre de lignes comprises entre les dates doit se limiter a une 20aine maximum), j'ai du mal à comprendre comment ca peut justifier de telles performances minables, même si en effet je demande 3 sums sur chacunes de ces 17 000 lignes...
Là je me suis arrêté à 3, mais au final j'aurai 5 fois la jointure Il me faut vraiment un coup de magie pour résoudre ça
Marsh Posté le 17-06-2004 à 10:04:04
faudrait essayer autrement : genre 3 select qui font chacun une jointure table / vue, et l'intersection du résultat, ou qqch dans le genre.
Marsh Posté le 17-06-2004 à 11:13:42
Je ne peux pas, c'est sous forme de colonnes que je dois récupérer les résultats.
D'autant plus que les traîtement faits dans la macro Excel qui se sert de la requête sont vraiment très complexes (y'a plus de 3 Mo de code), et je suis pas très chaud pour tout réécrire 
Il faut que j'implémente les jours ouvrés d'ici demain soir...
Marsh Posté le 17-06-2004 à 12:07:29
MORT - DE - RIRE (très jaune)
Vous savez, les jointures externes, c'est réputé très lent...
Ben...
J'ai modifié mes jointures sur CAP en jointures externes (en effet, une prériode peut ne pas contenir de jour férier ni de week-end) et...
Je suis passé de plus de 20 minutes à moins de 20 secondes 
Donc voilà, c'est parti pour la grosse qui n'en veut avec les 5 jointures :
|
Et... C'est re-tout lent
Bah ouais, forcément, le bol d'air lié aux jointures externes est complètement bouffé par l'ajout des deux nouvelles jointures
Ha, tiens, je peux en enlever une qui ne sert à rien (mêmes critères de filtre)
Bon, ça me gave, vais bouffer et voir ce qui colle pas dans ma 4° jointure...
Marsh Posté le 17-06-2004 à 14:27:00
Rien a fait, avec ma 4° jointure, je passe de 20 secondes à 14:30 minutes 
Marsh Posté le 22-06-2004 à 10:18:25
Bon ben ca me relourde un max
Ca merde toujours. J'ai vu avec le owner du projet pour voir si certaines données étaient superflues, j'ai pu apporter quelques modifs et optimisations à la requête, mais globalement ça marche guère mieu
On a décidé de passer la query en batch, mais j'arrive même pas a la laisser tourner une fois entière histoire de valider qu'elle ne dure pas plus d'une journée
En plus on a des problèmes réseau là, je craque
Marsh Posté le 27-06-2004 à 20:56:37
Petite question rapide : pourquoi tu as des decode avec des comparaisons à ' ' ??? tu as des dates avec de telles valeurs ???
Est-ce que tu peux essayer d'oragniser ta requête plutôt sous la forme :
select ...
from (select ... from soc1.wv_supplier_span, cap cap1 where <conditions entre cap1 et soc1.wv_supplier_span> ) res1,
(select ... from soc1.wv_supplier_span, cap cap1 where <conditions entre cap2 et soc1.wv_supplier_span> ) res2,
etc.
group by ...
order by ...
Enfin quelquechose dans le genre.
J'ai pas encore une très bonne vue du résultat que tu cherches à obtenir (ça commence à venir), mais il doit y avoir moyen, grâce à de telles requête imbriquées, de simplifier le travail.
Marsh Posté le 27-06-2004 à 21:58:42
Reply
Marsh Posté le 27-06-2004 à 22:43:36
| Beegee a écrit : Petite question rapide : pourquoi tu as des decode avec des comparaisons à ' ' ??? tu as des dates avec de telles valeurs ??? |
Je travaille dans la base d'un ERP appelé GENERIX.
Pour des raisons historiques, et de portage d'un SGBD à l'autre, les champs de dates sont des varchar(8), au format ISO YYYYMMDD
Deplus, pour ces mêmes raisons historiques et de portage d'un SGBD à l'autre, la valeur NULL est interdite dans la base (au début j'ai pu mettre quelques NULL dans certaines tables, l'ERP n'a pas aimé du tout : impossible d'accéder aux lignes en question, ou carrément traîtements totalement incongrus sur les données)
Les champs qui ne sont dont pas renseignés sont donc initialisés à ' '.
Ici, je traîte des commandes d'approvisionnement.
Elles ont une date de creation, une date de validation, une date de confirmation du fournisseur, une date "promise" par le founisseur, une date de réception à quai et une date de mise en stock (plus quelques autres dates)
On récupère toutes les lignes, y compris celles qui ne sont pas encore livrées, etc. Du coup j'ai pas mal de lignes dont les champs date ne sont pas remplis.
Marsh Posté le 28-06-2004 à 08:57:17
Voilà, je suis au taff
Alors...
La vue :
|
Et la requête dans son état actuel :
|
La vue met environ 15/20 secondes à s'éxécuter seule.
Pour le moment je touche à rien, j'ai pas mal de trucs urgents à finir avant (ça aussi c'était urgent, mais vu qu'on trouve pas de solution on l'a mis de côté 
)
Marsh Posté le 28-06-2004 à 09:57:51
ah ouais, la vue est sympa aussi ...
je savais pas que tu joignais fois la même table ... avec une vue qui est elle-même un grosse jointure
Marsh Posté le 28-06-2004 à 10:00:45
Bah vi, j'avais prévenu que la vue était costaud
Cependant, on l'a pas mal simplifiée en supprimant un truc inutile (pas géré pour le moment). Mais à terme, on rajoutera cette information (mais certainement via un procédé plus simple, étant donné qu'on a trouvé un meilleur endroit pour stocker l'info).
Marsh Posté le 28-06-2004 à 10:19:07
La vue contient combien de lignes en gros ?
T'as essayé de créer une table temporaire qui contiendrait les lignes de la vue, et d'utiliser cette table dans ta grosse requête ? Parce que si ça met 15 - 20s pour récupérer des lignes de la vue ... vaut mieux les mettre dans une table, bien indexée, ça devrait pas mal aider.
Marsh Posté le 28-06-2004 à 10:34:57
La vue retourne les 67 000 lignes.
La requête qui tourne dessus ce contente d'ajouter des information.
Passer la vue en table me semble une solution. Mais j'aurais préféré m'en passer. En effet, la requête qui tourne par dessus étant lente, et vu qu'on passe par une page ASP pour récupérer les infos dedans, on est pobligé de mettre moins de 90 secondes pour faire tourner la requête finale (timeout ASP configuré sur le serveur). Ainsi, je suis obligé de stocker en dur les données de la requête finale, et j'aurai préféré tout faire en un coup
Ceci-dit, je vais quand même voir tout ça.
Par contre, ce ne sera pas avant demain, parceque là c'est terrible, j'ai pas eu une minute à moi ce matin, et du coup tout le taff que j'ai a faire aujourd'hui reste à faire
Marsh Posté le 28-06-2004 à 11:07:50
Salut Arjuna,
Bon une première remarque : Pourquoi un 'group by' compliqué alors que dans ton cas il est équivalent à un 'select distinct' ?
Je ne comprend pas du tout pourquoi tu utilises 4 fois la table cap ? ? ?
Ok, c'est pour traiter 4 fois la table, mais un simple OR dans les conditions sur les dates devrait faire la même chose non ?
En fait là, N est le nombre d'enregs de cap, tu traites N*N*N*N = N^4 enregs ! C'est peut-être pour ça que c'est lent...
---------------
Laissez l'Etat dans les toilettes où vous l'avez trouvé.
Marsh Posté le 28-06-2004 à 11:29:21
Mara's Dad > Non, je ne peux pas faire des OR, sinon les SUM seront complètement faussés. En effet, les SUM des différentes colonnes sont totalement différents d'une colone à l'autre car il traîtent des périodes différentes.
Marsh Posté le 28-06-2004 à 11:30:57
Vi, j'ai regardé un peu vite. J'ai cru que les clauses du group by étaient identiques au select...
---------------
Laissez l'Etat dans les toilettes où vous l'avez trouvé.
Marsh Posté le 28-06-2004 à 11:53:39
Ok, je commence à voir.
Mais ça change rien au fait que tu traites N^4 * nbEnreg(wv_supplier_span) enregs...
T'as essayé de faire 4 vues et une requête pour regrouper le tout ?
Ou au moins une vue sur cap qui précalcule les champs nvl(to_date(cap.datfin, 'YYYYMMDD') - to_date(cap.datdeb, 'YYYYMMDD') + 1, 0)
---------------
Laissez l'Etat dans les toilettes où vous l'avez trouvé.
Marsh Posté le 28-06-2004 à 13:34:33
Bon, je laisse tomber mon autre truc, utiliser Visio 2.0 c'est vraiment pas pour moi, y'a pas à dire M$ fait des trucs pas si mal, parcequ'il faut voir de quoi ils sont parti avant de racheter le produit 
Troll à part, me voici donc en train de regarder cette requête.
Selon vos conseils à propos d'une vue sur CAP, je créé cette vue :
|
La requête devient donc :
|
Pour le moment, la requête tourne encore (ça fait 5 minutes). Si quand je reviens de pose cloppe c'est pas fini, je passe la vue principale dans une table temporaire avec des indexs, on verra bien... 
Marsh Posté le 28-06-2004 à 14:17:31
Bon, je crée la table temporaire à partir de la vue principale... (c'te bordel que je suis en train de foutre dans la base de données juste pour une requête à la con !
create table tmp_supplier_span |
Avec l'index qui va bien (enfin... j'espère)
CREATE INDEX TMP_SUPPLIER_SPAN_IDX1 ON |
La requête donne :
select |
Bon, je lance
Marsh Posté le 28-06-2004 à 14:25:15
Bon ben c'est peut-être plus rapide, mais c'est toujours trop lent
Marsh Posté le 28-06-2004 à 15:13:56
tu as enlevé le order by ? c'est voulu ou non ?
(en fait, si tu l'enlèves et que tu ajoutes un 'and rownum < 10' ça te permettra de voir le temps que ça prend pour renvoyer les 10 premières lignes ...
Si tu laisses l'order by, Oracle doit d'abord récupérer toutes les lignes à renvoyer, puis les trier et les afficher ...
Marsh Posté le 28-06-2004 à 15:43:28
Je l'ai viré car je passerai par une table stockée à nouveau (une de plus ) afin de faire mes requêtes finales.
Bon, là j'ai mis 58 minutes pour retourner 16000 lignes...
J'ai un peu peur, j'ai l'impression que j'ai merdé quelquepart, il me semble que ça retournait 67000 lignes avant
Fait chier...
Marsh Posté le 28-06-2004 à 15:45:25
Groumpf ! Nan, en fait y'a bien que 16000 lignes (j'oubliais que vendredi j'ai réussi à faire abstraction d'un niveau de détail dans les commandes d'achat : dénormalisation de MERISE powa 
)
Bon, avec tout ça c'est pas super terrible mais bon... Ca a l'air de marcher
Message édité par Arjuna le 28-06-2004 à 15:45:55
Marsh Posté le 28-06-2004 à 16:09:54
bon ben tu arrives à un temps d'exécution de moins d'une heure, ce qui est déjà pas mal, ça va te permettre de faire quelques runs en changeant des bouts de la requête
Marsh Posté le 28-06-2004 à 17:12:30
Combien de temp ça prend pour une seule colonne ?
Si c'est rapide, tu peux faire 4 vues, une par colonne et ensuite les réunir dans une requête.
Proposition déjà faite, mais pas de réponse...
---------------
Laissez l'Etat dans les toilettes où vous l'avez trouvé.
Marsh Posté le 28-06-2004 à 17:29:09
Ben et je les lie comment mes colonnes ? Nan mais là ça va aller, je vais bosser sur deux niveaux de tables temporaires mises à jour par batch et ça marchera très bien.
Marsh Posté le 28-06-2004 à 17:35:40
| Arjuna a écrit : Ben et je les lie comment mes colonnes ? |
Ben sur les champs communs à prendre dans :
po_number,
po_creation_date,
po_validation_date,
po_line_number,
po_schedule_number,
po_product_code,
bt,
supplier_leadtime,
PO_FIRST_CONFIRMED_DATE,
po_schedule_confirmed_date,
rcp_line_date,
po_schedule_qty,
po_schedule_rcp_qty,
rcp_validation_date,
rcp_creation_date
---------------
Laissez l'Etat dans les toilettes où vous l'avez trouvé.
Marsh Posté le 28-06-2004 à 17:51:56
Je ne suis pas certain que ça améliore les choses. Deplus, si je fais des modifications dans mes requêtes, ça va être super chaud à gérer, là, même si c'est fait en plusieurs fois, une unique modification dans une requête impacte l'ensemble des lignes et des colonnes
Sujets relatifs:
- recuperer resultat requete ds variable
- [MySQL] Requete et casse
- utiliser OCI avec oracle 7
- probleme de requete
- executer une procedure oracle dans la console
- Création hierarchique XML via ADO & Arbre Oracle SQL
- Oracle : Arbre SQL
- Requête SQL - Récupération des 5 lignes les plus récentes
- Query sur DB Oracle via PHP : charge CPU énorme !
- Connexion Oracle JSP [RESOLU]
Marsh Posté le 16-06-2004 à 17:14:52
C'est simple.
J'ai une petite requête de rien du tout qui me gonfle depuis ce matin, et je trouve vraiment pas ce qui ne va pas.
En résumé.
J'ai une vue qui retourne environ 17 000 lignes en 30 secondes.
Afin de faire des calculs de délais en jours ouvrés, je dois la joindre a une table qui stocke les jours ouvrés avec leurs dates.
Ces jours ouvrés étant impliqués dans plusieurs calculs portant sur différents intervalles de dates, je fais autant d'alias que d'intervalles.
Au premier, ca va. Au second, ca va. Je suis autour de une minute.
Et alors arrive le dernier... Et "pouf !"
La requête met 30 minutes.
Pourtant, il y a globalement le même nombre de lignes traîtés (idéalement, les intervalles de date se superposent), et j'utilise les mêmes critères de filtre/calculs...
Ci-dessous la requête :
select wv_supplier_span.supplier_number, wv_supplier_span.supplier_name, wv_supplier_span.po_number,
wv_supplier_span.po_creation_date, wv_supplier_span.po_schedule_validation_date,
wv_supplier_span.po_line_number, wv_supplier_span.po_schedule_number, wv_supplier_span.po_product_code,
decode(wv_supplier_span.bt, ' ', 'OTHERS', wv_supplier_span.bt) bt, wv_supplier_span.supplier_leadtime,
wv_supplier_span.rcp_requested_date, wv_supplier_span.po_schedule_confirmed_date,
wv_supplier_span.rcp_line_date, wv_supplier_span.po_schedule_qty, wv_supplier_span.po_schedule_rcp_qty,
wv_supplier_span.rcp_validation_date, wv_supplier_span.rcp_creation_date,
to_char(to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD'), 'YYYYMM') PO_CREATION_DATE_MONTH,
(to_date(wv_supplier_span.po_schedule_confirmed_date, 'YYYYMMDD') - to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD')) - sum(to_date(cap1.datfin, 'YYYYMMDD') -to_date(cap1.datdeb, 'YYYYMMDD')) "Confirmed - Creation",
(to_date(wv_supplier_span.po_schedule_requested_date, 'YYYYMMDD') - to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD')) - sum(to_date(cap2.datfin, 'YYYYMMDD') - to_date(cap2.datdeb, 'YYYYMMDD')) "Requested - Creation",
-- A virer si pas cap3
(to_date(decode(wv_supplier_span.rcp_line_date, ' ', '99991231', wv_supplier_span.rcp_line_date), 'YYYYMMDD') - to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD')) - sum(to_date(cap3.datfin, 'YYYYMMDD') - to_date(cap3.datdeb, 'YYYYMMDD')) "Receipt - Creation",
-- Ne pas virer la suite
wv_supplier_span.supplier_leadtime,
to_date(decode(wv_supplier_span.rcp_line_date, ' ', '99991231', wv_supplier_span.rcp_line_date),
'YYYYMMDD') - to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD') - wv_supplier_span.supplier_leadtime "Leadtime violation",
to_date(decode(wv_supplier_span.rcp_line_date, ' ', '99991231', wv_supplier_span.rcp_line_date), 'YYYYMMDD') - to_date(wv_supplier_span.PO_SCHEDULE_CONFIRMED_DATE, 'YYYYMMDD') "Confirmed date violation"
--
from cap cap3, cap cap2, cap cap1, soc1.wv_supplier_span
-- Si j'enlève "cap cap3" du from ca va très vite
/* cap cap_idx4*/
where cap1.codsoc = 0
and cap1.codcal = 'FER'
and cap1.DATFIN between wv_supplier_span.PO_CREATION_DATE and wv_supplier_span.po_schedule_confirmed_date
and cap2.codsoc = 0
and cap2.codcal = 'FER'
and cap2.DATFIN between wv_supplier_span.PO_CREATION_DATE and wv_supplier_span.po_schedule_requested_date
-- A virer si pas cap3
and cap3.codsoc = 0
and cap3.codcal = 'FER'
and cap3.DATFIN between wv_supplier_span.PO_CREATION_DATE and decode(wv_supplier_span.rcp_line_date, ' ', to_char(sysdate, 'YYYYMMDD'), wv_supplier_span.rcp_line_date)
-- Ne pas virer la suite
group by
supplier_number, supplier_name, po_number, po_creation_date, po_schedule_validation_date,
po_line_number, po_schedule_number, po_product_code, decode(bt, ' ', 'OTHERS', bt), supplier_leadtime,
rcp_requested_date, po_schedule_confirmed_date, rcp_line_date, po_schedule_qty, po_schedule_rcp_qty,
rcp_validation_date,
rcp_creation_date,
to_char(to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD'), 'YYYYMM'),
supplier_leadtime,
to_date(wv_supplier_span.po_schedule_confirmed_date, 'YYYYMMDD') -
to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD'),
to_date(wv_supplier_span.po_schedule_requested_date, 'YYYYMMDD') -
to_date(wv_supplier_span.PO_CREATION_DATE, 'YYYYMMDD'),
to_date(wv_supplier_span.po_schedule_requested_date, 'YYYYMMDD') -
to_date(wv_supplier_span.PO_SCHEDULE_CONFIRMED_DATE, 'YYYYMMDD')
order by
to_char(to_date(PO_CREATION_DATE, 'YYYYMMDD'), 'YYYYMM'), BT, supplier_name