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

Version imprimable

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

Bonjour à tous,

Je vais essayer d'être clair, c'est pas forcément gagné ! :oops:

Oracle9i Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production

:arrow: J'ai un traitement PL qui me permet de créer une table dynamique en fonction d'une chaine SQL passée en paramètre.
La chaine SQL est assez complexe, elle fait notamment de nombreux appels à DBMS_RANDOM.value, ce pourquoi je suis obligé de créer une table physique pour l'exploiter ensuite.

Code:

1
2
 
EXECUTE IMMEDIATE ('CREATE TABLE '||nom_table||' NOLOGGING CACHE TABLESPACE TP_USR_STATS1 AS '||chSQL) ;

J'obtiens une table de ce type :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
SQL> DESC TL_TEST
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ---------------
 POINT_ID                                           NUMBER(38)
 METH_ID                                            VARCHAR2(50)
 FLACO_ID                                           CHAR(1)
 ETIQ_REPLIQUE                                      NUMBER
 RESU_REPONSE                                       NUMBER
 RESU_QUANTITATIF                                   NUMBER
 RESU_MESURE                                        NUMBER

:arrow: la table comporte 4000 lignes. Je crée dynamiquement un index sur la colonne FLACO_ID dans mon traitement également.
Nota : la création de la table est bien optimisée, c'est quasiment instantanée.

:arrow: Je passe ensuite la requete suivante (que j'ai simplifié pour décrire le problème)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
SELECT M.POINT_ID,M.MOY_TOTAL,A.MOY_A,B.MOY_B,A.VAR_A,B.VAR_B
FROM
      (SELECT POINT_ID, AVG(RESU_MESURE) AS MOY_TOTAL
       FROM TL_TEST
       GROUP BY POINT_ID
       HAVING SUM(NVL(RESU_REPONSE,0))<4
       AND SUM(NVL(RESU_QUANTITATIF,0))=0) M,       
             (SELECT POINT_ID,AVG(RESU_MESURE) AS MOY_A,
             (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='A'
             GROUP BY POINT_ID) A,
                          (SELECT POINT_ID,AVG(RESU_MESURE) AS MOY_B,
                          (CASE WHEN (COUNT(RESU_MESURE)>=2 AND SUM(NVL(RESU_QUANTITATIF,0))=0) THEN VARIANCE(RESU_MESURE) ELSE NULL END )AS VAR_B
                          FROM TL_TEST
                          WHERE FLACO_ID='B'
                          GROUP BY POINT_ID) B   
WHERE M.POINT_ID=A.POINT_ID
AND M.POINT_ID=B.POINT_ID

:arrow: et voici l'explain plan

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     MERGE JOIN
   3    2       VIEW
   4    3         SORT (GROUP BY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
   6    5             INDEX (RANGE SCAN) OF 'I_4C48EPJAMV301GR9KQXR' (
          NON-UNIQUE)
 
   7    2       SORT (JOIN)
   8    7         VIEW
   9    8           FILTER
  10    9             SORT (GROUP BY)
  11   10               TABLE ACCESS (FULL) OF 'TL_TEST'
  12    1     SORT (JOIN)
  13   12       VIEW
  14   13         SORT (GROUP BY)
  15   14           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
  16   15             INDEX (RANGE SCAN) OF 'I_4C48EPJAMV301GR9KQXR' (
          NON-UNIQUE)

:arrow: Le pb que je cherche à résoudre est ici l'ACCESS FULL pour les calculs d'agrégats.

- J'ai essayé de créer une colonne PK supplémentaire à ma table alimentée par une séquence, mais cela n' a pas résolu mon problème (cela ralentit même quelque peu ma création de table dynamique)
- J'ai essayé d'ajouter un HINT /*+ ROWID(TL_TEST) */ mais celui-ci n'est pas pris en compte.

:arrow: Aujourd'hui, la requete que je vous présente est une requete sous-imbriquée d'une autre requete que j'ai tracé. La requete finale réalise un calcul d'agrégat globlal (variance, moyenne)+ différents calculs.

je l'ai lancé 6 fois, et les temps de réponse sont médiocres.

Code:

1
2
3
4
5
6
7
8
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      2.85       2.84         15      27822          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      2.87       2.86         15      27822          0           6

:arrow: J'ai réellement besoin d'optimiser cette requete. Elle est incluse dans un traitement itératif qui peut être lancé 50 à 100 fois.

Je tiens à préciser que j'ai bien conscience que les fonctions analytiques pourraient m'être d'un grand secours dans mon cas.
J'ai passé 1 mois là dessus, et je suis resté finalement sur la requete que je vous propose. Les fonctions analytiques ont été plus fortes que moi :wink:

Voilà, merci d'avance pour votre aide (sorry pour le long post, j'ai essayé d'être précis)
@

19/10/2005, 15h16
Jaouad

Bonjour ,
tout d'abord il est clair que cette requêtes a besoin d'être optimisé car parcourir 27822 block pour 6 lignes c'est bel et bien une gros probléme

Quelque chose attire mon attention en plus du FTS c'est le merge join :
est ce qu'il y a un produit cartésien ( cela ne m'étonneraot pas )

Est ce que tu as essayé un hint avec une autre méthode de jointure ( nesteed loop par exemple ) ou alors il faudrait ré écrire cette requête

Jaouad
19/10/2005, 15h28
Médiat
Comme Jaouad, je pense qu'il faut ré écrire cette requête, par exemple en calculant toutes tes colonnes en une seule fois (avec les CASE WHEN on peut faire des miracles :lol: ).

Par exemple
Code:

1 2 3 SELECT COUNT(*) FROM ... WHERE FLACO_ID = 'A'
et
Code:

1 2 3 SELECT COUNT(*) FROM ... WHERE FLACO_ID = 'B'
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 Jaouad, merci de ton aide

Citation:

Envoyé par Jaouad

Quelque chose attire mon attention en plus du FTS c'est le merge join :
est ce qu'il y a un produit cartésien ( cela ne m'étonneraot pas )

c'est une requete reflexive. j'ai besoin de faire remonter les calculs des agrégats intermédiaires de mes sous-requetes au niveau pére, pour réaliser d'autres traitements.

Citation:

Envoyé par Jaouad

Est ce que tu as essayé un hint avec une autre méthode de jointure ( nesteed loop par exemple )

non, mais qu'est ce que je pourrai essayer par exemple

Citation:

Envoyé par Jaouad

ou alors il faudrait ré écrire cette requête

cette requete fonctionne correctement dans d'autres conditions, et notamment lorsque je fais appel à des tables physiques de mon schéma qui ne sont pas créer dynamiquement : présence de PK, FK, index

exemple d'insertions de 14 lignes à partir de la même requete, mais portant sur une autre table de mon schéma
Code:

1 2 3 4 5 6 7 8 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.02 0 3 0 0 Execute 1 0.03 0.02 0 282 24 14 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.04 0.04 0 285 24 14
:arrow: la table que je crée dynamiquement peut être grandement améliorée, je le sais bien. mais ce qui me déconcerte est que la création d'une PK n'améliore pas les temps de réponse, j'ai également essayé de poser un index sur le champ POINT_ID, alors je vois plus koi faire.

:cry:

19/10/2005, 15h45
Jaouad

tu crée ta table et tu insére les données , puis tu fait le select et tout ca dynamiquement ?

Mais est ce que tu calcul les stats ?

Jaouad
19/10/2005, 15h47
PpPool
Citation:
Envoyé par Médiat

Comme Jaouad, je pense qu'il faut ré écrire cette requête, par exemple en calculant toutes tes colonnes en une seule fois (avec les CASE WHEN on peut faire des miracles :lol: ).

Par exemple

Code:

1 2 3 SELECT COUNT(*) FROM ... WHERE FLACO_ID = 'A'

et

Code:

1 2 3 SELECT COUNT(*) FROM ... WHERE FLACO_ID = 'B'

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 ?

@
19/10/2005, 15h53
aline

Evidement Jaouad à raison,

deux petites pistes:

essayes un index sur FLACO_ID.
Je n'y croit pas trop car tu ne lis pas beaucoup de blocs (6) mais on peux toujours voir...

Sinon, est ce que tes tables sont analysées?

Mais il est évident que tu fais trop de calcul et je pense également qu'il va faloir réecrire ta requete...
19/10/2005, 15h53
PpPool

Citation:

Envoyé par Jaouad

tu crée ta table et tu insére les données , puis tu fait le select et tout ca dynamiquement ?

Jaouad

oui dans le cas que je précise ci-dessus.
je suis amené à lancer la même requete non dynamique sur d'autres tables, et elle fonctionne très bien

Citation:

Envoyé par Jaouad

Mais est ce que tu calcul les stats ?

pour les tables créés dynamiquement, non. pour les autres tables, oui
j'ai fait le test en calculant les stats dans mon traitement et les résultats étaient moins bons.

:arrow: Je voudrai éviter autant que faire se peut de ré-écrire cette requete. Elle est pour moi satisfaisante dans la plupart des cas.

la création de la table dynamiquement peut être améliorée, j'en suis persuadé. Mais je ne sais plus koi essayé !
19/10/2005, 15h56
PpPool

Citation:

Envoyé par aline

Evidement Jaouad à raison,

deux petites pistes:

essayes un index sur FLACO_ID.
Je n'y croit pas trop car tu ne lis pas beaucoup de blocs (6) mais on peux toujours voir...

Sinon, est ce que tes tables sont analysées?

Mais il est évident que tu fais trop de calcul et je pense également qu'il va faloir réecrire ta requete...

Bonjour aline,

il y a bien un index sur la colonne FLACO_ID
c'est une requete gourmande, je le sais. Mais ce sont des traitements métiers nécessaires.

je sens que l'on va me parler des fonctions analytiques !!! moi qui ne faisait plus de cauchemar depuis 1 mois !!!! :piou:
19/10/2005, 16h08
Jaouad

est ce que dans ton traitemement tu peux ajouter un step qui est le calcul des stat
même par une commande du type :

Code:

analyze table table_name compute statistcs ;

Je pense que cela doit venir de là
19/10/2005, 16h17
PpPool

Citation:

Envoyé par Jaouad

est ce que dans ton traitemement tu peux ajouter un step qui est le calcul des stat
même par une commande du type :

Code:

analyze table table_name compute statistcs ;

Je pense que cela doit venir de là

Résultats catastrophiques, je passe de 2 s à 40s !!
19/10/2005, 16h19
Jaouad

tu as bien ajouté cette étape aprés la création de la table ?

est ce que tu peux nous renseigner sur le volumétrie de la table ?
combien tu remontes d'informations ??

peux tu également donné l'explain plan parés le calcul des stats ?

[edit] analyze tu également les indexs
19/10/2005, 16h28
aline

Citation:

Envoyé par PpPool

Citation:

Envoyé par Jaouad

est ce que dans ton traitemement tu peux ajouter un step qui est le calcul des stat
même par une commande du type :

Code:

analyze table table_name compute statistcs ;

Je pense que cela doit venir de là

Résultats catastrophiques, je passe de 2 s à 40s !!

comme le dit Jaouad, anayses-tu aussi les index?
Et quel est le nouveau plan qui est si mauvais?
Peux tu nous faire un tkprof egalement?

Volumétrie de la table : 4000 lignes
les calculs d'agrégats que je réalise porte sur 1000 lignes à chaque fois

ex de jeux de données
POINT_ID FLACO_ID ETIQ_REPLIQUE RESU_REPONSE RESU_QUANTITATIF RESU_MESURE
1 A 1 0 0 25.56
2 A 1 NULL NULL
...
1000 A 1 0 1 56.57
...
1 B 2 0 0 58.89
...
1000

Explain plan avant le calcul des stats :

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
39
40
 
Ecoulé : 00 :00 :00.01
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     MERGE JOIN
   3    2       VIEW
   4    3         SORT (GROUP BY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
   6    5             INDEX (RANGE SCAN) OF 'I_G16GDGRGMXTMG7807AJ0' (
          NON-UNIQUE)
 
   7    2       SORT (JOIN)
   8    7         VIEW
   9    8           FILTER
  10    9             SORT (GROUP BY)
  11   10               TABLE ACCESS (FULL) OF 'TL_TEST'
  12    1     SORT (JOIN)
  13   12       VIEW
  14   13         SORT (GROUP BY)
  15   14           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
  16   15             INDEX (RANGE SCAN) OF 'I_G16GDGRGMXTMG7807AJ0' (
          NON-UNIQUE)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5654  consistent gets
          0  physical reads
          0  redo size
     121868  bytes sent via SQL*Net to client
       1225  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       1000  rows processed

:arrow: Calculs des stats (table + index)

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
39
40
 
Ecoulé : 00 :00 :10.01
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=1 Bytes=104
          )
 
   1    0   NESTED LOOPS (Cost=103 Card=1 Bytes=104)
   2    1     NESTED LOOPS (Cost=71 Card=1 Bytes=65)
   3    2       VIEW (Cost=40 Card=1 Bytes=26)
   4    3         FILTER
   5    4           SORT (GROUP BY) (Cost=40 Card=1 Bytes=30)
   6    5             TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=4
          000 Bytes=120000)
 
   7    2       VIEW
   8    7         SORT (GROUP BY) (Cost=32 Card=938 Bytes=28140)
   9    8           TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=200
          0 Bytes=60000)
 
  10    1     VIEW
  11   10       SORT (GROUP BY) (Cost=32 Card=938 Bytes=28140)
  12   11         TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=2000
          Bytes=60000)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        243  consistent gets
          0  physical reads
          0  redo size
     121868  bytes sent via SQL*Net to client
       1225  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       1000  rows processed

merci encore de votre aide :boulet:

19/10/2005, 16h43
Jaouad

et bien mine de rien on avance , parce que même si les index ne sont pas pris en compte ( et la j'aimerai bien savoir pourquoi ) on n'a plus de merge join et ca c'est vachement bien .

je suppose que tu as des indexs sur cette table :

Essayons de construire des histogrammes :

Code:

analyze table table_name compute statistics for table for all indexed columns ;

ps : c'est moi que tu traites de boulet :evilred: :rire:
19/10/2005, 16h47
PpPool

Citation:

Envoyé par Jaouad

et bien mine de rien on avance , parce que même si les index ne sont pas pris en compte ( et la j'aimerai bien savoir pourquoi ) on n'a plus de merge join et ca c'est vachement bien .

je suppose que tu as des indexs sur cette table :

Essayons de construire des histogrammes :

Code:

analyze table table_name compute statistics for table for all indexed columns ;

ps : c'est moi que tu traites de boulet :evilred: :rire:

ok j'essaie de suite !!!

le GROS boulet c'est moi !! chu' interdit de smilies jusqu'à nouvel ordre !
heureusement qu'il y a des personnes comme toi pour nous venir en aide :wink:

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
39
40
41
42
43
 
Ecoulé : 00 :00 :10.01
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=101 Card=1 Bytes=104
          )
 
   1    0   NESTED LOOPS (Cost=101 Card=1 Bytes=104)
   2    1     NESTED LOOPS (Cost=70 Card=1 Bytes=65)
   3    2       VIEW (Cost=39 Card=1 Bytes=26)
   4    3         FILTER
   5    4           SORT (GROUP BY) (Cost=39 Card=1 Bytes=29)
   6    5             TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=4
          000 Bytes=116000)
 
   7    2       VIEW
   8    7         SORT (GROUP BY) (Cost=31 Card=938 Bytes=26264)
   9    8           TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=200
          0 Bytes=56000)
 
  10    1     VIEW
  11   10       SORT (GROUP BY) (Cost=31 Card=938 Bytes=26264)
  12   11         TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=2000
          Bytes=56000)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        243  consistent gets
          0  physical reads
          0  redo size
     121798  bytes sent via SQL*Net to client
       1225  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       1000  rows processed

:arrow: pas de réels changements

:arrow: si besoin, je rappel que j'ai :
1/ la table suivante

Code:

1
2
3
4
5
6
7
8
9
10
11
 
SQL> desc tl_test
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ------------
 POINT_ID                                           NUMBER(38)
 METH_ID                                            VARCHAR2(50)
 FLACO_ID                                           CHAR(1)
 ETIQ_REPLIQUE                                      NUMBER
 RESU_REPONSE                                       NUMBER
 RESU_QUANTITATIF                                   NUMBER
 RESU_MESURE                                        NUMBER

2/ un index sur la colonne FLACO_ID
3/ pas de PK

19/10/2005, 17h01
Jaouad

Tu devrais essayer avec un index sur Flaco_ID
19/10/2005, 17h05
orafrance

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 :)
19/10/2005, 17h06
PpPool

Citation:

Envoyé par Jaouad

Tu devrais essayer avec un index sur Flaco_ID

j'ai bien un index sur FLACO_ID :cry:

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