Ralentissement d'une requête au fur et à mesure

[gg14bis]

Bonjour à tous,

Je travail sur une petite application censée utiliser les données d'un planning prévisionnel de prospecteur/client.

Cette application doit régulièrement interroger la table de planning pour savoir si chacun des prospecteur se rend bien à ses rendez-vous.
J'ai plusieurs tables a utiliser (ces tables me sont imposées, je ne peux qu'ajouter des champs mais je ne peux ni changer leurs liaisons, ni enlever des éléments) :
planning : table des rendez-vous
prospect : table des prospecteurs (salariés)
detail_prospect : table donnant des informations complémentaires sur les prospecteurs.
client : table des clients
detail_client : table donnant des informations complémentaires sur les clients
produit : table de produits présentés

J'ai donc construit la requête suivante :

Code :

1
2
3
4
5
6
7
8
SELECT * FROM planning, prospect, detail_prospect, client, detail_client, produit
WHERE planning.id_prostect = prospect.id_prospect
AND prospect.id_dt_prospect = detail_prospect.id_dt_prospect
AND planning.id_client = client.id_client
AND client.id_dt_client = client_detail.id_dt_client
AND planning.id_produit=produit.id_produit
AND planning.pla_debut_confirme IS NULL
AND (to_char(pla_date_debut,'yyyymmdd')*10000)+(pla_hre_debut*100)+pla_min_debut >= '201110130000' AND (to_char(pla_date_debut,'yyyymmdd')*10000)+(pla_hre_debut*100)+pla_min_debut <='201110130900'

Cette requête me renvoi bien les informations souhaitées sauf que je l'exécute toutes les 5 minutes et qu'au fur et à mesure de la journée les performance se dégrade.
Il faut savoir que simultanément le planning prévisionnel évolue (ajout de rdv, suppression de rdv, modification de rdv, validation d'un RDV quand prospect confirme son arrivé chez un client).

J'avoue que je ne savais pas vraiment dans quelle catégorie poster cette demande, j'espère avoir choisi la bonne catégorie. Je ne m'y connais pas en paramétrage de base... (juste en développement)
J'ai remarqué que la commande : analyze table planning compute statistics;
Améliore le traitement mais très rapidement le traitement ralentit de nouveau...

Merci d'avance pour votre aide.

[StringBuilder]

Première question :
"Au cours de la journée, les performances se dégradent"
=> Est-ce que le lendemain, les performances sont de nouveau bonnes ?
=> Après un redémarrage de la base ?
=> Après un redémarrage de l'application cliente ?

Deuxième question :
=> La volumétrie augmente-t-elle ?
=> Dans quelles proportions ?
=> Quelle est la volumétrie actuelle ?

Troisième question :
=> Tes statistiques sont-elles à jour ?
=> Quand sont-elles recalculées ?
=> Quand tes index sont-ils reconstruit ?
=> Est-ce qu'après ces opérations de maintenance, la requête est de nouveau rapide ?

Je serais tenté de dire que s'il y a beaucoup de petits mouvements, tes statistiques sont vites faussées et tes index déséquilibrés.
Du coup Oracle patine dans la choucroute à choisir un plan d'exécution inadapté et des index contre-performants.

[StringBuilder]

Essaie de faire un index organisé en cluster sur l'heure des rendez-vous plutôt que sur la clé primaire, je pense que ça évitera de déséquilibrer trop vite tes index et stats (ça évite de mélanger les informations non chronologiques, dans l'ordre de leur insertion)

[Waldar]

La requête en l'état sent quand même le XXième siècle.
Une bonne pratique, on ne fait jamais de select *.
On récupère uniquement les colonnes dont on a besoin.

Une autre bonne pratique, on alias les tables. Ce n'est pas obligatoire, mais ça permet de raccourcir le code d'une requête et d'en faciliter la relecture.

Toujours pour avancer, on utilise plutôt les jointures ANSI. Ça ne change rien aux performances, mais ça laisse plus de souplesse dans les jointures externes, et ça facilite aussi la relecture d'une requête en différenciant les jointures des filtres.

Enfin, le dernier filtre sur la plage, est loin d'être bien écrit !
Vous transformez des dates en texte, sur lesquels vous faites des additions et vous comparez le tout à une chaîne de caractères.

En supposant que les colonnes pla_hre_debut et pla_min_debut de la table planning soient de type numériques, on devrait pouvoir réécrire quelque chose comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * -- pas étoile donc !
  FROM planning pla
       INNER JOIN prospect prp
         ON prp.id_prospect = pla.id_prostect -- prosTect ?
       INNER JOIN detail_prospect dpr
         ON dpr.id_dt_prospect = prp.id_dt_prospect
       INNER JOIN client cli
         ON cli.id_client = pla.id_client
       INNER JOIN detail_client dcl
         ON dcl.id_dt_client = cli.id_dt_client
       INNER JOIN produit prd
         ON prd.id_produit = pla.id_produit
 WHERE pla.pla_debut_confirme IS NULL
   AND trunc(pla.pla_date_debut, 'DD') + (pla.pla_hre_debut * 100) + pla.pla_min_debut BETWEEN trunc(sysdate, 'DD') AND trunc(sysdate, 'DD') + interval '9' hour;

À mon avis on peut encore améliorer cette dernière expression, mais là il me faut plus d'info sur les types donc, mais aussi sur les règles à appliquer.

Une dernière chose, je suis inquiet sur la modélisation de votre base, voir les id_détail dans les tables à priori mères, ça me paraît être de l'héritage un peu raté !
Pareillement sur votre table planning, vous avez trois colonnes.
Une pour la date, une pour les heures et une pour minutes, alors que toutes ces informations auraient leur place dans la première.

[mnitu]

Quelle version d’Oracle ?
Quelle volumétrie ? Quelle sont les indexes ?
Est-ce que votre requête utilise des variables de liaison (binding variables) ou des littéraux en dure comme dans votre exemple ?
Est-ce que vous pouvez tracer (trace SQL) votre traitement ?
Dans votre cas plusieurs phénomènes pourraient expliquer un ralentissement donc il faut plus des détails.

[Mohamed.Houri]

Envoyé par StringBuilder

Essaie de faire un index organisé en cluster sur l'heure des rendez-vous plutôt que sur la clé primaire, je pense que ça évitera de déséquilibrer trop vite tes index et stats (ça évite de mélanger les informations non chronologiques, dans l'ordre de leur insertion)

Voilà une suggestion qui ne se base sur aucun fait avéré. Comment avez-vous déduit sur la base des seules informations données, c'est à dire une requête dont la performance se dégrade au fur et à mesure de l'avancement du temps d'une journée, que le problème ici est non seulement du à un déséquilibre des indexes (en passant il va bien falloir définir ce qu'est un index déséquilibré) mais également que la solution serait de faire un index organisé en cluster sur l'heure des rendez-vous plutôt que sur la clé primaire?

Je suggérerai ici de

1. avoir l'explain plan au matin quand tout va bien
2. avoir un autre explain plan lorsque la requête performe mal

Et analysez la différence.

[ORA-007]

Bonjour,

Si il s'agit bien d'expliquer le comportement régressif d'une requête tout au long de la journée, et bien il va falloir tracer, et tracer large.

En effet des traitements batch ou une montée des transactions utilisateurs, bref une sollicitation de la base croissante peut expliquer ce phénomène.

Y a t il un comportement similaire avec d'autre requêtes qui a déjà été constaté ?

Si tu as Statspack de dispo sur ton environnement, cela pourrait déjà etre un debut pour pouvoir analyser.

http://oracle.developpez.com/guide/tuning/statpack/

http://download.oracle.com/docs/cd/B...3/statspac.htm

Sinon dans un premier temps il faudrait voir l'explain plan + volumetrie des tables + index + checker les statistiques.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT /*+ gather_plan_statistics */
   * 
  FROM planning pla
       INNER JOIN prospect prp
         ON prp.id_prospect = pla.id_prostect -- prosTect ?
       INNER JOIN detail_prospect dpr
         ON dpr.id_dt_prospect = prp.id_dt_prospect
       INNER JOIN client cli
         ON cli.id_client = pla.id_client
       INNER JOIN detail_client dcl
         ON dcl.id_dt_client = cli.id_dt_client
       INNER JOIN produit prd
         ON prd.id_produit = pla.id_produit
 WHERE pla.pla_debut_confirme IS NULL
   AND trunc(pla.pla_date_debut, 'DD') + (pla.pla_hre_debut * 100) + pla.pla_min_debut BETWEEN trunc(sysdate, 'DD') AND trunc(sysdate, 'DD') + interval '9' hour;
 
SELECT *
FROM
    TABLE (dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));

PS : La dégradation peut etre chiffrée à quelle hauteur : x2,x3,xN ?