Optimisation de jointure

**Skandy** · 30/03/2009, 18h43

Bonjour,

pouvez-vous analyser cette requête et m'indiquer quelles optimisations peuvent être mises en place (quels champs à indexer, index composite..etc.). Je vous en remercie d'avance

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
 
SELECT to_char(C.date_stat,'yyyy/mm') AS DATE_STAT,
        to_char(L.date_stat,'yyyy/mm') AS DATE_STAT_M,
        C.classe, C.service,
        L.classe, L.service,
        avg(C.tps) AS TEMPS_MOY,
        avg(L.tps) AS TEMPS_MOY_M,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY C.tps DESC) AS TEMPS_MED,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY L.tps DESC) AS TEMPS_MED_M
 
FROM STAT C, STAT L
 
WHERE C.date_stat BETWEEN TRUNC( SYSDATE, 'MM' ) AND last_day(SYSDATE)
AND L.date_stat BETWEEN TRUNC( ADD_MONTHS(SYSDATE, -1), 'MM') AND last_day(ADD_MONTHS(SYSDATE, -1))
AND C.classe = L.classe
AND C.service = L.service
 
GROUP BY
	to_char(C.date_stat,'yyyy/mm'),to_char(L.date_stat,'yyyy/mm'),C.classe,C.service, L.classe, L.service

Un index normal non unique composite est déjà créé sur :

1 service
2 date_stat
3 un autre champ non utilisé dans la requête

MERCI.

**orafrance** · 31/03/2009, 10h27

quel est le plan d'exécution ?

**Skandy** · 31/03/2009, 10h49

Envoyé par orafrance

quel est le plan d'exécution ?

EDIT: J'avais oublié la médiane :

SELECT STATEMENT CHOOSE 3066 1859 230516
SORT(GROUP BY) 3066 1859 230516
FILTER
HASH JOIN 3029 1859 230516
TABLE ACCESS(FULL) STAT ANALYZED 1512 3568 221216
TABLE ACCESS(FULL) STAT ANALYZED 1512 3568 221216

Merci

**orafrance** · 31/03/2009, 11h05

Moi j'essayerai de créer un index sur (service, classe, date_stat)

**orafrance** · 31/03/2009, 11h07

Moi j'essayerai de créer un index sur (service, classe, date_stat)

Si tu enlèves les fonctions de regroupement, ça ramène combien de lignes ?

Que donne :

Code :

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

1
2
3
4
5
6
SELECT COUNT(*) 
FROM STAT C, STAT L
 WHERE C.date_stat BETWEEN TRUNC( SYSDATE, 'MM' ) AND last_day(SYSDATE)
AND L.date_stat BETWEEN TRUNC( ADD_MONTHS(SYSDATE, -1), 'MM') AND last_day(ADD_MONTHS(SYSDATE, -1))
AND C.classe = L.classe
AND C.service = L.service

Et

Code :

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

SELECT COUNT(*) FROM STAT

**Skandy** · 31/03/2009, 11h28

Envoyé par orafrance

Moi j'essayerai de créer un index sur (service, classe, date_stat)

Si tu enlèves les fonctions de regroupement, ça ramène combien de lignes ?

Que donne :

Code :

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

1
2
3
4
5
6
SELECT COUNT(*) 
FROM STAT C, STAT L
 WHERE C.date_stat BETWEEN TRUNC( SYSDATE, 'MM' ) AND last_day(SYSDATE)
AND L.date_stat BETWEEN TRUNC( ADD_MONTHS(SYSDATE, -1), 'MM') AND last_day(ADD_MONTHS(SYSDATE, -1))
AND C.classe = L.classe
AND C.service = L.service

ça mouline encore.. c'est très mais très long..

Et

Code :

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

SELECT COUNT(*) FROM STAT

1446968

Je vais essayer avec l'index sur (service, classe, date_stat) dans cet ordre?

Merci.

**orafrance** · 31/03/2009, 11h29

oui dans cet ordre. Eventuellement, tu peux même ajouter tps pour éviter le TABLE ACCESS après (je l'espère

) l'INDEX ACCESS

**Skandy** · 31/03/2009, 11h39

Envoyé par orafrance

oui dans cet ordre. Eventuellement, tu peux même ajouter tps pour éviter le TABLE ACCESS après (je l'espère

) l'INDEX ACCESS

OK merci beaucoup. Je vais tenter avec un index sur (service, classe, date_stat) et puis dans un second temps avec (service, classe, date_stat, tps)

**Skandy** · 31/03/2009, 11h57

Envoyé par orafrance

Moi j'essayerai de créer un index sur (service, classe, date_stat)

Comme j'utilise un to_char sur date_stat ça risque pas de zapper l'index? Ne devrais-je pas en créer un sur to_char(date_stat,'yyyy/mm') ?

Merci.

**Skandy** · 31/03/2009, 12h15

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT to_char(C.date_stat,'yyyy/mm') AS DATE_STAT,
        to_char(L.date_stat,'yyyy/mm') AS DATE_STAT_M,
        C.service,
        L.service,
        C.tps AS TEMPS_MOY,
        L.tps AS TEMPS_MOY_M
 
FROM STAT C, STAT L
 
WHERE to_char(C.date_stat,'yyyy/mm') = to_char(SYSDATE,'yyyy/mm')
AND to_char(L.date_stat,'yyyy/mm') = to_char(ADD_MONTHS(SYSDATE, -1),'yyyy/mm')
AND C.classe = L.classe
AND C.service = L.service

Je suis en INDEX ACCESS FULL :

SELECT STATEMENT CHOOSE 11373 34018300 4218269200
HASH JOIN 11373 34018300 4218269200
INDEX(FAST FULL SCAN) TUXEDO.STAT_BC_IDX2 ANALYZED 1492 482727 29929074
INDEX(FAST FULL SCAN) TUXEDO.STAT_BC_IDX2 ANALYZED 1492 482727 29929074

Dès que je remets mes agrégations je repasse en TABLE ACCESS FULL

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
SELECT to_char(C.date_stat,'yyyy/mm') AS DATE_STAT,
        to_char(L.date_stat,'yyyy/mm') AS DATE_STAT_M,
        C.classe, C.service,
        L.classe, L.service,
        avg(C.tps) AS TEMPS_MOY,
        avg(L.tps) AS TEMPS_MOY_M,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY C.tps DESC) AS TEMPS_MED,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY L.tps DESC) AS TEMPS_MED_M
 
FROM TSTAT C, STAT L
 
WHERE to_char(C.date_stat,'yyyy/mm') = to_char(SYSDATE,'yyyy/mm')
AND to_char(L.date_stat,'yyyy/mm') = to_char(ADD_MONTHS(SYSDATE, -1),'yyyy/mm')
AND C.classe = L.classe
AND C.service = L.service
 
GROUP BY
	to_char(C.date_stat,'yyyy/mm'),to_char(L.date_stat,'yyyy/mm'),C.classe,C.service, L.classe, L.service

SELECT STATEMENT CHOOSE 1337915 34018300 4218269200
SORT(GROUP BY) 1337915 34018300 4218269200
HASH JOIN 11413 34018300 4218269200
TABLE ACCESS(FULL) TUXEDO.STAT_BC ANALYZED 1512 482727 29929074
TABLE ACCESS(FULL) TUXEDO.STAT_BC ANALYZED 1512 482727 29929074

**orafrance** · 31/03/2009, 12h15

Non puisque la fonction to_char est dans le SELECT et pas la jointure

Edit : ha oui, tu as ajouté du to_char, c'était mieux avant

Alors pourquoi l'agrégat change le plan ?

**Waldar** · 31/03/2009, 12h28

Pourquoi scanner deux fois la table ?
Avec juste un index sur date_stat, que donnerait ceci (si les chiffres sont bons on fera la mise en page après coup) :

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
SELECT
    to_char(date_stat,'yyyy/mm') AS DATE_STAT,
    classe,
    service,
    avg(tps) AS TEMPS_MOY,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tps DESC) OVER (PARTITION BY to_char(date_stat,'yyyy/mm')) AS TEMPS_MED
FROM
    STAT 
WHERE
    date_stat >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND date_stat <  TRUNC(ADD_MONTHS(SYSDATE,  1), 'MM')
GROUP BY
    to_char(date_stat,'yyyy/mm'),
    classe,
    service

**Golgotha** · 31/03/2009, 12h34

Envoyé par Skandy

Comme j'utilise un to_char sur date_stat ça risque pas de zapper l'index? Ne devrais-je pas en créer un sur to_char(date_stat,'yyyy/mm') ?

Merci.

Je pense que oui.. ne peut tu pas ajouter le bon format directement dans ta table ou faire une vue matérialisée pour ne pas avoir de to_char

Que donne ta requête sans to_char ?

**Skandy** · 31/03/2009, 12h35

Bonjour,

parce qu'il me faut les temps moyens et médians pour le mois en cours ET le mois précédant et non les temps moyens et médians sur les deux mois.

Merci.

Envoyé par Waldar

Pourquoi scanner deux fois la table ?
Avec juste un index sur date_stat, que donnerait ceci (si les chiffres sont bons on fera la mise en page après coup) :

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
SELECT
    to_char(date_stat,'yyyy/mm') AS DATE_STAT,
    classe,
    service,
    avg(tps) AS TEMPS_MOY,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tps DESC) OVER (PARTITION BY to_char(date_stat,'yyyy/mm')) AS TEMPS_MED
FROM
    STAT 
WHERE
    date_stat >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND date_stat <  TRUNC(ADD_MONTHS(SYSDATE,  1), 'MM')
GROUP BY
    to_char(date_stat,'yyyy/mm'),
    classe,
    service

**Waldar** · 31/03/2009, 12h50

Envoyé par Skandy

Parce qu'il me faut les temps moyens et médians pour le mois en cours ET le mois précédant et non les temps moyens et médians sur les deux mois.

Exactement ce que fait ma requête, sauf erreur.
Si vous deviez faire une analyse par mois sur un an feriez-vous onze jointures ?

**Skandy** · 31/03/2009, 13h21

Envoyé par Waldar

Exactement ce que fait ma requête, sauf erreur.
Si vous deviez faire une analyse par mois sur un an feriez-vous onze jointures ?

Autant pour moi

J'ai crée un index sur date_stat, votre requête sans les agrégats :

SELECT STATEMENT CHOOSE 6 3620 170140
FILTER
TABLE ACCESS(BY INDEX ROWID) TUXEDO.STAT_BC ANALYZED 6 3620 170140
INDEX(RANGE SCAN) TUXEDO.STAT_BC_IDX3 2 6517

L'agrégat AVG passe bien, par contre :

Code :

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

1
2
 
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tps DESC) OVER (PARTITION BY to_char(date_stat,'yyyy/mm')) AS TEMPS_MED

J'ai un ORA00979: N'est pas une expression GROUP BY

Merci.

**Skandy** · 31/03/2009, 13h24

Peut être qu'en décrivant mon objectif, une autre méthode serait plus simple.

Le but de vouloir sortir les temps moyens et médians des derniers mois est de calculer le nombre de services dont les temps moyens et médians se sont dégradés entre les deux mois..

Voyez-vous une autre solution que ma requête de deux heures et 1/2.

Merci.

**Skandy** · 31/03/2009, 13h58

Ce serait pas plutôt :

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
SELECT
    to_char(date_stat,'yyyy/mm'),
    classe,
    service,
    avg(tps) AS TEMPS_MOY,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tps DESC) AS TEMPS_MED
FROM
    STAT
WHERE
    date_stat >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND date_stat <  TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM')
 
GROUP BY
    to_char(date_stat,'yyyy/mm'),
    classe,
    service

Merci.

Envoyé par Waldar

Pourquoi scanner deux fois la table ?
Avec juste un index sur date_stat, que donnerait ceci (si les chiffres sont bons on fera la mise en page après coup) :

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
SELECT
    to_char(date_stat,'yyyy/mm') AS DATE_STAT,
    classe,
    service,
    avg(tps) AS TEMPS_MOY,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tps DESC) OVER (PARTITION BY to_char(date_stat,'yyyy/mm')) AS TEMPS_MED
FROM
    STAT 
WHERE
    date_stat >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND date_stat <  TRUNC(ADD_MONTHS(SYSDATE,  1), 'MM')
GROUP BY
    to_char(date_stat,'yyyy/mm'),
    classe,
    service

**Waldar** · 31/03/2009, 14h30

Je pense que le group by dans le PERCENTILE_CONT n'est pas passé à cause de l'utilisation de tps, qui n'est effectivement pas dans la requête.

Soit dit en passant il existe une fonction MEDIAN, qui va probablement résoudre ce problème (http://download.oracle.com/docs/cd/B...6.htm#i1279886) :

Avec cette requête vous pourrez comparer vos moyennes / médians entre le mois courant et le mois précédent :

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
 
SELECT *
FROM
(
SELECT
    classe,
    service,
    to_char(date_stat,'yyyy/mm') AS MOIS_STAT,
    avg(tps) AS TEMPS_MOY,
    lag(avg(tps)) over(partition by classe, service order by to_char(date_stat,'yyyy/mm') asc) AS TEMPS_MOY_M1,
    median(tps) AS TEMPS_MED,
    lag(median(tps)) over(partition by classe, service order by to_char(date_stat,'yyyy/mm') asc) AS TEMPS_MED_M1
FROM
    STAT 
WHERE
    date_stat >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND date_stat <  TRUNC(ADD_MONTHS(SYSDATE,  1), 'MM')
GROUP BY
    classe,
    service,
    to_char(date_stat,'yyyy/mm')
)
WHERE
    MOIS_STAT = to_char(TRUNC(SYSDATE, 'MM'), 'yyyy/mm')

**Waldar** · 31/03/2009, 14h32

Envoyé par Skandy

Le but de vouloir sortir les temps moyens et médians des derniers mois est de calculer le nombre de services dont les temps moyens et médians se sont dégradés entre les deux mois..

Oui j'aurai du lire celà avant

Je modifie ma requête ci-dessus.

Optimisation de jointure

Administration Oracle

Discussions similaires

Partager

Partager