[TUNING] : Access full sur calculs d'agrégats

Version imprimable

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

19/10/2005, 17h08
PpPool

Citation:

Envoyé par Fred_D

j'ai pas regardé en profondeur mais il semble que personne n'a conseillé les fonctions analytiques alors que ça semble pouvoir régler quelque soucis :)

salut Fred,

comme je l'ai précisé, j'ai passé 1 mois sur le sujet, et j'ai jamais réussi à m'en sortir comme je voulai avec les fonctions analytiques

Aujourd'hui la requete présentée fonctionne très bien dans la plupart des cas, sauf quand je suis dans le cas d'une création de table dynamique, comme décrit ci-dessus.

@
19/10/2005, 17h10
Jaouad

Citation:

Envoyé par PpPool

Citation:

Envoyé par Jaouad

Tu devrais essayer avec un index sur Flaco_ID

j'ai bien un index sur FLACO_ID :cry:

je voulais dire POINT_ID

et effectivement il faudrait qu'il regarde les fonctions analytiques , lui même le sait , et d'ailleurs nous lui avons dit de ré écrire sa requête :wink:
19/10/2005, 17h14
PpPool
Description des statistiques de la table TL_TEST :

* Blocs vides : 1
* Espace moyen : 327
* Nombre de lignes : 4000
* Taille de l'échantillon : 4000
* Longueur moyenne des lignes : 43
* Nombre de lignes continues : 2276
* Nombre moyen des blocs de listes d'espaces libres : 3372
* Nombre de blocs de listes d'espaces libres : 4

et cela obtenu après :
Code:

1 2 analyze table TL_TEST compute statistics for table for all indexed columns ;

Citation:

Envoyé par PpPool

Citation:

Envoyé par Médiat

peut se calculer avec :

Code:

1
2
3
SELECT SUM( CASE WHEN FLACO_ID = 'A' THEN 1 ELSE 0 END),
       SUM( CASE WHEN FLACO_ID = 'B' THEN 1 ELSE 0 END)
FROM ...

Bonjour Médiat et merci de ton aide

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

@

et ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT   point_id, AVG (resu_mesure) AS moy_a,flaco_id
                 (CASE
                     WHEN (    COUNT (resu_mesure) >= 2
                           AND SUM (NVL (resu_quantitatif, 0)) = 0
                          )
                        THEN VARIANCE (resu_mesure)
                     ELSE NULL
                  END
                 ) AS var_a
            FROM tl_test
           WHERE flaco_id IN ('A','B')
        GROUP BY point_id,flaco_id

19/10/2005, 17h21
orafrance

ce serait bien d'avoir le code complet pour faire des tests :ange:
19/10/2005, 17h27
ducho

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

Citation:
Envoyé par Fred_D
Citation:
Envoyé par PpPool
Citation:
Envoyé par Médiat
peut se calculer avec :
Code:

1 2 3 SELECT SUM( CASE WHEN FLACO_ID = 'A' THEN 1 ELSE 0 END), SUM( CASE WHEN FLACO_ID = 'B' THEN 1 ELSE 0 END) FROM ...
Bonjour Médiat et merci de ton aide

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

@
et ça ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT point_id, AVG (resu_mesure) AS moy_a,flaco_id (CASE WHEN ( COUNT (resu_mesure) >= 2 AND SUM (NVL (resu_quantitatif, 0)) = 0 ) THEN VARIANCE (resu_mesure) ELSE NULL END ) AS var_a FROM tl_test WHERE flaco_id IN ('A','B') GROUP BY point_id,flaco_id
Tout d'abord, merci encore à tous de vous intéresser à mon pb.

c'est une requete métier très complexe, que j'ai bcq simplifié pour poser le problème sur le forum.

Je vais me repencher sur les fonctions analytiques, peut-etre cette fois -ci avec plus de succés !

:cry:

19/10/2005, 17h37
orafrance

Citation:

Envoyé par ducho

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

Moi j'suis ni gourou ni voyant extralucide ;)

Sans trace, impossible de connaitre les événements d'attente et donc les axes d'amélioration :?

En effet, les fonctions analytiques risque de ne pas trop t'aider... essaye de voir comment ne faire qu'une seule requête et de filtrer le résultat de cette requête selon ton besoin, j'ai l'impression que tu cherches à faire quelques chose de très complexe en une passe qui pourrait être bien plus simple en plusieurs étape ;)
19/10/2005, 17h40
PpPool
Citation:

Envoyé par ducho

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

bonjour ducho,

voici les scripts de création que j'ai utilisé :

a/ pour les tablespaces
Code:

1 2 3 4 5 --STATISTIQUES (NOLOGGING) CREATE TABLESPACE TP_USR_STATS1 DATAFILE 'D:\oracle\oradata\local\TP_USR_STATS1_01.DBF' size 20M EXTENT MANAGEMENT LOCAL AUTOALLOCATE NOLOGGING;
b/ pour la table dynamique
Code:

1 2 EXECUTE IMMEDIATE ('CREATE TABLE '||nom_table||' NOLOGGING CACHE TABLESPACE TP_USR_STATS1 AS '||chSQL) ;
19/10/2005, 18h14
Médiat

Citation:

Envoyé par PpPool

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

J'arrive un peu après la bataille, mais avec le CASE WHEN tu transformes les valeur des flacons qui ne t'intéressent par des NULL, ils ne seront pas pris en compte dans le calcul de VARIANCE (je suppose, puisque non pris en compte dans le calcul de AVG) et tu auras bien la VARIANCE des flacon A...
19/10/2005, 18h19
aline

on peux prendre le problème autrement en le découpant.

Tu fais trois sous requêtes puis deux jointures.

Pourrais tu faire tourner chacune des trois séparement et nous poster les résultats (explain plan + tkprof)?

On pourrait vois ainsi si une sous requete est plus couteuse ou bien si c'est la jointure si c'est aucune des trois...

A tout hasard, voila ce que j'avais en tête (je n'ai pas testé)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT POINT_ID, AVG(RESU_MESURE) MOY_TOTAL, 
       AVG(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) MOY_A 
       AVG(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) MOY_B, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='A' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='A' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END ) VAR_A, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='B' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='B' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END) VAR_B 
FROM TL_TEST
GROUP BY POINT_ID
HAVING SUM(RESU_REPONSE)     < 4 
   AND SUM(RESU_QUANTITATIF) = 0

PS : j'ai retirer les NVL, inutiles, à ma connaissance, dans les calculs de SUM.

Citation:

Envoyé par Médiat

A tout hasard, voila ce que j'avais en tête (je n'ai pas testé)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT POINT_ID, AVG(RESU_MESURE) MOY_TOTAL, 
       AVG(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) MOY_A 
       AVG(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) MOY_B, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='A' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='A' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END ) VAR_A, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='B' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='B' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END) VAR_B 
FROM TL_TEST
GROUP BY POINT_ID
HAVING SUM(RESU_REPONSE)     < 4 
   AND SUM(RESU_QUANTITATIF) = 0

PS : j'ai retirer les NVL, inutiles, à ma connaissance, dans les calculs de SUM.

:lahola:

Bonjour Médiat, et merci infiniment de ton aide

les résultats sont excellents. j'ai toujours un access full sur ma table malgré l'index sur le champ POINT_id

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
 
Ecoulé : 00 :00 :00.01
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=43)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=48 Card=1 Bytes=43)
   3    2       TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=4000 By
          tes=172000)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         81  consistent gets
          0  physical reads
          0  redo size
     121840  bytes sent via SQL*Net to client
       1225  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Je vais reprendre le problème posément. J'ai certainement voulu sortir la grosse usine à gaz en une seule requete.

mais grace à vous, j'ai désormais des pistes très intéressantes d'optimisation.

Merci encore une fois à tous :bravo: VIVE DVP !!!!!

@

20/10/2005, 09h22
Médiat

Citation:

j'ai toujours un access full sur ma table malgré l'index sur le champ POINT_id

C'est normal puisque le filtre se fait sur le HAVING du GROUP BY, il faut bien accéder à la totalité de la table au moins une fois pour constituer les groupes et faire les calculs...

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