Discussion :

[Le nain Attila]

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 :

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

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

[zekey]

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.

[Le nain Attila]

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

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)

[zekey]

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)

[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
-------------------------------------------------------------------------------
Edit 1 :
Je viens de tester l'inversion de la jointure, sans surprise, c'est le même résultat qu'avant
-------------------------------------------------------------------------------
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

[Le nain Attila]

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

[aline]

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

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?

[zekey]

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

[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).

[Le nain Attila]

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

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

[Le nain Attila]

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

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

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

[LeoAnderson]

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

[Le nain Attila]

En ce qui conscerne le COALESCE, votre remarque est judicieuse ... 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

[aline]

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 :

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

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 ?

[Le nain Attila]

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.

[aline]

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

[Le nain Attila]

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

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

[aline]

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?

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 :

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

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

[Le nain Attila]

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

Oui, c'est fait ...

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.

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 :

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

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

[bouyao]

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.