Optimisation de jointure

Version imprimable

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

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:

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.

31/03/2009, 10h27
orafrance

quel est le plan d'exécution ?
31/03/2009, 10h49
Skandy

Citation:

Envoyé par orafrance

quel est le plan d'exécution ?

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

Citation:

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
31/03/2009, 11h05
orafrance

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

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:

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:

SELECT COUNT(*) FROM STAT

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

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:

SELECT COUNT(*) FROM STAT
1446968

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

Merci.

31/03/2009, 11h29
orafrance

oui dans cet ordre. Eventuellement, tu peux même ajouter tps pour éviter le TABLE ACCESS après (je l'espère :mouarf:) l'INDEX ACCESS ;)
31/03/2009, 11h39
Skandy

Citation:

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 :mouarf:) 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)
31/03/2009, 11h57
Skandy

Citation:

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.

Tests avec INDEX(service, classe, to_char(date_stat,'yyyy/mm'), tps)

Code:

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:

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

:bug:

31/03/2009, 12h15
orafrance

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

Alors pourquoi l'agrégat change le plan ? :koi:

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:

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

31/03/2009, 12h34
Golgotha

Citation:

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 ?

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

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

31/03/2009, 12h50
Waldar

Citation:

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 ?
31/03/2009, 13h21
Skandy
Citation:

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:

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.
31/03/2009, 13h24
Skandy

Objectif

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.

Ce serait pas plutôt :

Code:

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.

Citation:

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:

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

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:

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')

31/03/2009, 14h32
Waldar

Citation:

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.

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