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

**PpPool** · 19/10/2005, 15h10

Bonjour à tous,

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

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

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 :

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

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

J'obtiens une table de ce type :

Code :

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

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

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.

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

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

et voici l'explain plan

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

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.

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 :

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

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

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

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

**Jaouad** · 19/10/2005, 15h16

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

**Médiat** · 19/10/2005, 15h28

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

).

Par exemple

Code :

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

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

et

Code :

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

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

peut se calculer avec :

Code :

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

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

**PpPool** · 19/10/2005, 15h39

Bonjour Jaouad, merci de ton aide

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.

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

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 :

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

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

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.

**Jaouad** · 19/10/2005, 15h45

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

**PpPool** · 19/10/2005, 15h47

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

).

Par exemple

Code :

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

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

et

Code :

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

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

peut se calculer avec :

Code :

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

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 ?

@

**aline** · 19/10/2005, 15h53

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

**PpPool** · 19/10/2005, 15h53

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

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.

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é !

**PpPool** · 19/10/2005, 15h56

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 !!!!

**Jaouad** · 19/10/2005, 16h08

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 :

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

analyze table table_name compute statistcs ;

Je pense que cela doit venir de là

**PpPool** · 19/10/2005, 16h17

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 :

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

analyze table table_name compute statistcs ;

Je pense que cela doit venir de là

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

**Jaouad** · 19/10/2005, 16h19

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

**aline** · 19/10/2005, 16h28

Envoyé par PpPool

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 :

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

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?

**PpPool** · 19/10/2005, 16h35

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 :

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

Calculs des stats (table + index)

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

**Jaouad** · 19/10/2005, 16h43

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 :

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

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

ps : c'est moi que tu traites de boulet

**PpPool** · 19/10/2005, 16h47

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 :

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

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

ps : c'est moi que tu traites de boulet

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

**PpPool** · 19/10/2005, 16h53

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

pas de réels changements

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

Code :

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

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