Discussion :

[bidi]

ok merci, je vais tester ça mais là du coup j'ai une réunion, je vous tiens au courant dès demain.

Merci pour l'aide

[lallio]

pour les stast:
si tu veux faire toutes les tables d'un schema

Code :

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

exec dbms_stats.gather_schema_stats(user, cascade=>true);

Laurent

[bidi]

tadahhh

A force de chercher j'ai compris ^^ la réponse était dans vos commentaires plus haut mais comme je ne comprenais pas bien de quoi vous parliez ;p

La requête génère beaucoup trop de LIOs, du coup c'est super lent. En full scan c'est déjà beacoup plus rapide car, j'imagine qu'il ne sature pas en IO mémoire. Et en Hash_join je passe sous la demi seconde (je viens de 32 qd meme ;p)

Code :

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

1
2
3
4
5
SELECT /*+ use_hash(orderer) */count(1)
	FROM FILE_DECLARATION fd
	INNER JOIN PARTICIPATION_ACTOR pa ON pa.file_id=fd.file_id
	INNER JOIN ACTOR orderer ON orderer.id= pa.actor_id AND (upper(orderer.DENOM_LNM) LIKE 'ARMI%' )
	WHERE (upper(fd.CITY_LNM) = 'GENT') AND(fd.POSTCODE = '9000');

Pas évident quand même à cerner tout ça quand on ne baigne pas dedans :-)
De ma pauvre connaissance index=performance. Or, ici, on voit une exception.

Merci à tous pour votre précieuse aide ^^

[mnitu]

Forcer le plan d’exécution par un sql_hint est une solution du désespoir à utiliser seulement en urgence pour gagner du temps (il y a quelques exceptions mais ce n’est pas le cas). Elle peut être utilisé pour vérifier qu’une meilleur solution est disponible pour l’optimiseur.
Dans aucun cas cella ne peut être une solution pérenne.

[lallio]

bonjour,

"Solution du désespoir" me parait exagèré. Le fait de mettre un hint permet de voir qu'ORACLE ne choisit pas le plan optimimum. Lorsqu'on connait l'ensemble des paramtres qui influent sur un plan sans parler des bugs oracle ou différence entre les versions, ca peut etre une solution viable.

Car jouer avec les paramètres pour arriver au plan voulu nécessite de verifier toutes requetes qui transite sur la base au risque de regler un pb en en crééant un bon paquet.

Rien que pour t'amuser prends une requete complexe et joue avec le parametre optimizer_features_enable et tu seras surpris des différence.

Alors quand en plus tu n'as pas la main sur le serveur pour modifier des params, le hint peut etre une solution qui n'est pas forcement une solution "du desespoir".

Laurent

[Mathias44]

Bonjour,

Dans ces types de cas, j'ai pu constater d'importantes modifications de plans - positivement - en calculant des statistiques système. Effectivement ça oblige à plus de tests de non régression, et souvent plus d'investigations mais je rejoins mnitu sur le côté non pérenne des hints qui valables à un instant T, ne le sont plus forcément lorsque la distribution des données a changé. Question de point de vue

[mnitu]

bonjour,

"Solution du désespoir" me parait exagèré. Le fait de mettre un hint permet de voir qu'ORACLE ne choisit pas le plan optimimum. ....

Bonjour Laurent,

Bref si tu préfère c’est une solution du DBA et non pas de développeur et c’est une mauvais solution, il n’y a pas de doute ! Le bon plan d’exécution peut être trouvé par l’optimiseur mais peut être que le développeur doit comprendre lui aussi ce qu’il fait.

[lallio]

Mon point de vue rejoint globalement le votre. C'est le coté "desespoir" , "en aucun cas".
Je travaille sur un datawharejouse.
Etape 1 : j'injecte plusieurs millions de lignes dans une table (mes stats sont calculés en periode de week end )
Etape 2 : une fois ces millions de lignes injectés, je fais une un traitement pour inserer ces nouvelles données dans des datamarts pour agreger les données. (mais uniquement les données du jour)

ayant un index sur la table et pas de stats ORACLE décide d'utiliser l'index, moyen pour les perfs vu que ma table est partiionnée et que le traitement va se faire sur toute la partition.
Un hint indiquant de faire un fulls scan ne me parait pas etre totalement un acte de désespoir.Quelque soit la volumétrie journalière, mon traitement sera indentique et le full sera plus efficace que l'usage de l'index.

Si je comprends bien,il faut etre puriste et ne pas utiliser de hint mais calculer les stats sur ma partitions à plusieurs millions de lignes et prendre le risque de voir un traitement de nuit déborder sur la journée et nuire à l'activité des services.

Je n'ai pas envie de rentrer dans une polémique et ce n'était pas l'objet du post.
Je reste cependant tout a fait d'accord que sur la nécessité de faire attention et d'anticiper des changement de volumétrie....

Laurent

[lallio]

bonsoir,

"Bref si tu préfère c’est une solution du DBA et non pas de développeur".

Pour avoir beaucoup travaillé avec des editeur, une bonne pratique serait également de ne pas les "opposer" et de les faire travailler ensemble.
Encore une fois, je ne dis pas qu'il faille utiliser hint sans comprendre les tenant et les aboutissant, mais c'est un outil fourni par Oracle dans le cadre d'optimisation et ca n'est pas uniquement pour créer de l'option inutile.
Donc le "desespoir" et "aucun cas" me parait exagèré.

Laurent.

[mnitu]

Laurent,

Analyse la requête. Regarde le plan d'exécution. Relis l'ensemble des discutions. Et maintenant dit moi qu'il te faut un hint pour t'en sortir.
Un hint c'est comme une valeur en dur! C'est mauvais! Ce qu'il répare sur une base il peut bien le caser ailleurs ou plus tard. Cette une technique faisant partie de ce que j'appelle: la programmation orienté maintenance, POM. C'est de l'optimisation du désespoir: «*nous ne savons pas ce qui se passe, nous ne comprendrons pas pourquoi il y a tant de lecture, mais il faut qu'on trouve une solution toute suite. Et après on verra*».
Il y a quelques exceptions mais encore une fois c'est ne pas le cas ici. Dans ce cas il peut être utile pour prouver qu'un meilleur plan existe. Et ce n'est pas forcement le hash sur la table ACTOR. Voilà.

PS. Sinon t'as raison et je rectifie, c'est une technique employées par certains DBA et développeurs.

[bidi]

Attention, il faut bien tout remettre dans son contexte ^^

1) Il est clair, je ne suis pas DBA et, en vous voyant parler, je vois que je ne m'y connais pas dutout quand on arrive à un niveau aussi bas. A la base, je suis censé designer des applications, pas faire des optimisations bas niveau sur des requêtes qui me semblent bancales...

2) Je n'ai absolument pas la main sur les serveurs db, ça limite déjà fortement ce que je peux envisager comme solution.

3) La db est certainement pourrie à la base. Un tas d'applications tournent dessus, tout est mélangé, les connexions sont saturées, bref, c'est pas super de ce côté là non plus.

Il est dès lors très difficile, en mettant tous ces points ensemble, de trouver une solution viable à long terme qui ne soit pas du bricolage. Bon, ça ne m'enchante pas non plus mais on fait avec ce qu'on a

Pour ma part, j'ai fait tout ce que je pouvais, en retournant la requête dans tous les sens, en faisant des 10aines de recherches sur le net pour comprendre ce qu'il se passe et j'ai compris le pourquoi. Reste le comment mais là je sature ^^.

Si vous avez une meilleure solution, je suis preneur.

En tout cas, tout ça m'aura déjà appris pas mal de choses. Merci pour vos explications :-)

[bidi]

Heu, je reviens encore avec ça mais est-ce que ça:

Code :

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

1
2
3
4
5
6
 
  SELECT distinct(count(d.file_id)),
	FROM FILE_DECLARATION fd
	INNER JOIN (select pa.file_id,pa.actor_id from PARTICIPATION_ACTOR pa where pa.ACT_PART_CODE='orderer') j ON j.file_id=fd.file_id	
       INNER JOIN (select orderer.id from ACTOR orderer where orderer.DENOM_LNM Like 'UNIV%') k on k.id=j.actor_id
	where upper(fd.CITY_LNM) = 'GENT' AND fd.POSTCODE = '9000' AND fd.DECLARATION_NUM IS NOT NULL;

est une bonne façon de faire? (les selects au niveau des joins)

Merci

[bidi]

Oubliez ça, j'ai fumé ;p c'est con ^^

[mnitu]

Analyse les tables et les indexes correctement. Avec des informations incorrectes comment veut tu que l’optimiseur trouve un plan correcte.
Ensuite si les problèmes persistent poste ta requête telle quelle est exécutée (faire attention aux variables de binding) et le plan d’exécution. Et on verra.

[mnitu]

J'ai trouvé les règles pour les SQL Hints

[bidi]

Hehe merci, c'est intéressant ^^

Pour l'analyse, j'ai fait comme on m'a dit plus haut, avec

Code :

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

exec dbms_stats.gather_schema_stats(user, cascade=>true);

. Mais ça ne change rien . Le plan d'exécution à l'air très bien, ce qui foire, c'est au niveau du nombre extraordinaire de consistant gets qu'il effectue...

Je pars en vacance pour la semaine mais je relancerai le sujet en revenant car mon problème n'est pas complètement réglé...:-/ et je compte bien comprendre ce qu'il se passe avant d'aller plus loin.

Merci pour vos recherches

[bidi]

RE :-)

J'avais un peu laissé tout ça de côté mais je suis tombé sur l'article très intéressant de mnitu :-) en plus j'ai réussi à me débrouiller pour copier la db sur une serveur où j'ai accès à des outils plus appropriés...

J'ai donc repris un peu les recherches et je suis tombé au moment de l'article où les évaluations d'Oracle sur le nombre de lignes ramenées est fausse (ce qui est mon cas...) pour rappel voici l'explain plan que j'obtenais:


Maintenant, je test avec l'hint suivant: dynamic_sampling(fd 10)

et la mes temps de réponses sont divisés par 10 car Oracle prend le bon chemin!

voici le nouveau plan (Bon c'est moche mais c'est normal avec le 'A%' ^^

Code :

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

1
2
3
4
5
6
7
8
9
10
11
-----------------------------------------------------------------------------------------------
| Id  | Operation               |  Name                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                                |     1 |    50 |   868  (38)|
|   1 |  SORT AGGREGATE         |                                |     1 |    50 |            |
|*  2 |   HASH JOIN             |                                |    59 |  2950 |   868  (38)|
|*  3 |    INDEX RANGE SCAN     | FILE_DECLARATION_M3_CITY_POST  |  3419 | 64961 |     4  (25)|
|*  4 |    HASH JOIN            |                                |  9848 |   298K|   863  (38)|
|*  5 |     INDEX RANGE SCAN    | ACTOR_M1_DENOM_LNM             |  3397 | 71337 |    18   (6)|
|   6 |     INDEX FAST FULL SCAN| PART_ACTOR_M1_ACTOR_ID         |  1874K|    17M|   712  (27)|
-----------------------------------------------------------------------------------------------

Avec un critère plus précis ('UNIVERSIT%' par exemple) j'arrive à :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
--------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                          | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                                |     1 |    50 |    12  (17)|
|   1 |  SORT AGGREGATE      |                                |     1 |    50 |            |
|*  2 |   HASH JOIN          |                                |     1 |    50 |    12  (17)|
|   3 |    NESTED LOOPS      |                                |     7 |   217 |     8  (13)|
|*  4 |     INDEX RANGE SCAN | ACTOR_M1_DENOM_LNM             |     2 |    42 |     4  (25)|
|*  5 |     INDEX RANGE SCAN | PART_ACTOR_M1_ACTOR_ID         |     3 |    30 |     3  (34)|
|*  6 |    INDEX RANGE SCAN  | FILE_DECLARATION_M3_CITY_POST  |  3419 | 64961 |     4  (25)|
--------------------------------------------------------------------------------------------

Et ça, c'est déjà bien mieux :-)

Tout ça pour en venir à ma question:

Qu'est-ce qui pourrait être mal configuré? Vu que ça passe très bien avec le hint, j'imagine que c'est une histoire de config mais je ne suis vraiment pas expert et je ne vois pas trop ce que je pourrais changer comme paramètres pour améliorer les choses...



Merci :-)

[mnitu]

Qu’est-ce que ça donne une requête sans le hint de ce type

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
 
SELECT count(fd.file_id)
  FROM FILE_DECLARATION fd
 WHERE upper(fd.CITY_LNM) = 'GENT' 
   AND fd.POSTCODE = '9000' 
   AND fd.DECLARATION_NUM IS NOT NULL
   And fd.file_id In  
       (SELECT pa.file_id
          FROM PARTICIPATION_ACTOR pa  
               Inner Join
               ACTOR orderer 
            ON (orderer.id=pa.actor_id)
         WHERE pa.ACT_PART_CODE='orderer'  
           and orderer.DENOM_LNM LIKE 'UNIV%' 
       )

[bidi]

Bon

Donc finalement, ma requête est la suivante :

Code :

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

1
2
3
4
5
SELECT count(distinct(fd.file_id))
	FROM FILE_DECLARATION fd
	INNER JOIN PARTICIPATION_ACTOR pa ON pa.file_id=fd.file_id
	INNER JOIN ACTOR orderer ON orderer.id= pa.actor_id AND (upper(orderer.DENOM_LNM) LIKE 'UNIV%' )
	WHERE (upper(fd.CITY_LNM) = 'GENT') AND(fd.POSTCODE = '9000')

qui, sans le hint donne:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     1 |    50 |     8  (13)|
|   1 |  SORT AGGREGATE               |                                |     1 |    50 |            |
|   2 |   NESTED LOOPS                |                                |     1 |    50 |     8  (13)|
|   3 |    NESTED LOOPS               |                                |     1 |    29 |     6  (17)|
|*  4 |     INDEX RANGE SCAN          | FILE_DECLARATION_M3_CITY_POST  |     1 |    19 |     4  (25)|
|*  5 |     INDEX RANGE SCAN          | PART_ACTOR_M1_FILE_ID          |     3 |    30 |     3  (34)|
|*  6 |    TABLE ACCESS BY INDEX ROWID| ACTOR                          |     1 |    21 |     3  (34)|
|*  7 |     INDEX UNIQUE SCAN         | ACTOR_PK                       | 10732 |       |     2  (50)|
-----------------------------------------------------------------------------------------------------

==> +- 13 secondes





Avec le dynamic sampling à 10:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
--------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                          | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                                |     1 |    50 |   128   (2)|
|   1 |  SORT AGGREGATE      |                                |     1 |    50 |            |
|*  2 |   HASH JOIN          |                                |     1 |    50 |   128   (2)|
|   3 |    NESTED LOOPS      |                                |   175 |  5425 |   124   (1)|
|*  4 |     INDEX RANGE SCAN | ACTOR_M1_DENOM_LNM             |    60 |  1260 |     4  (25)|
|*  5 |     INDEX RANGE SCAN | PART_ACTOR_M1_ACTOR_ID         |     3 |    30 |     3  (34)|
|*  6 |    INDEX RANGE SCAN  | FILE_DECLARATION_M3_CITY_POST  |  3419 | 64961 |     4  (25)|
--------------------------------------------------------------------------------------------

==> +- 1.5 secondes



Et votre requête adaptée (je pense que vous vous êtes basé sur la mauvaise pour votre requête):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT count(fd.file_id)
  FROM FILE_DECLARATION fd
 WHERE upper(fd.CITY_LNM) = 'GENT' 
   AND fd.POSTCODE = '9000' 
   AND fd.DECLARATION_NUM IS NOT NULL
   AND fd.file_id IN  
       (
         SELECT pa.file_id FROM PARTICIPATION_ACTOR pa 
         INNER JOIN ACTOR orderer ON (orderer.id=pa.actor_id)
         WHERE upper(orderer.DENOM_LNM) LIKE 'UNIV%' 
       )

qui donne:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     1 |    46 |   129   (2)|
|   1 |  SORT AGGREGATE               |                                |     1 |    46 |            |
|*  2 |   HASH JOIN SEMI              |                                |     1 |    46 |   129   (2)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| FILE_DECLARATION               |     1 |    33 |     5  (20)|
|*  4 |     INDEX RANGE SCAN          | FILE_DECLARATION_M3_CITY_POST  |     1 |       |     4  (25)|
|   5 |    VIEW                       | VW_NSO_1                       |   175 |  2275 |            |
|   6 |     NESTED LOOPS              |                                |   175 |  5425 |   124   (1)|
|*  7 |      INDEX RANGE SCAN         | ACTOR_M1_DENOM_LNM             |    60 |  1260 |     4  (25)|
|*  8 |      INDEX RANGE SCAN         | PART_ACTOR_M1_ACTOR_ID         |     3 |    30 |     3  (34)|
-----------------------------------------------------------------------------------------------------

+- 2.5 secondes


C'est difficile à mesurer (le temps de réponse) car je ne sais pas trop comment bypasser la cache Oracle :-/

[mnitu]

J'ai l'impression que la condition

Code :

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

1
2
 
WHERE (upper(fd.CITY_LNM) = 'GENT') AND(fd.POSTCODE = '9000')

est très proche de mon exemple avec les services et leurs localisations, c'est-à-dire la violation de l'hypothèse d'indépendance des prédicats.
Pourriez-vous sur votre requête initiale faire un test avec seulement le code postal pour voir que est-ce que ça donne ?