Discussion :

[nuke_y]

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 :

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

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

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

[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é

[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

[Jaouad]

Est ce que l'on pourrait également avoir les index et l'explan plan complet pour voir leur sélectivité ?

[nuke_y]

Sélectivité :

Code :

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

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 :

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

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 :

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

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 :

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

Grosse_Table.debut_date >= :0

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

Code :

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

Grosse_Table.debut_date <= :1

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

Code :

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

Grosse_Table.fin_date >= :0

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

Code :

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

Grosse_Table.fin_date <= :1

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

Code :

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

Enorme_Table.action_date >= :0

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

Code :

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

Enorme_Table.action_date <= :1

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

Code :

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

Grosse_Table.annulée = 0

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

Code :

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

Grosse_Table.livrée = 'EC'

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

Code :

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

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 :

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

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 :

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

Minuscule_Table.Identifiant7 = 'C'

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

Pour les contraintes je regarde. Merci.

[nuke_y]

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

Code :

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

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

[Jaouad]

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

Code :

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

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

[McM]

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

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

[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 ?

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

[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 ?

[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 ?

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

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

[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

[Jaouad]

POur avancer sur le sujet :

Code :

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

1
2
set timing on 
set autot trace exp stat

puis tu lance la requête incriminé et tu nous colles le résultat

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

[McM]

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 ?

[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

[aline]

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