[9i] Optimisation de requête

**david71** · 04/07/2007, 18h24

Salut,

Voilà je m'aperçois que j'ai une requête qui prend pas mal de temps CPU, donc je cherche à l'améliorer. N'étant pas expert, quelqu'un peut-il me faire des suggestions ?

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
32
SELECT   HE.C_SITE,
         HE.C_ESPACE,
         HE.C_EMETTEUR,
         '' AS L_DENOM_SOCIALE,
         GM.C_GRP_MODULE,
         GM.L_GRP_MODULE,
         0 AS ACCES_AV,
         0 AS ACCES_AP,
         (CASE 
            WHEN TRIM(AUTH.ID_CONFIG) = 'CACEIS' THEN '01 - NOM1'
            WHEN TRIM(AUTH.ID_CONFIG) = 'NOM2' THEN '02 - NOM3'
            ELSE 'non renseigné'
          END) AS C_CONF
FROM     IODA.HABILITATION_EMET HE,
         IODA.GRP_MODULE_MODULE GMM,
         IODA.GROUPE_MODULE GM,
         IODA.ESPACE_GRP_MODULE EGM,
         IODA.ESPACE ESP,
         IODA.AUTHENTIFICATION AUTH
WHERE    ESP.C_SITE = 'OLIS'
         AND ESP.C_ESPACE = 'EMETTEUR'
         AND HE.C_SITE = ESP.C_SITE
         AND HE.C_ESPACE = ESP.C_ESPACE
         AND ESP.C_SITE = EGM.C_SITE
         AND ESP.C_ESPACE = EGM.C_ESPACE
         AND EGM.C_GRP_MODULE = GM.C_GRP_MODULE
         AND GM.C_GRP_MODULE = GMM.C_GRP_MODULE
         AND GMM.C_MODULE = HE.C_MODULE
         AND HE.C_EMETTEUR = AUTH.C_EMETTEUR
GROUP BY HE.C_SITE,HE.C_ESPACE,HE.C_EMETTEUR,GM.C_GRP_MODULE,
         GM.L_GRP_MODULE,AUTH.ID_CONFIG
ORDER BY HE.C_EMETTEUR

Merci pour tout complément d'info

.

**Yanika_bzh** · 05/07/2007, 02h57

Comment peut on vous aider a optimiser une requete sans les infos minimums !?!?

Volumetrie des tables, liste des index, explain plan, type de SGBD, ...

**david71** · 05/07/2007, 10h36

J'utilise un SGBD de type ORACLE 9.1 et l'analyseur de requêtes que j'utilise est TOAD. L'explain plan que me donne TOAD est le suivant:

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
 
Operation	Object Name	Rows	Bytes	Cost	Object Node
 
SELECT STATEMENT Optimizer Mode=CHOOSE		7 K	 	11106  	 	      	             	 
  SORT ORDER BY		7 K	1 M	11106  	 	      	             	 
    SORT GROUP BY		7 K	1 M	11106  	 	      	             	 
      HASH JOIN		418 K	67 M	225  	 	      	             	 
        HASH JOIN		123  	18 K	9  	 	      	             	 
          NESTED LOOPS		19  	2 K	5  	 	      	             	 
            HASH JOIN		59  	4 K	5  	 	      	             	 
              NESTED LOOPS		59  	2 K	2  	 	      	             	 
                INDEX UNIQUE SCAN	IODA.PK_ESPACE	1  	14  	 	 	      	             	 
                INDEX FULL SCAN	IODA.PK_GRP_MODE_MOD	59  	2 K	1  	 	      	             	 
              TABLE ACCESS FULL	IODA.GROUPE_MODULE	32  	1 K	2  	 	      	             	 
            INDEX UNIQUE SCAN	IODA.PK_ESP_GRP_MOD	1  	31  	 	 	      	             	 
          INDEX FAST FULL SCAN	IODA.PK_HABILITATION_EMET	343  	13 K	3  	 	      	             	 
        TABLE ACCESS FULL	IODA.AUTHENTIFICATION	128 K	1 M	215

**gwadakillah** · 05/07/2007, 11h12

dans les dernières versions de TOAD , tu as un outil qui te permet d'optimiser les requetes... (Quest SQL Optimizer)
C'est vachement pratique car il te propose différents plans d'éxécutions, te propose de mettre des index la ou il en a besoin pour optimiser les requetes que tu veux optimiser

**orafrance** · 05/07/2007, 11h13

le mélange hash-join et nested loop est rarement performant (pour ne pas dire jamais

)

Il faut se débarasser des FULL TABLE SCAN, éventuellement, en créant des indexes.

**david71** · 05/07/2007, 12h41

Merci pour l'info mais je ne vois pas ce fameux utilitaire Quest SQL Optimizer, j'ai la version TOAD V8.

**Garuda** · 05/07/2007, 19h16

Option payante !

**McM** · 06/07/2007, 00h29

Pour optimiser une requete pareil, faut se poser des questions sur le GROUP BY : Pourquoi un group by sans summary colonne ? Si c'est pour éviter les doublons, ça cache généralement quelque chose.

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
32
33
34
35
36
37
38
SELECT   he.c_site, he.c_espace,
         he.c_emetteur,
         '' AS l_denom_sociale,
         gm.c_grp_module, gm.l_grp_module,
         0 AS acces_av, 0 AS acces_ap,
         (CASE 
            WHEN TRIM(auth.id_config) = 'CACEIS' THEN '01 - NOM1'
            WHEN TRIM(auth.id_config) = 'NOM2' THEN '02 - NOM3'
            ELSE 'non renseigné'
          END) AS c_conf
FROM        
         ESPACE             esp,
         HABILITATION_EMET  he,
         ESPACE_GRP_MODULE  egm,
         GROUPE_MODULE      gm,
         GRP_MODULE_MODULE  gmm,
         AUTHENTIFICATION   auth
 
WHERE esp.c_site        = 'OLIS'
AND esp.c_espace        = 'EMETTEUR'
 
AND he.c_site           = esp.c_site
AND he.c_espace         = esp.c_espace
 
AND egm.c_site          = esp.c_site
AND egm.c_espace        = esp.c_espace
 
AND gm.c_grp_module     = egm.c_grp_module
 
AND gmm.c_grp_module    = gm.c_grp_module
AND gmm.c_module        = he.c_module
 
AND auth.c_emetteur     = he.c_emetteur
 
GROUP BY 
            he.c_site, he.c_espace, he.c_emetteur, 
            gm.c_grp_module,
            gm.l_grp_module,auth.id_config

La table ESPACE est-elle nécessaire ? Si he.c_site, he.c_espace y font référence, pas la peine de la mettre.
On peut aussi remplacer les jointures suivantes par les valeurs en dur pour voir ce que ça donne.

Code :

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

1
2
3
4
AND he.c_site           = esp.c_site
AND he.c_espace         = esp.c_espace
AND egm.c_site          = esp.c_site
AND egm.c_espace        = esp.c_espace

**david71** · 09/07/2007, 16h58

Bonjour,

J'ai retiré la référence à la table ESPACE, mais ça ne change quasiment rien !

Si vous avez d'autres suggestions ?

Merci d'avance.

**McM** · 10/07/2007, 17h40

Crées des indexes

**Bruno2r** · 11/07/2007, 14h22

Bonjour,
J'ai l'habitude d'intégrer dans le From les clauses restrictives lorsqu'elles ont un fort impact sur la volumétrie
et de placer ces références en substitution de tables en fin de clause FROM pour les présenter en premier (du bas vers le haut)
Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
FROM          
AUTHENTIFICATION   auth,
GROUPE_MODULE      gm,
GRP_MODULE_MODULE  gmm,
(SELECT c_grp_module
		FROM ESPACE_GRP_MODULE  egm
		WHERE c_site = 'OLIS' AND c_espace = 'EMETTEUR') egm,
(SELECT c_site, c_espace, c_emetteur 
		FROM HABILITATION_EMET  
		WHERE c_site = 'OLIS' AND esp.c_espace = 'EMETTEUR') he

à essayer ...
d'autre part

Code :

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

1
2
3
4
5
(CASE 
            WHEN TRIM(auth.id_config) = 'CACEIS' THEN '01 - NOM1'
            WHEN TRIM(auth.id_config) = 'NOM2' THEN '02 - NOM3'
            ELSE 'non renseigné'
          END) AS c_conf

est il plus efficace ? que

Code :

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

decode(auth.id_config),'CACEIS', '01 - NOM1', 'NOM2', '02 - NOM3', 'non renseigné') AS c_conf

**orafrance** · 11/07/2007, 14h55

DECODE et CASE sont aussi performants

Merci de penser aux balises CODE la prochaine fois

**david71** · 23/07/2007, 14h44

Salut,

J'essaie de suivre les conseils de BRUNO2R sans succès pour le moment.

Ce que j'essaye déjà de remplacer c'est :

Code :

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

1
2
3
4
5
6
 
(case 
when TRIM(AUTH.ID_CONFIG)='NOM1' then '01 - NOM1' 
when TRIM(AUTH.ID_CONFIG)='NOM2' 
then '02 - NOM2'
else 'non renseigné' end) AS C_CONF

par :

Code :

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

1
2
 
decode((AUTH.id_config),'NOM1', '01 - NOM1', 'NOM2', '02 - NOM2', 'non renseigné') AS C_CONF

Sans succès puisque le traitement avec "decode" prend 1 seconde de plus.

Par contre, je ne comprends pas bien la première optimisation ? Quelq'un pourrait m'expliquer ou me donner l'optimisation complète par rapport à la requête initiale ?

Merci d'avance.

**McM** · 23/07/2007, 15h57

Tu pourra faire ce que tu veux au niveau des colonnes ramenées, sans des index sur les critères, ce sera dur d'optimiser quoi que ce soit.

Tu n'as pas répondu non plus sur le GROUP BY.

**david71** · 23/07/2007, 16h25

OK, comme je le disais j'utilise l'environnement TOAD pour aller sous ORACLE.
Donc comment je vois si les colonnes sont indexées ?
Je ne peux quand même pas mettre des indexs partout ? De plus, est-ce que cela ne va pas pénaliser d'autres applications ?

Concernant le GROUP BY, j'avoue que je ne sais pas trop à quoi il sert.
Si je le retire tout simplement, la durée d'exécution de la requête double !

Merci pour toute info complémentaire.

**Bruno2r** · 23/07/2007, 16h58

salut, je viens d'essayer d'appliquer à ton sql d'origine les propositions que j'avais faites de ramener les restrictions dans les deux tables he et egm suite à l'observation tout à fait pertinente de McM concernant l'inutilité du passage par la table espace puiqu'aucun champ n'apparait dans le select.

Sauf erreur de ma part (et j'espère pour toi m'être trompé) ton sql boucle car si je suis tes jointures
on passe de esp à he (par c_site et c_espace)

Code :

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

1
2
AND he.c_site           = esp.c_site
AND he.c_espace         = esp.c_espace

on passe de esp à egm (par c_site et c_espace)

Code :

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

1
2
AND egm.c_site          = esp.c_site
AND egm.c_espace        = esp.c_espace

puis de egm à gm (par grp_module)

Code :

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

AND gm.c_grp_module     = egm.c_grp_module

de gm à gmm (par grp_module)

Code :

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

AND gmm.c_grp_module    = gm.c_grp_module

et on revient ! de gmm à he par (c_module)

Code :

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

AND gmm.c_module        = he.c_module

Je ne comprends même pas que le sql s'execute

La dificulté pour t'aider c'est que nous n'avons pas la description des tables. Peux-tu vérifier ce que je crois avoir décelé

**McM** · 23/07/2007, 17h25

Pour les indexes, tu te mets sur le nom d'une table, tu appuies sur F4.
Tu sélectionnes ensuite l'onglet index et tu vois ceux qui existent.

**david71** · 24/07/2007, 09h46

Salut BRUNO2R,

Est-ce que je peux voir ta requête SQL avec tes conseils afin que je puisse déjà la tester ?

Merci d'avance.

**McM** · 24/07/2007, 10h34

Le sql ne va pas boucler !
Le fait que gmm soit lié à he par c_module, ça dépend du modèle de donnée.

Si la requete est fausse, sans connaitre les tables et les données, c'est impossible à voir.

**david71** · 24/07/2007, 11h45

Pour le moment pas moyen d'ajouter des indexs sur la table.
C'est la seule solution pour optimiser ?

Je suis d'accord avec vous concernant la table ESPACE, mais ça ne change rien en performance comme je l'avais dit dans un post précédent.

La requête marche bien c'est certain, j'ai repris le programme tel quel mais le problème c'est qu'elle prend trop de temps !

Merci pour toute autre suggestion.

[9i] Optimisation de requête

SQL Oracle

Discussions similaires

Partager

Partager