[9.2.0.6]Problème performances avec fonctions analytiques

Version imprimable

Bonjour,
j'ai un soucis de perf avec un Select que je n'arrive pas à solutionner :
Ce 1er script tourne bien (env 7 secondes) :

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
Select 
        Distinct
        D.DistrictName as "District"
        , D.District_i as "No District"
        , T.Township   as "Commune"
        , T.Township_i as "No Commune"
        , PC.PopulationCount as "Pop Totale Commune"
        , Count(P0.Requester_i) over (partition by T.Township_i) as "Nb RA Commune"
From 
        Township        T
        , District      D
        , TownshipPopulationCount   PC
        , (
            Select
                    Requester_i
                    , ActualLodge_i
                    , SocialCategoryLabel
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail   
          )             P0
Where
                T.District_i = D.District_i
        And     T.Township_i = PC.Township_i
        And     PC.Township_i = P0.Township_i (+)
Order by D.District_i, T.Township_i

Celui-ci également (env 2 secondes) :

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
Select 
        Distinct
        D.DistrictName as "District"
        , D.District_i as "No District"
        , T.Township   as "Commune"
        , T.Township_i as "No Commune"
        , PC.PopulationCount as "Pop Totale Commune"
        , Count(P1.Requester_i) over (partition by T.Township_i) as "Nb RA Commune"
From 
        Township        T
        , District      D
        , TownshipPopulationCount   PC
        , (
            Select
                    Requester_i
                    , ActualLodge_i
                    , SocialCategoryLabel
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail 
            Where   SocialCategoryLabel = 'AUTONOME'  
          )             P1
Where
                T.District_i = D.District_i
        And     T.Township_i = PC.Township_i
        And     PC.Township_i = P1.Township_i (+)
Order by D.District_i, T.Township_i

La différence entre les 2 est seulement l'ajout d'une clause where dans le inline-view.

maintenant, lorsque j'aimerais LES DEUX résultats dans deux colonne séparées, je regroupe les deux scripts comme ceci :

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
30
31
32
33
34
35
36
37
38
Select 
        Distinct
        D.DistrictName as "District"
        , D.District_i as "No District"
        , T.Township   as "Commune"
        , T.Township_i as "No Commune"
        , PC.PopulationCount as "Pop Totale Commune"
        , Count(P0.Requester_i) over (partition by T.Township_i) as "Nb RA Commune"
        , Count(P1.Requester_i) over (partition by T.Township_i) as "Nb RA Autonomes"
From 
        Township        T
        , District      D
        , TownshipPopulationCount   PC
        , (
            Select
                    Requester_i
                    , ActualLodge_i
                    , SocialCategoryLabel
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail   
          )             P0
        , (
            Select
                    Requester_i
                    , ActualLodge_i
                    , SocialCategoryLabel
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail  
            Where   SocialCategoryLabel = 'AUTONOME' 
          )             P1
Where
                T.District_i = D.District_i
        And     T.Township_i = PC.Township_i
        And     PC.Township_i = P0.Township_i (+)
        And     PC.Township_i = P1.Township_i (+)
Order by D.District_i, T.Township_i

et là le Select tourne plus de 20 minutes sans me donner de résultat (je flingue le process au bout de 20 minutes)...
Est-ce que quelqu'un vois un soucis qui m'aurais échappé ?

Pour info, la base tourne sous Oracle 9.2.0.6 sous Solaris, client 9.2.0.7 sous XP Pro, script exécuté avec TOAD, même soucis sous SQLPlus...

merci d'avance pour toutes vos suggestions !

21/11/2005, 09h16
aline

Bonjour Yorglaa,

Pourrais-tu nous donner les plan d'éxecution de tes requêtes?

Bien sûr !
Requête 1 (celle de 7 secondes)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE		6 K	 	265  	 	      	             	 
  SORT UNIQUE		6 K	470 K	186  	 	      	             	 
    WINDOW SORT		6 K	470 K	265  	 	      	             	 
      HASH JOIN OUTER		6 K	470 K	29  	 	      	             	 
        HASH JOIN		382  	18 K	9  	 	      	             	 
          TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIPPOPULATIONCOUNT	382  	2 K	2  	 	      	             	 
          HASH JOIN		382  	15 K	6  	 	      	             	 
            TABLE ACCESS FULL	ASYLUM_3_0.DISTRICT	19  	456  	2  	 	      	             	 
            TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIP	382  	6 K	3  	 	      	             	 
        TABLE ACCESS FULL	ASYLUM_3_0.POPULATIONDETAIL	6 K	154 K	19

la 2èem (celle de 2 secondes)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE		2 K	 	115  	 	      	             	 
  SORT UNIQUE		2 K	177 K	86  	 	      	             	 
    WINDOW SORT		2 K	177 K	115  	 	      	             	 
      HASH JOIN OUTER		2 K	177 K	29  	 	      	             	 
        HASH JOIN		382  	18 K	9  	 	      	             	 
          TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIPPOPULATIONCOUNT	382  	2 K	2  	 	      	             	 
          HASH JOIN		382  	15 K	6  	 	      	             	 
            TABLE ACCESS FULL	ASYLUM_3_0.DISTRICT	19  	456  	2  	 	      	             	 
            TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIP	382  	6 K	3  	 	      	             	 
        TABLE ACCESS FULL	ASYLUM_3_0.POPULATIONDETAIL	2 K	80 K	19

et enfin la requête qui pose problème

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE		34 K	 	1888  	 	      	             	 
  SORT UNIQUE		34 K	3 M	1275  	 	      	             	 
    WINDOW SORT		34 K	3 M	1888  	 	      	             	 
      HASH JOIN		34 K	3 M	50  	 	      	             	 
        TABLE ACCESS FULL	ASYLUM_3_0.DISTRICT	19  	456  	2  	 	      	             	 
        HASH JOIN OUTER		34 K	2 M	47  	 	      	             	 
          HASH JOIN OUTER		2 K	130 K	26  	 	      	             	 
            HASH JOIN		382  	9 K	6  	 	      	             	 
              TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIPPOPULATIONCOUNT	382  	2 K	2  	 	      	             	 
              TABLE ACCESS FULL	ASYLUM_3_0.TOWNSHIP	382  	6 K	3  	 	      	             	 
            TABLE ACCESS FULL	ASYLUM_3_0.POPULATIONDETAIL	2 K	80 K	19  	 	      	             	 
          TABLE ACCESS FULL	ASYLUM_3_0.POPULATIONDETAIL	6 K	154 K	19

Pour info complémentaire, la table la plus grosse (PopulationDetail) comporte 6600 lignes... et la table la plus "déterminante" en terme de restriction est l'ensemble des lignes de TownshipPopulationCount qui restreint (par la jointure) le set final à 382 lignes...
ce n'est donc vraiment pas une requête qui porte sur de grosses volumétries...

au fait, petite question,

pourquoi fais-tu dans ta fonction analytique
Code:

1 2 Count(P0.Requester_i) over (partition by T.Township_i) as "Nb RA Commune"
au lieu de
Code:

1 2 Count(P0.Requester_i) over (partition by P0.Township_i) as "Nb RA Commune"
Y a t'il une différence?

21/11/2005, 10h53
Yorglaa

non, aucune différence, j'ai essayé les deux avec le même résultat et des prformances idem...
j'ai laissé dans le script la dernière version testée...

je sèche....

tu peux faire un sqltrace/tkprof pour que l'on en sache un peux plus?

2 pistes, mais je n'y crois pas trop:

- que donne le temps d'execution de ta requete sans les jointure externe(jointures simple)?

- j'ai déjà vu des choses bizarres avec les inlines view, que donne ce type de requête?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Select
        Distinct
        D.DistrictName as "District"
        , D.District_i as "No District"
        , T.Township   as "Commune"
        , T.Township_i as "No Commune"
        , PC.PopulationCount as "Pop Totale Commune"
        , Count(P0.Requester_i) over (partition by Utility.FGetTownshipIDFromLodge(ActualLodge_i)) as "Nb RA Commune"
From
        Township        T
        , District      D
        , TownshipPopulationCount   PC
        , PopulationDetail          P0
Where
                T.District_i = D.District_i
        And     T.Township_i = PC.Township_i
        And     PC.Township_i = P0.Township_i (+)
Order by D.District_i, T.Township_i

21/11/2005, 13h57
Yorglaa

Salut,
tout d'abord, merci de prendre le temps de te pencher sur mon problème...

alors, pour ta suggestion de script, je ne peux hélas pas, parce que la colonne P0.Township_i (celle avec la jointure externe) n'existe pas en tant que telle dans la table, c'est la valeur ramenée par la fonction Utility.FGetTownshipIDFromLodge... et je n'arrive pas à faire une jointure externe sur le résultat d'une fonction...

Pour ce qui est de tester les script sans les (+), je vais essayer... ça ne donnera pas les résultats dont j'ai besoin, mais au moins ça nous donnera une indication.

Sinon j'ai également ouvert un Tar avec ce problème, et si j'ai des infos de la part de Oracle, je ne manquerais pas de les mettre ici.

Bon, ben j'ai contourné le problème en ne passant pas par les fonctions analytiques...

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
30
31
32
33
34
35
Select
        D.DistrictName as "District"
        , D.District_i as "No District"
        , T.Township   as "Commune"
        , T.Township_i as "No Commune"
        , PC.PopulationCount as "Pop Totale Commune"
        , nvl(P0.ReqCount,0) as "Nb RA Commune"
        , nvl(P1.ReqCount,0) as "Nb RA Autonomes"
From
        Township        T
        , District      D
        , TownshipPopulationCount   PC
        , (
            Select
                    Count(Requester_i) as ReqCount
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail
            Group by Utility.FGetTownshipIDFromLodge(ActualLodge_i)
          )             P0
        , (
            Select
                    Count(Requester_i) as ReqCount
                    , Utility.FGetTownshipIDFromLodge(ActualLodge_i) as Township_i
            From
                    PopulationDetail
            Where   SocialCategoryLabel = 'AUTONOME'
            Group by Utility.FGetTownshipIDFromLodge(ActualLodge_i)
          )             P1
Where
                T.District_i = D.District_i
        And     T.Township_i = PC.Township_i
        And     T.Township_i = P0.Township_i (+)
        And     T.Township_i = P1.Township_i (+)
Order by D.District_i, T.Township_i

ça marche... mais c'est moins fun !