[9i][Optimisation] Optimiser une requete

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour à tous, j'ai une grosse requete qui prend du temps et j'aimerais la réduire.

J'ai d'abord identifié en analysant l'Explain Plan qu'il y avait un MERGE CARTESIAN en accès full à Minuscule_Table lors de la jointure entre Enorme_Table et Minuscule_Table. J'ai donc créé 2 index, 1 sur Minuscule_Table et 1 sur Enorme Table sur leurs champs utilisés pour la jointure entre ces 2 tables et ça a bien fait disparaître le MERGE CARTESIAN.

Malheureusement ça n'a pas vraiment amélioré les temps (je suis passé de 45mn à 35mn, pas terrible), donc ce n'est pas vraiment ça le problème. Il n'y a pas si longtemps, cette (presque) même requête tournait en 3mn, j'ai depuis ré-organisé la requête et je ne sais pas ce qui pose problème, mais je trouverais. J'ai bien l'intention de décortiquer cette requête en partant de la base et en testant jusqu'à ce que j'arrive à trouver ce qui la perturbe, mais si quelquechose vous frappe, merci de me prévenir (parce que moi c'est mon boss qui me frappe mais ceci est une autre histoire...).

La requête :
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 SELECT DISTINCT Grosse_Table.Identifiant1, Grosse_Table.Identifiant2, Grosse_Table.Identifiant3, Grosse_Table.Identifiant4, Grosse_Table.Identifiant5, Enorme_Table.Identifiant6, Minuscule_Table.Valeur1 FROM Enorme_Table Enorme_Table, Grosse_Table Grosse_Table, Minuscule_Table Minuscule_Table WHERE Grosse_Table.Identifiant1 = Enorme_Table.Identifiant1 AND Grosse_Table.Identifiant2 = Enorme_Table.Identifiant2 AND Enorme_Table.Identifiant1 = Minuscule_Table.Identifiant1 and Enorme_Table.Identifiant6 = Minuscule_Table.Identifiant6 AND Grosse_Table.Identifiant1 IN (130, 290) AND Grosse_Table.debut_date >= :0 AND Grosse_Table.debut_date <= :1 AND Grosse_Table.fin_date >= :0 AND Grosse_Table.fin_date <= :1 AND Enorme_Table.action_date >= :0 AND Enorme_Table.action_date <= :1 AND Grosse_Table.annulée = 0 AND Grosse_Table.livrée = 'EC' AND Grosse_Table.Identifiant5 IS NULL AND Grosse_Table.Identifiant4 IS NULL AND Minuscule_Table.Identifiant7 = 'C'
Et l'explain plan :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 14 SELECT STATEMENT 13 SORT [UNIQUE] 12 FILTER 11 Grosse_Table TABLE ACCESS [BY INDEX ROWID] 10 NESTED LOOPS 7 NESTED LOOPS 3 INLIST ITERATOR 2 Minuscule_Table TABLE ACCESS [BY INDEX ROWID] 1 I_Minuscule_Table INDEX [RANGE SCAN] 6 INLIST ITERATOR 5 Enorme_Table TABLE ACCESS [BY INDEX ROWID] 4 I_Enorme_Table INDEX [RANGE SCAN] 9 INLIST ITERATOR 8 I_Grosse_Table INDEX [RANGE SCAN]
merci thanks ++

22/02/2006, 15h57
nuke_y

Je me permet de rajouter des informations sur la requete.

Elle permet de trouver au milieu de données disons "volatiles" de Enorme_Table des informations supplémentaires à mettre à jour dans Grosse_Table. Minuscule_Table est juste une table satellite de dimension qui sert à obtenir des informations supplémentaires sur des valeurs de Enorme_Table.

Exemple Grosse_Table c'est des Colis (entreprise de transport) et Enorme_Table c'est des Evénements sur ces colis (code barre du colis flashé à la charge et décharge des camions et des entrepots). Minuscule Table contient la signification de l'Evénement (Identifiant7) et le fait que ce soit un événement de livraison (Valeur1).

Donc ce que je fais, c'est que j'essaye de savoir pour les Colis (de Grosse_Table) les Evénements (Enorme_Table) qui sont de type (via Minuscule_Table) 'C' (Identifiant 7='C') et je récupère le code de l'Evenement (Identifiant6) et si c'est une livraison (Valeur1).

Ensuite je mettrais à jour la ligne de Grosse_Table avec le code de l'Evenement Identifiant6 et le fait que ce soit une livraison ou pas (Valeur1) et c'est bon, j'ai fait évoluer les informations que j'ai sur mon colis (je sais quel est le dernier Evenement qu'il a eu et si il a été livré ou pas).

EDIT: corrigé
22/02/2006, 16h21
aline

Bonjour nuke_y,

- Peux-tu nous donner les contraintes sur tes tables (primary, foreign key).
Et nous donner des indications sur la selectivitée de tes tables
22/02/2006, 16h24
Jaouad

Est ce que l'on pourrait également avoir les index et l'explan plan complet pour voir leur sélectivité ?
22/02/2006, 16h38
nuke_y

Sélectivité :

Code:

Grosse_Table.Identifiant1 = Enorme_Table.Identifiant1 AND Grosse_Table.Identifiant2 = Enorme_Table.Identifiant2

Grosse_Table.Identifiant1 : seulement 3 valeurs possibles, répartition 2/3 1/6 et 1/6
Enorme_Table.Identifiant1 : seulement 3 valeurs possibles, répartition 2/3 1/6 et 1/6
Grosse_Table.Identifiant2 : des millions de valeurs possibles, valeur presque unique
Enorme_Table.Identifiant2 : des millions de valeurs possibles, entre 1 et 30 lignes avec la même valeur.

Code:

Enorme_Table.Identifiant1 = Minuscule_Table.Identifiant1 and Enorme_Table.Identifiant6 = Minuscule_Table.Identifiant6

Enorme_Table.Identifiant1 : seulement 3 valeurs possibles, répartition 2/3 1/6 et 1/6
Minuscule_Table.Identifiant1 : seulement 3 valeurs possibles, répartition 2/3 1/6 et 1/6
Enorme_Table.Identifiant6 : environ 250 valeurs possibles, répartition équivalente entre les valeurs et entre 1 ligne et 30 lignes par valeur
Minuscule_Table.Identifiant6 : 250 lignes, avec presque 1 valeur différente pour chaque

Code:

Grosse_Table.Identifiant1 IN (130, 290)

seulement 3 valeurs possibles, répartition 2/3 1/6 et 1/6. Là on tape sur 2/3 + 1/6 des lignes.

Code:

Grosse_Table.debut_date >= :0

environ 30000 lignes par date, soit 1/365e des données.

Code:

Grosse_Table.debut_date <= :1

environ 30000 lignes par date, soit 1/365e des données.

Code:

Grosse_Table.fin_date >= :0

environ 30000 lignes par date, soit 1/365e des données.

Code:

Grosse_Table.fin_date <= :1

environ 30000 lignes par date, soit 1/365e des données.

Code:

Enorme_Table.action_date >= :0

environ 30000 lignes par date, soit 1/365e des données.

Code:

Enorme_Table.action_date <= :1

environ 30000 lignes par date, soit 1/365e des données.

Code:

Grosse_Table.annulée = 0

seulement 2 valeurs (0 ou 1), 5 à 10% des données à 1, les autres à 0

Code:

Grosse_Table.livrée = 'EC'

seulement 2 valeurs (EC ou LIV), 5 à 10% des données à EC, les autres à LIV

Code:

Grosse_Table.Identifiant5 IS NULL

250 valeurs possibles, 90% des données à NULL (puisque le but c'est de les alimenter), valeurs équiprobables sinon.

Code:

Grosse_Table.Identifiant4 IS NULL

3 valeurs possibles, 90% des données à NULL (puisque le but c'est de les alimenter), répartition 70% / 20% / 10% sur les non null.

Code:

Minuscule_Table.Identifiant7 = 'C'

3 valeurs possibles, répartition 70% / 20% / 10%

Pour les contraintes je regarde. Merci.

jaouad, je ne comprend pas bien ce que tu veux. Est-ce que tu veux quelque chose comme ça ?

Code:

1
2
Etape n°	Description	Coût estimé	Nombre estimé de lignes renvoyées	Nombre estimé de kilo-octets renvoyés
  1   	Cette étape du plan extrait toutes les lignes de la table Minuscule_Table.	2	1	0,032

Citation:
Envoyé par nuke_y
jaouad, je ne comprend pas bien ce que tu veux. Est-ce que tu veux quelque chose comme ça ?
Code:

1 2 Etape n° Description Coût estimé Nombre estimé de lignes renvoyées Nombre estimé de kilo-octets renvoyés 1 Cette étape du plan extrait toutes les lignes de la table Minuscule_Table. 2 1 0,032
oui stroumph

Si test paramètres ne sont pas NULL, mieux vaut mettre un between.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DISTINCT 
		Colis.Identifiant1, Colis.Identifiant2,  Colis.Identifiant3, 
		Colis.Identifiant4,  Colis.Identifiant5, 
		Evenements.Identifiant6, 
		signification.Valeur1 
FROM 
    COLIS, 
    EVENEMENTS, 
    SIGNIFICATION 
WHERE 
	Colis.Identifiant1 		= Evenements.Identifiant1 
AND Colis.Identifiant2 		= Evenements.Identifiant2 
AND Evenements.Identifiant1 = signification.Identifiant1 
AND Evenements.Identifiant6 = signification.Identifiant6 
AND Colis.Identifiant1 		IN (130, 290) 
AND Colis.debut_date 		BETWEEN :0 AND :1
AND Colis.fin_date 			BETWEEN :0 AND :1
AND Evenements.action_date 	BETWEEN :0 AND :1
AND Colis.annulée 			= 0 
AND Colis.livrée 			= 'EC' 
AND Colis.Identifiant4 		IS NULL 
AND Colis.Identifiant5 		IS NULL 
AND signification.Identifiant7 = 'C'

Ensuite vu que le 'EC' est très restrictif sur ton ENORME table (COLIS), mets un index dessus.

22/02/2006, 17h02
nuke_y

ok McM mais vaut-il mieux mettre un index juste sur ce champ de la table Colis (Grosse d'ailleurs et pas Enorme, c'est les Evenements qui sont énormes) ou alors un index sur tous les champs de la table Colis sur lesquels je fais la requête ?
22/02/2006, 17h32
nuke_y

Bon alors je confirme.

1mn30 la requete sur Grosse_Table seule, 2mn sur Grosse_Table jointe avec Enorme_Table. Donc il semblerait que ce soit Minuscule_Table qui pose problème.

1mn30 la requete sur Colis seule, 2mn sur Colis jointe avec Evenements. Donc il semblerait que ce soit SignificationEvt qui pose problème.

Je continue mes investigations.
22/02/2006, 17h36
McM

Il vaut mieux n'avoir qu'un seul index qui serve. De toute façon Oracle n'en prendra qu'un.
En avoir plusieurs c'est suivant les différentes requetes qui vont être faites sur cette table.

Dans ton cas, un INDEX sur COLIS.LIVREE, le reste est moins restrictif.

Le but serait :
Accès COLIS par INDEX LIVREE
Puis Accès Evenements par PK Range (Identifiant1, Identifiant2)
Puis Accès à Signification par index (Identifiant1, Identifiant6)

La demande d'Aline aidera à trouver : Il faut les PK des tables et les Foreign Key.

PS : t'as mis 'C' et dans ton explication tu as mis 'D'. Erreur de frappe ?
22/02/2006, 17h42
nuke_y

Ouaih je me suis trompé, j'ai corrigé (les 3 valeurs c'est M, C et D).

Bon pour les index malheureusement les mêmes tables servent à 3000 requêtes différentes donc je ne peux pas en faire qu'un. Par contre je crois que je comprend ce que vous voulez dire pour le coup des Index.

Maintenant si je fais 10 index différents sur chacunes des tables, est-ce que je suis sur qu'Oracle va prendre le bon ? Eventuellement il faudrait rajouter des hints ?
22/02/2006, 18h01
McM

Si tu dois créer des index sur tes tables pour une requete, en créer plusieurs ne sert à rien.
Les autres index servent à d'autres requetes, c'est normal.

Les Hint, ouais, ça peut marcher.
Le RULE peut parfois faire des miracles (plus utilisable en 10g je crois)
Les autres hints (index, ordre d'accès etc...) c'est pas trop mon truc car notre base bouge sans arrêt.
23/02/2006, 06h48
gregory.broissard

Je me pose une question peut etre tout bete, mais quel est l'interet du distinct sur grosse_table.identifiant_1 ?

Generalement, les identifiants sont uniques ?

Le distinct impose un balayage de toutes les valeurs et un tri (d'où le SORT dans ton explain).
23/02/2006, 10h08
aline

Bonjour Nuke,

quelques remarques et pistes:

- Il serait vraiment bien d'avoir les contraintes sur tes tables. Tu peux les chercher dans user_constraints.

- ton explain plan est inutilsable pour nous.
en effet,je sait que l'on passe par l'index I_Enorme_Table. Pas très parlant.
Les remarques de Jaouad sont donc très pertinantes.

- Seuls les colonnes de date semblent discriminantes, donc pour l'utilisation de l'index classique, à priori(sachant qe l'on dispose d'une info incomplète ), je conseillerai de mettre un index sur grosse table sur les colonnes date début, date fin.
su enorme table identifiant2,identifiant1(dans cet ordre (je ne suis même pas sur que identifiant 1 soit nécessaire)).

ensuite des index bitmaps peuvent être utiles pour les colonnes non selectives, mais je ne m'avance pas. Ils sont trrès contraignant et il faudrait être sur qu'il ne gêne pas.

L'idéee, serait de selectionner grosse table par les dates, puis énorme table par grosse table(identifiant2,identifiant1).
La jointure entre enorme et minuscule n'est pas selective du tout.
donc pas d'index sur minuscule et peux être éventuelement des bitmap sur identifiant1,6 de enorme.
si pas de bitmpa, on rique d'avoir un hash_join entre petite et enorme(ce qui n'est pas forcément grave!).

en esperant avoir été plus ou moins claire :roll:
23/02/2006, 10h31
Jaouad
POur avancer sur le sujet :
Code:

1 2 set timing on set autot trace exp stat
puis tu lance la requête incriminé et tu nous colles le résultat
23/02/2006, 10h32
nuke_y

Ok merci à tous.

Comme je ne peux pas créer des indexs n'importe comment sur la base de production, je suis en train de créer une réplication significative sur une base de test et là je pourrais tester.

Sinon oui les identifiants DEVRAIENT être uniques mais malheureusement non (oui oui je sais c'est des sagouins). On utilise un Discriminant (un autre champ) pour atteindre l'unicité avec identifiant1+Identifiant2+Discriminant.

Les contraintes :
- Grosse Table (colis) : PK sur Identifiant1, Identifiant2 et Discriminant (qui est un champ supplémentaire qui permet d'avoir l'unicité qu'on a pas en utilisant seulement Identifiant1 + Identifiant2) et des FK sur d'autres tables de dimension constituées de Identifiant1 + 1 ou 2 autres Identifiants qui ne sont pas utilisés dans cette requête.

- Enorme Table (événement) : aucune contrainte, même pas de PK (c'est un peu une table bazard en fait...)

- Minuscule Table (Signification_Evt) : PK sur Identifiant6 (alors que l'unicité n'est définie qu'avec Identifiant1+Identifiant6).
23/02/2006, 10h48
McM

Citation:

Minuscule Table (Signification_Evt) : PK sur Identifiant6 (alors que l'unicité n'est définie qu'avec Identifiant1+Identifiant6).

hein ?
Mais une PK est unique !!
Comment tu peux avoir une PK sur 1 colonne et l'unicité sur 2 colonnes ?
23/02/2006, 11h04
nuke_y

Ok Jaouad, je fais ça et je vous donne le résultat.

>McM : Comment ? Ben parce que pour l'instant ça marche, mais bientôt ça va leur péter à la tronche. Je te raconte pas les délais pour faire modifier une PK sur une table, alors en attendant on croise les doigts. lol
23/02/2006, 11h04
aline

Citation:

Envoyé par nuke_y

Ok merci à tous.

Comme je ne peux pas créer des indexs n'importe comment sur la base de production, je suis en train de créer une réplication significative sur une base de test et là je pourrais tester.

Sinon oui les identifiants DEVRAIENT être uniques mais malheureusement non (oui oui je sais c'est des sagouins). On utilise un Discriminant (un autre champ) pour atteindre l'unicité avec identifiant1+Identifiant2+Discriminant.

Les contraintes :
- Grosse Table (colis) : PK sur Identifiant1, Identifiant2 et Discriminant (qui est un champ supplémentaire qui permet d'avoir l'unicité qu'on a pas en utilisant seulement Identifiant1 + Identifiant2) et des FK sur d'autres tables de dimension constituées de Identifiant1 + 1 ou 2 autres Identifiants qui ne sont pas utilisés dans cette requête.

- Enorme Table (événement) : aucune contrainte, même pas de PK (c'est un peu une table bazard en fait...)

- Minuscule Table (Signification_Evt) : PK sur Identifiant6 (alors que l'unicité n'est définie qu'avec Identifiant1+Identifiant6).

y-a t'il une fk qui ne serait pas indiquée entre grosse table et enorme table sur id1,id2,discriminant.

Dans ce cas, ce serait bien de le dire à Oracle pour qu'il en tienne compte dans son plan d'optimisation (cela sert aussi à cela les fk!) et peux être de rajouter dans ta requete enorme.discimiant=groose.disciminant

Voir 40 message(s) de cette discussion en une page