[Perf/Bug??]9.2.0.1-Problème de performance bizarre.

Bonjour à tous,

Je travaille actuellement sur une base 9.2.0.1 et je rencontre un problème de performance très bizarre sur une procédure particulière, mais surtout sur un ordre particulier de cette procédure.

Voici mon soucis :

Lorsque je lance ma procédure PL/SQL suite à un arrêt/relance de ma
base de donnée, elle s'exécute en 3min. Si je lance la même procédure
juste après un premier passage, même deux jours après, elle met alors
20min à s'exécuter.

Après le lancement des traces Oracle, je me suis aperçu qu'un ordre en
particulier, dont le plan d'exécution est affiché comme étant identique,
consommait dans un cas 2' cpu (2052 accès disque) et dans le
second cas 1000' cpu (20 millions d'accès disque). Il s'agit de l'ordre
suivant :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT SUM(a.montant) FROM elemp a, regroup b WHERE a.elem=b.elem AND a.societe=:b4 AND a.idpeop=:b3 AND a.date=:b2 AND a.type='R' AND a.temoin='N' AND b.regrp=:b1 GROUP BY b.regrp

---

Le plan d'exécution indique qu'Oracle passe bien par l'index positionné
sur la table dans les deux cas. Encore une petite chose, la table est
déclarée en PARALLEL.

Travaillant sur Oracle depuis déjà quelques temps, je dois reconnaître que je suis assez désarmé face à ce problème. En effet, je suis passé par tous les paramètres d'optimisation que je connais, mais là, çà fait déjà 4 jours que je suis dessus et je commence à être sec :roll:
-------------------------------------------------------------------------------
Edit :
Encore une petite précision, je ne lance pas la procédure toute seule. En fait, j'exécute des opérations d'optimisations à l'intérieur d'un script. C'est ce script que je lance dans les deux cas.

Enfin, mon sentiment, au vue du nombre de lignes interrogé dans chacun des cas, est qu'Oracle semble lire des lignes "fantomes", inexistantes, car détruites au début de mon script (puis lancement d'un commit), avant toutes les opérations d'optimisations.

Salut,

Combien de lignes sur tes tables ?
As tu essayé les trucs à la con genre inverser a.elem=b.elem (fonction de la là ou il y a le plus de données)
Supprimer le group by pour voir la différence
Et surtout enlever le parrallel parce que desfois c'est bien pire avec que sans.

Pour l'instant je suis en phase de test avec environ 4000 dossiers, possédant chacun environ 25 lignes dans la table elemp (a terme 120000 dossiers environ, d'où le soucis) et environ 400 lignes dans la table regroup (équivalence à terme).

Pour les trucs à la con, du genre inverser la jointure, je vais essayer, mais je n'y crois pas, car l'index est déjà utilisé par Oracle dans son plan d'exécution. J'essaie juste après.

Le "group by" ne peut pas être supprimé par contre, car il s'agit d'une opération qui doit justement renvoyer une somme par rapport à chaque valeur de la rubrique regrp. C'est bêtement fonctionnel :wink:

Enfin, le paramètre PARALLEL, je l'ai rajouté sur la table après coup, parce qu'avant, la procédure m'était 25 minutes dans les deux cas 8)

Tu dis que tu lances ce truc à l'intérieur d'un script mais que ce passe t'il si tu le lance simplement comme ca dans sqlplus.
(Concernant les trucs à la con je suis d'accord mais bon desfois les voies de l'optimizer sont impénétrables)

En fait, le script ne contient qu'un delete plus recalcul stat, recalcul index ... Le tout étant identique dans les deux cas. Dans SQLPLUS, c'est pareil ... Sorry :oops:
-------------------------------------------------------------------------------
Edit 1 :
Je viens de tester l'inversion de la jointure, sans surprise, c'est le même résultat qu'avant :cry:
-------------------------------------------------------------------------------
Edit 2 :
Sur les conseils d'un éminent collègue, j'ai intégré un tablespace UNDO dans ma base en lieu et place des oldies rollback segments ... Le problème persiste :oops:

J'ai encore fait quelques tests hier ... Pas de news positives ... Personne n'a d'idée pour ce problème ??? :cry:

Citation:

---

Envoyé par Le nain Attila

En fait, le script ne contient qu'un delete plus recalcul stat, recalcul index ... Le tout étant identique dans les deux cas. Dans SQLPLUS, c'est pareil ... Sorry :oops:

---

Tu peux nous en dire plus sur le delete?

Si tu delete toute tes rows, le problème vient peux être de la car l'espace liberé n'est pas réalloué. D'un autre coté, si c'est cela, un arret/redémarrage de la base ne changera rien.

recalcul d'index = reconstruction?
Si oui, pourquoi?


Peux-tu nous poster tes traces dans le bon et le mauvais cas pour que l'on puisse se faire une idée?

Quel est l'état de fragementation de ta base ?
Tu peux faire un truncate à la place du delete ?

Ma base n'est pas du tout fragmentée. Les tables sont en un seul gros morceau et je passe un coalesce sur les divers tablespaces avant de lancer ma procédure.

En ce qui conscerne le delete, il s'agit d'un truc tout simple avec une clause where basée sur deux colonnes fortement discriminantes. Donc rapide et suivi d'un commit.

De plus, je ne peux pas véritablement faire un truncate à la place de mon delete étant donné qu'une majorité des données doivent-être conservées.

Enfin, je recalcul mes indexes et mes statistiques, pour m'assurer qu'Oracle passera bien par les structures d'optimisation que j'ai mis en place et parce que le volume de mise à jour global de ma procédure est élevé (insert/delete).

Voici la trace quand tout va bien (après redémarrage) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 7926 0.19 0.24 0 0 0 0 Fetch 7926 2.62 8.62 2081 185122 0 2144 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 15853 2.81 8.87 2081 185122 0 2144   Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (..) (recursive depth: 1)   Rows Row Source Operation ------- --------------------------------------------------- 2144 SORT GROUP BY NOSORT 2567 TABLE ACCESS BY INDEX ROWID ELEMP 30158 NESTED LOOPS 19665 TABLE ACCESS FULL REGROUP 2567 INDEX RANGE SCAN INX_ELEMP (object id 21828)     Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 2144 SORT (GROUP BY NOSORT) 2567 NESTED LOOPS 30158 TABLE ACCESS (BY INDEX ROWID) OF 'ELEMP' 19665 INDEX (FULL SCAN) OF 'INX_ELEMP' (NON-UNIQUE) 2567 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REGROUP' 0 INDEX (UNIQUE SCAN) OF 'PK_REGROUP' (UNIQUE)

---

[Edit] Post corrigé par mes soins avec la fameuse cote 'Code' ...

Voici la seconde partie, quand çà và pôas :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 7926 0.40 0.39 0 0 0 0 Fetch 7926 1073.58 1484.09 19459911 19702092 0 2144 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 15853 1073.99 1484.49 19459911 19702092 0 2144   Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (..) (recursive depth: 1)   Rows Row Source Operation ------- --------------------------------------------------- 2144 SORT GROUP BY NOSORT 2567 NESTED LOOPS 154038 TABLE ACCESS BY INDEX ROWID ELEMP 154038 INDEX FULL SCAN INX_ELEMP (object id 21832) 2567 TABLE ACCESS BY INDEX ROWID REGROUP 154038 INDEX UNIQUE SCAN PK_REGROUP (object id 14706)     Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 2144 SORT (GROUP BY NOSORT) 2567 NESTED LOOPS 154038 TABLE ACCESS (BY INDEX ROWID) OF 'ELEMP' 154038 INDEX (FULL SCAN) OF 'INX_ELEMP' (NON-UNIQUE) 2567 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'REGROUP' 154038 INDEX (UNIQUE SCAN) OF 'PK_REGROUP' (UNIQUE)

---

N'empêche merci pour votre intérêt.

Citation:

---

Envoyé par Le nain Attila

Ma base n'est pas du tout fragmentée. Les tables sont en un seul gros morceau et je passe un coalesce sur les divers tablespaces avant de lancer ma procédure.

En ce qui conscerne le delete, il s'agit d'un truc tout simple avec une clause where basée sur deux colonnes fortement discriminantes. Donc rapide et suivi d'un commit.

De plus, je ne peux pas véritablement faire un truncate à la place de mon delete étant donné qu'une majorité des données doivent-être conservées.

Enfin, je recalcul mes indexes et mes statistiques, pour m'assurer qu'Oracle passera bien par les structures d'optimisation que j'ai mis en place et parce que le volume de mise à jour global de ma procédure est élevé (insert/delete).

---

Je n'ai pas lu tout votre problème :oops: mais j'attire votre attention sur l'action du coalesce : ça ne fait que fusionner les extents libres contigus en un seul !
Si vos tables sont composés de 1500 extents, ça ne changera rien !
De plus, le delete ne libèrera pas d'extents.

En ce qui conscerne le COALESCE, votre remarque est judicieuse :D ... En fait, c'est un des derniers tests que j'ai réalisé, n'ayant plus beaucoup d'idée.

Je viens de vérifier le nombre de fragments des tables de ma base de données. La situation n'est pas aussi rose que je l'aie décrite. Cependant, les tables fragmentées ne sont pas celles qui sont utilisées dans l'ordre problématique ... çà aurait été trop beau :cry:

C'est la première fois que je vois deux plans d'executions pour la même requete.
un row source opération (?!) et un execution plan.

En tout cas, le plan d'éxécution n'est pas le même dans les deux cas et le nested loop + l'acces par index ne sembnle pas adapté.

Essaye cette requête:

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT /*+full(regroup)*/ SUM(a.montant) FROM elemp a, regroup b WHERE a.elem=b.elem AND a.societe=:b4 AND a.idpeop=:b3 AND a.date=:b2 AND a.type='R' AND a.temoin='N' AND b.regrp=:b1 GROUP BY b.regrp

---

au fait, la colonne regroup est-elle discriminante?

De quoi est composé l'index PK_REGROUP ?

En fait "regroup" n'est pas une colonne. Il s'agit d'une table dans laquelle une liste de codes (elem) est associée à un identifiant unique (regrp). La colonne discriminante est donc bien regrp, puisque je veux obtenir avec cette requête une liste de montant par rapport à cet identifiant ...

PK_REGROUP est un index issu du positionnement d'une primary key sur la colonne elem de la table REGROUP.

Merci pour ton aide ... Je vais essayer de positionner le paramètre que tu proposes, puis je transmettrais le résultat.

Désolé, je voulais dire la colonne regrp est-elle disriminante?
et non pas la colonne regoup!


Si cette colonne est vraiment disriminante, alors je te conseille de créer un index dessus.
Il parait hallucinant et le roblème vient donc de la que ton optimiseur choisisse de passer par PK_REGROUP qui n'est clairement pas le bon index.

Donc FULL(regroup) ou même peux être mieux, créer un index sur REGRP

La table regrp faisant 600 lignes, le hint n'a pas fonctionné ...

En fait, la table compte environ 600 lignes et seulement 7 valeurs de regrp pour quasi 600 valeurs différentes de elem !

Pour répondre à ta question, et c'est étonnant, toutes proportions gardées, la table regroup possède un index composite INX_REGROUP, positionné sur les colonnes regrp et elem ... A la réflexion, je vais le corriger, pour ne mettre que la colonne regrp :wink:

Je vais voir si c'est mieux ... Des fois à chercher trop loin, on ne se rend pas compte qu'une chose toute simple est sous nos yeux :roll:

Citation:

---

Envoyé par Le nain Attila

La table regrp faisant 600 lignes, le hint n'a pas fonctionné ...

---

Cela n'a rien à voir.
Que se passe t'il quand tu fais un hint? Il utilise l'index?
Cela veux dire qu'il y a une faute de syntaxe!

Au fait, les stats sont calculées sur tes deux tables ainsi que sur tout les index e tes tables?

Citation:

---

Envoyé par Le nain Attila

En fait, la table compte environ 600 lignes et seulement 7 valeurs de regrp pour quasi 600 valeurs différentes de elem !

---

Donc, l'index sur regrp n'est pas du tout discriminant!
Il ne faux surtout pas l'utiliser.
Quand à la colonne elem, elle est peux être discriminante, mais on ne s'en sert pas comme restriction. donc elle ne sert à rien.

Je maintiens.
Il faux faire un table acces full sur la table REGROUP.
c'est d'ailleurs ce que semble faire Oracle quand tu redemarres ta base mais pas par la suite.


Je viens de trouver l'erreur:

La voici:

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT /*+full(b)*/ SUM(a.montant) FROM elemp a, regroup b WHERE a.elem=b.elem AND a.societe=:b4 AND a.idpeop=:b3 AND a.date=:b2 AND a.type='R' AND a.temoin='N' AND b.regrp=:b1 GROUP BY b.regrp

---

En effet, tu renome regroup en b, il faux en tenir compte dans le hint....

Citation:

---

Envoyé par aline

Au fait, les stats sont calculées sur tes deux tables ainsi que sur tout les index e tes tables?

---

Oui, c'est fait ...

Citation:

---

Envoyé par aline

Citation:

---

Envoyé par Le nain Attila

En fait, la table compte environ 600 lignes et seulement 7 valeurs de regrp pour quasi 600 valeurs différentes de elem !

---

Donc, l'index sur regrp n'est pas du tout discriminant!
Il ne faux surtout pas l'utiliser.
Quand à la colonne elem, elle est peux être discriminante, mais on ne s'en sert pas comme restriction. donc elle ne sert à rien.

---

Le second index dont je parlais ici, INX_REGROUP, n'était de toute façon pas utilisé dans le plan d'exécution, le problème venant d'ailleurs peut-être de là, même si sa manifestation était étrange.

En ce qui conscerne la colonne elem, c'est elle qui fait la jointure de la table regroup avec la table elemp et sert en fait de primary key dans le sens ou un elemp.elem n'est valide que si regroup.elem existe.

Citation:

---

Envoyé par aline

Je maintiens.
Il faux faire un table acces full sur la table REGROUP.
c'est d'ailleurs ce que semble faire Oracle quand tu redemarres ta base mais pas par la suite.

Je viens de trouver l'erreur:

La voici:

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT /*+full(b)*/ SUM(a.montant) FROM elemp a, regroup b WHERE a.elem=b.elem AND a.societe=:b4 AND a.idpeop=:b3 AND a.date=:b2 AND a.type='R' AND a.temoin='N' AND b.regrp=:b1 GROUP BY b.regrp

---

En effet, tu renome regroup en b, il faux en tenir compte dans le hint....

---

Ouff ... On est vendredi ... J'ai trop de mal, j'avais même pas vue l'erreur :wink: :wink:

Bonjour,

Je n'ai pas lu tous le sujet, je regarderai ca à lundi.
Mais ce que j'ai remarqué que c'est normal après le démarrage de la base les premières requêtes sont lentent car oracle doit faire des analyses et d'autres choses ce qui prends beaucoup de temps, aprés, le lancement de la même requête est plus rapide car tous ce qu'il a besoin se trouve dans la mémoire cache.