Optimisation requête et trace

[Tikate]

Bonjour,

je suis sous oracle 9.2.0.4. Depuis plusieurs jours nous avons un batch qui "a priori" tourne mais ne se termine pas. Il reste bloqué sur une procédure stockée :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
PROCEDURE X
  (
    p_dt_min IN DATE,
    p_dt_max IN DATE
  ) IS
 
    -- Curseur de recherche des operations dans la table de reporting
    CURSOR c1 IS
      SELECT DISTINCT a
        FROM tab1
       ORDER BY a;
 
    -- Curseur de recherche des UO traitantes dans la table de reporting
    CURSOR c2(p_a tab3.a%TYPE) IS
      SELECT DISTINCT b
        FROM tab1
       WHERE a = p_a;
 
    -- Curseur de recherche des UO traitee dans la table de reporting
    CURSOR c3(p_a tab3.a%TYPE, p_b tab3.b%TYPE) IS
      SELECT DISTINCT c
        FROM tab1
       WHERE a = p_a
         AND b = p_b;
 
    rec1 c1%ROWTYPE;
    rec2 c2%ROWTYPE;
    rec3 c3%ROWTYPE;
 
 
 
  BEGIN
 
    -- Vidage de la table temporaire de calcul des moyennes mobiles tab2
    truncate_table('tab2');
    -- Suppression des index de la table tab2
    drop_table_index('index_tab2');
 
    -- Boucle sur le colonne a
    FOR rec1 IN c1
    LOOP
 
      -- Boucle sur colonne b
      FOR rec2 IN c2(rec1.a)
      LOOP
 
        -- Boucle sur colonne c
        FOR rec3 IN c3(rec1.a, rec2.b)
        LOOP
 
          INSERT INTO tab2
            (date_a
            ,b
            ,a
            ,c
            ,d_mym
            ,e_mym
            ,f_mym
            ,g_mym
            ,h_mym
            ,i_mym
            ,j_mym
            ,k_mym
            ,l_mym
            ,m_mym)
            SELECT date_a,
                   b,
                   a,
                   c,
                   AVG(SUM(d)) over(ORDER BY date_a rows cst_nbmm preceding) AS em,
                   AVG(SUM(e)) over(ORDER BY date_a rows cst_nbmm preceding) AS sm,
                   AVG(SUM(d - e)) over(ORDER BY date_a rows cst_nbmm preceding) AS dm,
                   AVG(SUM(g)) over(ORDER BY date_a rows cst_nbmm preceding) AS debm,
                   AVG(SUM(h)) over(ORDER BY date_a rows cst_nbmm preceding) AS finm,
                   AVG(SUM(d * nvl(n, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS uem,
                   AVG(SUM(e * nvl(o, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS usm,
                   AVG(SUM((d - e) * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS udm,
                   AVG(SUM(g * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS udebm,
                   AVG(SUM(h * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS ufinm
              FROM tab3 t
             WHERE a = rec1.a
               AND b = rec2.b
               AND nvl(c, ' ') = nvl(rec3.c, ' ')
               AND date_a >= p_dt_min
               AND date_a <= p_dt_max
             GROUP BY date_a,
                      b,
                      a,
                      c;
 
 
        END LOOP;
 
        COMMIT;
 
      END LOOP;
 
    END LOOP;
 
 
    -- Creation de l'index index_tab2 = a, b, c, date_a sur la table tab2
    create_table_index('index_tab2',
                       'tab2',
                       'a,b,c,date_a');
 
  EXCEPTION
    WHEN OTHERS THEN
      lblmsg := 'ERREUR ORACLE : ' || to_char(SQLCODE) || ' : ' || SQLERRM;
      --ecriture message d erreur
      RAISE;
 
  END X;

J'ai demandé un plan d'éxecution à la dba et voilà ce qu'elle me transmets :

Code :

1
2
3
4
5
6
7
8
9
 
CHILD_NUMBER   ID PARENT OPERATION                           OBJECT_NAME                              FILTER_PREDICATES
------------ ---- ------ ----------------------------------- ---------------------------------------- ------------------------------
           0    0        INSERT STATEMENT
                1      0 WINDOW BUFFER
                2      1 SORT GROUP BY NOSORT
                3      2 FILTER                                                                       :B2<=:B1
                4      3 TABLE ACCESS BY INDEX ROWID         tab3
                5      4 INDEX RANGE SCAN                    index_tab2                                "a"=:B5 AND "b"=:B4 AND NVL("c",' ')=NVL(:B3,' ')

Je ne mis connais pas des masses, si ce n'est pas du tout, j'ai lu pas mal de post ce matin ainsi que l'url : http://www.developpez.net/forums/f42/bases-donnees/oracle/

Il me semble que le niveau de trace de ce plan ne nous apprends pas grand chose, est ce que je me trompe?

J'ai demandé le niveau de la trace, j'attends une réponse.

Pourriez vous me conseiller?

merci par avance pour tt les réponses

[Michel SALAIS]

D'après ce que je vois dans le plan en tant que tel rien d'anormal même si des informations manquent

Qq points:
- quels sont les index sur la table et notamment sur les colonnes utilisées dans la clause where
- Le nombre de lignes de la table tbr001raa
- L'efficacité de l'index utilisé

Mais une piste importante consiste à essayer de faire une seule instruction et de se débarasser des curseurs et des boucles.

[fontome_ 123]

salut
j'ai essayé de lire ta procedure c'est pas evident j'ais pas pu detecté qqc qui louche je te propose de diviser ta procedure sur plusieurs scripts afin de voir vraiment ou ca se block c'est comme si tu dbug dans un prog sequential

[Tikate]

Envoyé par Michel SALAIS

D'après ce que je vois dans le plan en tant que tel rien d'anormal même si des informations manquent

Qq points:
- quels sont les index sur la table et notamment sur les colonnes utilisées dans la clause where
- Le nombre de lignes de la table tbr001raa
- L'efficacité de l'index utilisé

Mais une piste importante consiste à essayer de faire une seule instruction et de se débarasser des curseurs et des boucles.

Merci bcp pour ta réponse.
- De mémoire (je ne suis plus au bureau) l'index de la table tab3 (j'ai tt renommé et j'ai simplifié la procédure) est :
date_a, a b, c

- le dernier batch qui a tourné a limenté quelque chose comme 2 millions de lignes

- l'efficacité de l'index? c'est 4 colonnes constitue la clef primaire il me semble.

Sinon, peux tu me donner un exemple qui m'expliquerais comment me débarasser de mes boucles?

merci

[Tikate]

Envoyé par fontome_ 123

salut
j'ai essayé de lire ta procedure c'est pas evident j'ais pas pu detecté qqc qui louche je te propose de diviser ta procedure sur plusieurs scripts afin de voir vraiment ou ca se block c'est comme si tu dbug dans un prog sequential

Merci pour ta réponse.

J'ai enlevé pas mal d choses de la procédure, J'a gardé le plus important cad:

- truncate de la table tab2
suppression index de la table tab2
- 3 boucles imbriquées
- 1 INSERT dans une table tab2 en y mettant des données issues d'un SELECT sur des colonnes d'une table tab3 après avoir fait des moyennes mobiles sur ces colonnes
- fin des boucles, re-création de l'index de la table tab2

Voilà, j'espère que cela t'aidera à y voir plus clair.

merci

[Mario92]

Bonsoir,

Je cherche un manuel utilisateur pour Oracle Fa (Fixed Assets), je n'en trouve nulle part,

Pouvez-vous m'aider,

Merci

[Michel SALAIS]

Envoyé par Tikate

Sinon, peux tu me donner un exemple qui m'expliquerais comment me débarasser de mes boucles?

En regardant le code nous pouvons enlever les boucles et la requête n'est pratiquement pas changée : Ajouter dans la clause FROM les tables des trois curseurs et ajuster dans la clause where en conséquence.

Evidemment il faudrait dans ce cas faire attention aux index. Mais le gros problème peut-être va être les segments de rollback et là ça peut partir dans la discussion que j'adore ... Tu fais un commit dans les boucles et ce ne sera pas possible. Pourquoi ce commit a été ajouté?

[wondersonic]

Envoyé par Tikate

Merci pour ta réponse.

J'ai enlevé pas mal d choses de la procédure, J'a gardé le plus important cad:

- truncate de la table tab2
suppression index de la table tab2
- 3 boucles imbriquées
- 1 INSERT dans une table tab2 en y mettant des données issues d'un SELECT sur des colonnes d'une table tab3 après avoir fait des moyennes mobiles sur ces colonnes
- fin des boucles, re-création de l'index de la table tab2

Voilà, j'espère que cela t'aidera à y voir plus clair.

merci

Bonsoir Tikate,
vu ce que tu dis, je comprends que tu ais des problèmes de perf. Voici les points que j'ai noté et les questions en suspend :
- as-tu recalculé les statistiques de la table tab3 après le chargement de ces 2 millions de lignes ?
- Les 3 boucles imbriquées, ce n'est pas génial (surtout si l'on considère que tu commites trop souvent) (à lire : un article sur asktom.oracle.com)

Je te conseille de tenter l'approche suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO tab2
(...)
SELECT ... FROM tab3 t3, (SELECT DISTINCT a, b, c FROM tab1 ORDER BY a) t1
WHERE t3.a = t1.a
   AND t3.b = t1.b
   AND nvl(t3.c, ' ') = nvl(t1.c, ' ')
   AND t3.date_a >= p_dt_min
   AND t3.date_a <= p_dt_max
GROUP BY t3.date_a,
               t3.b,
               t3.a,
               t3.c;

Là, tu as supprimé les commit trop nombreux, tes 3 curseurs, les 3 boucles imbriquées. (un seul commit)

- la comparaison

Code :

nvl(t3.c, ' ') = nvl(t1.c, ' ')

n'est pas super performante, je te conseille de décider d'une valeur (exemple : 'NULL') afin que les colonnes
tab3.c et tab1.c soient not nullable ainsi tes index sur tab3 (date_a, a, b, c) et sur tab1 (a, b, c) seront plus sélectifs et donc plus performants MAIS pour 2 millions de lignes, je préfèrerai un FULL table scan dans le plan d'exécution de tab3 et de tab1.

Bon courage !

[fontome_ 123]

j'ai pas vraimenet voulais dire symplifier les termes lol mais plutot ton code le deviser pour que tu puisse voir où ca se plante.
bon a mon avis la seul solution qui te reste c'est d'enlever les boucles 2 propositions :
1-ou bien comme a dit salais l'utiliser dans les clause where meme si c'est pas evident a mon avis comme requete car justement les pointeurs sont la pour balayer plusieurs tables;
2-c'est create table tomporaire que tu crée au besoin pour sauvegarder le resultat de la 1,2.... reherche,et le réutiliser apres par la suite drop.
chose sur il faut enlever le sboucles imbriqué ca n'a jamais etait un bon exemple ca posait des prob dans les editeurs de compilations dedié a ca,alors qu'oracle n'est pas concu pour ca

[Tikate]

Envoyé par wondersonic

Bonsoir Tikate,
vu ce que tu dis, je comprends que tu ais des problèmes de perf. Voici les points que j'ai noté et les questions en suspend :
- as-tu recalculé les statistiques de la table tab3 après le chargement de ces 2 millions de lignes ?
- Les 3 boucles imbriquées, ce n'est pas génial (surtout si l'on considère que tu commites trop souvent) (à lire : un article sur asktom.oracle.com)

Je te conseille de tenter l'approche suivante :

Code :

Là, tu as supprimé les commit trop nombreux, tes 3 curseurs, les 3 boucles imbriquées. (un seul commit)

- la comparaison

Code :

nvl(t3.c, ' ') = nvl(t1.c, ' ')

n'est pas super performante, je te conseille de décider d'une valeur (exemple : 'NULL') afin que les colonnes
tab3.c et tab1.c soient not nullable ainsi tes index sur tab3 (date_a, a, b, c) et sur tab1 (a, b, c) seront plus sélectifs et donc plus performants MAIS pour 2 millions de lignes, je préfèrerai un FULL table scan dans le plan d'exécution de tab3 et de tab1.

Bon courage !

Merci bcp pour ta réponse.

Pour répondre à ta question sur les statistiques. Oui elle sont recalculées après l'insert.

En ce qui concerne ton dernier conseil. En fait je ne connaissais pas les plans d'exécution avant vendredi. Si j'ai bien compris, pour toi il est préférable que Oracle "balaye" la table tab3, lignes par lignes, c'est bien ça le FULL table scan, au lieu d'un balayage en ce servant du rowid comme c'est le cas TABLE ACCESS BY INDEX ROWID ...?
Je ne suis pas très forte en optimisation mais ne penses tu pas que scanner une table aussi grosse prendra trop de temps?

je te remercie
Tikate

[Tikate]

Je voulais juste ajouter une chose.

Ce qui m'ennuie c'est que le batch passait sans problème depuis plusieurs mois malgré sa lenteur. Ce que je n'explique pas c'est pourquoi maintenant celui ci reste bloqué à cette étape (la procédure que j'ai citée)? oracle ne signale aucune erreur et le process unix a toujours l'air de continuer à tourner.

Y a t'il un moyen dans les traces oracle d'en savoir plus sur ce qui ce passe?

merci

[orafrance]

Envoyé par Tikate

En fait je ne connaissais pas les plans d'exécution avant vendredi.

Si statspack est installé tu pourras peut-être quand même retrouver l'ancien plan

[wondersonic]

Envoyé par Tikate

Pour répondre à ta question sur les statistiques. Oui elle sont recalculées après l'insert.

Parfait.

Envoyé par Tikate

En ce qui concerne ton dernier conseil. En fait je ne connaissais pas les plans d'exécution avant vendredi. Si j'ai bien compris, pour toi il est préférable que Oracle "balaye" la table tab3, lignes par lignes, c'est bien ça le FULL table scan, au lieu d'un balayage en ce servant du rowid comme c'est le cas TABLE ACCESS BY INDEX ROWID ...?
Je ne suis pas très forte en optimisation mais ne penses tu pas que scanner une table aussi grosse prendra trop de temps?

Effectivement, j'entends bien un FULL table scan pour des requêtes balayant de grosses tables dans lesquelles on va utiliser réellement plus de 5% des lignes.

En fait, il faut comprendre l'accès via un index (attention, je simplifie grandement) :
- je lis les blocs de l'index (pour trouver le début) puis je lis les rowids contenus dans mon premier bloc feuille,
- pour chaque rowid j'accède à une ligne de ma table (TABLE ACCESS BY ROWID) (je filtre et je la conserve si j'ai besoin de faire des traitements dessus),
- je lis le second rowid, j'accède à une ligne de ma table etc...

Maintenant, tu sais répondre à la question suivante :

Si je dois traiter 100% des lignes de ma table de 2 millions de lignes, est-ce que je passe par un index ou par un FULL TABLE SCAN ?

Autrement dit, je lis tout l'index puis toute ma table (voir plusieurs fois le même bloc de table selon que l'index est "en phase" avec l'organisation des données de ma table => voir la notion de clustering factor) ? Ou bien je ne lis que toute ma table ?

Evidemment, je ne lis que toute la table. Ceci est vrai si je traite 100% des lignes. Et si je traite maintenant que 80% des lignes à chaque fois ? Et bien, le simple FULL TABLE SCAN sera encore plus rapide. L'index devient réellement pertinent à partir de 5% des données réellement utilisées (je ne me veux pas catégorique mais 5% est la barrière à connaître).

Maintenant, il se peut que ton CBO continue de suggérer l'utilisation d'un index, dans ce cas, fait les tests avec un hint (/*+ FULL( ma_table ) */) ou alors vérifie tes statistiques systèmes etc...

Dans tous les cas, tu pourras encore optimiser ton FULL TABLE SCAN en effectuant ta requête en parallèle (parallel query) mais ceci se fait à certaines conditions.

[wondersonic]

Envoyé par Tikate

Je voulais juste ajouter une chose.

Ce qui m'ennuie c'est que le batch passait sans problème depuis plusieurs mois malgré sa lenteur. Ce que je n'explique pas c'est pourquoi maintenant celui ci reste bloqué à cette étape (la procédure que j'ai citée)? oracle ne signale aucune erreur et le process unix a toujours l'air de continuer à tourner.

Y a t'il un moyen dans les traces oracle d'en savoir plus sur ce qui ce passe?

merci

Tu peux utiliser le package PL/SQL dbms_profiler pour savoir d'où vient réellement le problème. (sur un petit jeu de données).

Sinon, est-ce que la table tab1 a subi un gros chargement de données ?
Est-ce que tu fais beaucoup de tri sur disque (en gros, as-tu assez de mémoire ? ou est-ce que tu swapes ?)