Discussion :

[pat29]

Bonjour,

j'ai la problématique suivante :

en schématisant il y a 3 grosses tables T1, T2, T3 (plusieurs millions de lignes) qui sont liées en cascade sur 2 axes de jointures
- 1 hiérarchique avec les ID
- 1 géographique sur la zone, depuis une table ZONE
Les 3 tables sont partitionnées par zone.
De plus les objets présents à chaque niveau ont une durée de validité comprise entre 2 dates de début et de fin.

La table TEMPS qui contient un calendrier permet de générer les jours de l'intervalle

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
ZONE
	IDZONE (PK)
	LIBZONE
 
T1
	IDZONE
	ID1 (PK)
	IDZONE
	DATE_DEBUT1
	DATE_FIN1
 
T2
	IDZONE
	ID1 (FK)
	ID2 (PK)
	DATE_DEBUT2
	DATE_FIN2
 
T3
	IDZONE
	ID2 (FK)
	ID3 (PK)
	DATE_DEBUT3
	DATE_FIN3
 
TEMPS
	JOUR (PK)

La requête de base est de retrouver tous les Objets de T1 d'une zone donnée, avec tous les objets associés par l'ID dans T2 puis T3, pour un jour donné, donc comprise dans l'intervalle [DATE_DEBUTx, DATE_FINx]
Ces données doivent être accédées par BO, alors pour éviter des boucles à cause des dates j'ai fait une vue regroupant les clés :

Code :

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

1
2
3
4
5
6
7
8
9
10
create view V as 
	select t.JOUR, z.IDZONE, T1.ID1, T2.ID2, T3.ID3
	from ZONE z
	join T1 on T1.IDZONE = z.IDZONE
	join T2 on T2.IDZONE = T1.IDZONE and T2.ID1=T1.ID1
	join T3 on T3.IDZONE = T2.IDZONE and T3.ID2=T2.ID2
	join TEMPS t 
		on  t.JOUR between DATE_DEBUT1 and DATE_FIN1
		and t.JOUR between DATE_DEBUT2 and DATE_FIN2
		and t.JOUR between DATE_DEBUT3 and DATE_FIN3

cette vue étant jointe dans l'univers aux 5 tables par les PK.

si l'on applique le critère de zone sur la vue :

Code :

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

1
2
select * from V
where LIBZONE="xxx"

le plan d'exécution idéal donne un HASH JOIN entre ZONE, T1, T2 et T3 sur la seule partition de la zone concernée pour T1,T2,T3 avec un filtre sur ZONE et des JOIN PART FILTER sur les ID entre T1,T2 et T3.
Et pour finir un MERGE JOIN avec TEMPS avec les filtres BETWEEN sur les dates.

Le problème est que si l'on ajoute le critère sur le jour

Code :

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

1
2
3
select * from V
where LIBZONE="xxx"
and JOUR=TO_DATE('01/12/2015','DD/MM/YYYY')

l'optimiseur change complètement le plan et entre par TEMPS puis fait des NESTED LOOP sur les tables T1,T2 et T3, et plus d'exclusion de partition !
du coup les perfs en prennent un coup...
Et ces 2 critères sont obligatoires pour tous les rapports.

J'ai recréé la vue de cette façon pour forcer les HASH JOIN

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
create view V as 
with V1 as (
	select /*+ USE_HASH(T1) USE_HASH(T2) USE_HASH(T3) */ 
		T1.IDZONE
		,T1.ID1
		,T1.DATE_DEBUT1
		,T1.DATE_FIN1
		,T2.ID2
		,T2.DATE_DEBUT1
		,T2.DATE_FIN1
		,T3.ID3
		,T3.DATE_DEBUT1
		,T3.DATE_FIN1
	from T1
	join T2 on T2.IDZONE = T1.IDZONE and T2.ID1=T1.ID1
	join T3 on T3.IDZONE = T2.IDZONE and T3.ID2=T2.ID2
	)
select	
	t.JOUR, z.IDZONE, T1.ID1, T2.ID2, T3.ID3
	from ZONE z
		join V1 on V1.IDZONE = z.IDZONE
		join TEMPS t 
			on  t.JOUR between DATE_DEBUT1 and DATE_FIN1
			and t.JOUR between DATE_DEBUT2 and DATE_FIN2
			and t.JOUR between DATE_DEBUT3 and DATE_FIN3

mais ça ne change rien, toujours les NESTED LOOP ...

Note : il n'y pas d'index (ce qui empire les choses)

Quelqu'un saurait il si l'on peut empêcher l'optimiseur de rentrer par les dates mais de prendre celles-ci simplement comme filtre ?
Merci !

[mnitu]

Commencez quand même par vérifier/recalculer vos statistiques sur les tables en question!

Si cela ne marche pas une solution facile pourrait être la suivante :
Prenez votre requête sans le jour et via dbms_xplan.display et l'option qui affiche le "stored outiline" récupérez toutes les hints SQL. Ensuite copiez ces hints dans la requête qui contient le critère sur le jour et vérifiez que votre plan ne change plus.

So vous avez plus de temps vous devez essayer de comprendre pourquoi l'optimiseur part dans les choux.

[pat29]

Bonjour mnitu,

les stats sont bien à jour.
Pour ce qui est des hints je ne peux hélas qu'en mettre dans la vue puisque c'est BO qui forme la requête...
Et je ne comprends toujours pas pourquoi l'optimiseur choisit le mauvais plan...

Cdlt.

[mnitu]

Bonjour,

Oubliez BO pour l'instant. Prenez la requête et sortez tous les hints comme je vous l'ai dit, etc.
Si tout se passe bien vous allez arriver a injecter les hints dans la vue plus tard.

[Mohamed.Houri]

Bonjour,

Le problème est que si l'on ajoute le critère sur le jour

Code :

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

1
2
3
select * from V
where LIBZONE="xxx"
and JOUR=TO_DATE('01/12/2015','DD/MM/YYYY')

l'optimiseur change complètement le plan et entre par TEMPS puis fait des NESTED LOOP sur les tables T1,T2 et T3, et plus d'exclusion de partition !
du coup les perfs en prennent un coup...
Et ces 2 critères sont obligatoires pour tous les rapports.
Merci !

Pourquoi vous n'avez pas posté le plan d'exécution? Selon ce que vous avez donné comme information, il semblerait qu'Oracle ait opté pour une VIEW PUSH PREDICATE transformation couplée avec une NESTED LOOP lorsque vous avez ajouté le critère sur le jour. Si c'est le cas il y a forcément des statistiques non adéquatement calculées sur les colonnes qui font penser au CBO que l'accès via une NESTED LOOP est plus approprié.

Bien Cordialement
Mohamed Houri