[Performance] Select avec trois grosses tables

**chleuh** · 17/06/2011, 12h26

Bonjour à tous,

Je vous embête un peu parce que j'ai un problème de performance avec un select qui dure 3h. Je suis pas très doué en optimisation et du coup je bloque un peu.

Mon select se base sur 2 tables et 1 vue.
La première table (table a) possède une volumétrie de 2millions de lignes
la seconde table (table b) est petite (11 000 lignes)
la vue est une vue assez simple et rapide à lire (table c) (20 000 lignes)

Afin d'améliorer les performances du select, j'ai crée des index sur mes table :
ma table b (index 1 : ALTACCT,TYPE index 2 : b.CNP_ACCT_MAGN, b.DESCR index 3 :CNP_ACCT_MAGN)

sur ma table A j'ai crée également 4 index (index 1 : ALTACCT,PRODUCT index2 : CNP_MEGA_ENTITE,FISCAL_YEAR, ACCOUNTING_PERIOD, ALTACCT index 3 : ALTACCT et index 4 : PRODUCT)

Le hic c'est que j'ai toujours un full scan sur ma table a que je n'arrive pas à optimiser. Du coup je pense que le trois heures est du à ce full scan.

Ma question est donc :
Est-il possible d’éviter un full scan sur ma table A ?

Le SQL en question le voici

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
 
SELECT 'TRIM_RBEV1' 
a.cnp_mega_entite 
a.fiscal_year, a.accounting_period, a.altacct, b.cnp_acct_magn,
b.descr, -SUM (a.amount)
FROM ps_cnp_smldg_t28 a, ps_cnp_cpt_magn b, ps_cnp_prdct_nm_vw c
WHERE b.altacct = a.altacct
AND b.TYPE = 'P' 
AND a.product = c.product(+)
AND (   b.cnp_acct_magn = (SELECT d.cnp_acct_magn FROM ps_cnp_norm_magn d WHERE d.cnp_norm = NVL (c.cnp_norm, 'IFRS4')
 AND d.cnp_acct_magn = b.cnp_acct_magn)
 OR NOT EXISTS (SELECT 'X'
 FROM ps_cnp_norm_magn d
 WHERE b.cnp_acct_magn = d.cnp_acct_magn)) 
 AND NOT EXISTS (SELECT 1
                          FROM ps_cnp_cpt_magn
                         WHERE altacct = a.altacct AND TYPE = 'A')
   GROUP BY a.cnp_mega_entite,
            a.fiscal_year,
            a.accounting_period,
            a.altacct,
            b.cnp_acct_magn,
            b.descr 
HAVING SUM (a.amount) >=
                     0 ;

Pour plus de lisibilité j'ai mis mon plan d’exécution dans le second message plus bas

Pour info je suis sous oracle 10.2.0.1.0
Auriez vous une piste vers laquelle je puisse m'orienter ?

Merci beaucoup pour votre attention .

**Waldar** · 17/06/2011, 12h38

Les plans d'exécution en XML c'est sympa, mais assez illisible sur le forum en l'état.

Exécutez ceci :

Code :

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

1
2
3
4
5
6
set linesize 250;
 
explain plan for
<votre_requête>;
 
SELECT * FROM TABLE(dbms_xplan.display);

**chleuh** · 17/06/2011, 12h48

Effectivement

Je savais pas trop comment vous le mettre à disposition.

Merci pour l'astuce. Voici mon plan d'éxecution

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
44
PLAN_TABLE_OUTPUT
 
Plan hash value: 300597425
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |   131 |  3153   (2)| 00:00:38 |
|*  1 |  FILTER                             |                    |       |       |            |          |
|   2 |   HASH GROUP BY                     |                    |     1 |   131 |  3153   (2)| 00:00:38 |
|*  3 |    FILTER                           |                    |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID     | PS_CNP_CPT_MAGN    |     1 |    50 |     2   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                   |                    |     1 |   131 |  3152   (2)| 00:00:38 |
|   6 |       NESTED LOOPS OUTER            |                    |     1 |    81 |  3150   (2)| 00:00:38 |
|*  7 |        HASH JOIN RIGHT ANTI         |                    |     1 |    47 |  3082   (2)| 00:00:37 |
|*  8 |         INDEX FAST FULL SCAN        | PSZCNP_CPT_MAGN    |  2564 | 28204 |    11   (0)| 00:00:01 |
|   9 |         TABLE ACCESS FULL           | PS_CNP_SMLDG_T28   |  1680K|    57M|  3057   (2)| 00:00:37 |
|  10 |        VIEW PUSHED PREDICATE        | PS_CNP_PRDCT_NM_VW |     1 |    34 |    68   (2)| 00:00:01 |
|* 11 |         HASH JOIN OUTER             |                    |     1 |    26 |    68   (2)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID| PS_PRODUCT_TBL     |     1 |    14 |    62   (0)| 00:00:01 |
|* 13 |           INDEX SKIP SCAN           | PS1PRODUCT_TBL     |     1 |       |    61   (0)| 00:00:01 |
|* 14 |          MAT_VIEW ACCESS FULL       | VM_TDO_R_NORM_IFRS |    73 |   876 |     5   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN              | PSACNP_CPT_MAGN    |     1 |       |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN               | PS_CNP_NORM_MAGN   |     1 |    19 |     0   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN               | PS_CNP_NORM_MAGN   |     2 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SUM("A"."AMOUNT")>=0)
   3 - filter("B"."CNP_ACCT_MAGN"= (SELECT "D"."CNP_ACCT_MAGN" FROM "PS_CNP_NORM_MAGN" "D" WHERE 
              "D"."CNP_NORM"=NVL(:B1,'IFRS4') AND "D"."CNP_ACCT_MAGN"=:B2) OR  NOT EXISTS (SELECT 0 FROM 
              "PS_CNP_NORM_MAGN" "D" WHERE "D"."CNP_ACCT_MAGN"=:B3))
   7 - access("ALTACCT"="A"."ALTACCT")
   8 - filter("TYPE"='A')
  11 - access("A"."CNP_ETAT"="B"."NIF_CD_ETAT"(+) AND 
              "B"."NIF_CD_PROD"(+)=SUBSTR("A"."CNP_PRODUIT",2,5))
  13 - access("A"."PRODUCT"="A"."PRODUCT")
       filter("A"."PRODUCT"="A"."PRODUCT")
  14 - filter("B"."NIF_CD_ETAT"(+) IS NOT NULL)
  15 - access("B"."ALTACCT"="A"."ALTACCT" AND "B"."TYPE"='P')
  16 - access("D"."CNP_ACCT_MAGN"=:B1 AND "D"."CNP_NORM"=NVL(:B2,'IFRS4'))
  17 - access("D"."CNP_ACCT_MAGN"=:B1)

J'ai également oublié d'ajouter que ma table A est une table que j'alimente juste avant ce select. Je mets à jour du coup les statistiques dès la fin de l'alimentation
Merci

**pacmann** · 17/06/2011, 14h11

Salut,

Un full scan même sur 2 millions de lignes, ce n’est pas en soi le problème (en tous cas, ça ne justifie pas trois heures de traitement).
Par contre, c’est ce qu’on fait de ces 2 millions de lignes qui est important.

En l’occurrence dans ton plan, il commence par :

Code :

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

1
2
3
4
5
6
7
8
9
 
AND NOT EXISTS (SELECT 1
                FROM ps_cnp_cpt_magn
                WHERE altacct = a.altacct 
                  AND TYPE = 'A')
 
|*  7 |        HASH JOIN RIGHT ANTI         |                    |     1 |    47 |  3082   (2)| 00:00:37 |
|*  8 |         INDEX FAST FULL SCAN        | PSZCNP_CPT_MAGN    |  2564 | 28204 |    11   (0)| 00:00:01 |
|   9 |         TABLE ACCESS FULL           | PS_CNP_SMLDG_T28   |  1680K|    57M|  3057   (2)| 00:00:37 |

Avec ce résultat, il réalise ensuite des NL OUTER et d'autres trucs... s'il y a beaucoup de lignes après la première étape (que j'ai citée plus haut), ça devient catastrophique.

La question qu’il faut se poser : quel est le groupe de conditions qui permet vraiment de restreindre la sélection sur la table A ?
(Le filtre sur la table B peut être ?)
Est-ce que l’index sur ALTACCT de la table A est très sélectif ?

**orafrance** · 17/06/2011, 15h13

Si je ne m'abuse ceci :

Code :

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

1
2
3
 OR NOT EXISTS (SELECT 'X'
 FROM ps_cnp_norm_magn d
 WHERE b.cnp_acct_magn = d.cnp_acct_magn))

Ne peux pas arriver. Si cette condition était vérifiée alors le SELECT ne retournerait pas de ligne

Ceci :

Code :

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

1
2
 b.cnp_acct_magn = (SELECT d.cnp_acct_magn FROM ps_cnp_norm_magn d WHERE d.cnp_norm = NVL (c.cnp_norm, 'IFRS4')
 AND d.cnp_acct_magn = b.cnp_acct_magn)

Ne peut-il pas être remplacé par :

Code :

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

NVL(b.cnp_norm,'IFRS4') = 'IFRS4'

Serait-il possible de nous expliquer un peu le fonctionnel et donner des alias plus parlant que a, b,c ou d ?

**mnitu** · 17/06/2011, 15h19

C’est full scan à cause du Hash Join. C’est Hash Join parce que vous n’avez pas des filtres vraiment utiles ni sur la table A ni sur ta table B. Et comme de plus il y un outer join avec la vue matérialisé c et que cette vue filtre dans une sous-requête sur la table b il en déduit que le meilleur plan est le Hash Join (la Table A est grande la table B petite).
Je pense que vous devrez retravailler votre requête.

**Mohamed.Houri** · 17/06/2011, 15h39

Bonjour,

De mon côté je vois trois points:

(1) il serait mieux d'avoir l'explain plan en le prenant directement de la mémoire après execution de la query (en ajoutant le hint /*+ gather_plan_statistics) et en faisant ceci

Code :

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

1
2
 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Ainsi, nous pourrions voir
(1.a) quelle est l'operation la plus couteuse en temps
(1.b) la précision des statistiques sur lesquelles se base le CBO

(2) comme pacman l'a bien noté, la première opération effectuée par votre requete est l'operation 8 suivie de la 9 suivie de la 7 puis de la 13 etc... Les premières opérations à elles seules génèrent un volume important qu'il convient d'eliminer plutot, pour une meilleure performance (start small and keep small)

(3) je vois que vous ne selectionnez aucun enregistrement de la table ps_cnp_prdct_nm_vw pourquoi alors la laisser dans la "from list". Est-ce la query suivante ne serait pas mieux

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
 
SELECT 'TRIM_RBEV1' 
       ,a.cnp_mega_entite 
       ,a.fiscal_year
       ,a.accounting_period
       ,a.altacct
       ,b.cnp_acct_magn
       ,b.descr
       ,-SUM (a.amount)
FROM   ps_cnp_smldg_t28 a
      ,ps_cnp_cpt_magn  b      
WHERE b.altacct = a.altacct
AND   b.TYPE    = 'P' 
AND   EXISTS (SELECT NULL
              FROM ps_cnp_prdct_nm_vw c
              WHERE a.product = c.product(+))
AND  (b.cnp_acct_magn = (SELECT d.cnp_acct_magn 
                         FROM ps_cnp_norm_magn d 
                         WHERE d.cnp_norm = NVL (c.cnp_norm, 'IFRS4')
                         AND   d.cnp_acct_magn = b.cnp_acct_magn
                         )
       OR NOT EXISTS (SELECT 'X'
                      FROM ps_cnp_norm_magn d
                      WHERE b.cnp_acct_magn = d.cnp_acct_magn)
      ) 
AND NOT EXISTS (SELECT 1
                FROM ps_cnp_cpt_magn
                WHERE altacct = a.altacct 
                AND TYPE = 'A')
GROUP BY    a.cnp_mega_entite,
            a.fiscal_year,
            a.accounting_period,
            a.altacct,
            b.cnp_acct_magn,
            b.descr 
HAVING SUM (a.amount) >=0 ;

Bien à vous

Mohamed Houri

**mnitu** · 17/06/2011, 16h01

Envoyé par Mohamed.Houri

...
(3) je vois que vous ne selectionnez aucun enregistrement de la table ps_cnp_prdct_nm_vw pourquoi alors la laisser dans la "from list". Est-ce la query suivante ne serait pas mieux

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
SELECT ...
FROM   ps_cnp_smldg_t28 a
      ,ps_cnp_cpt_magn  b      
WHERE b.altacct = a.altacct
AND   b.TYPE    = 'P' 
AND   EXISTS (SELECT NULL
              FROM ps_cnp_prdct_nm_vw c
              WHERE a.product = c.product(+))
AND  (b.cnp_acct_magn = (SELECT d.cnp_acct_magn 
                         FROM ps_cnp_norm_magn d 
                         WHERE d.cnp_norm = NVL (c.cnp_norm, 'IFRS4')
                         AND   d.cnp_acct_magn = b.cnp_acct_magn
                         )
...

La vue matérialisé ps_cnp_prdct_nm_vw "colle" la table A et la table B.
Bref malheureusement, votre proposition ne marchera pas.

**chleuh** · 17/06/2011, 16h11

Vraiment merci pour vos réponses,

Cela ma permis d'améliorer mon select voici mes actions :
grâce à Pacmann j'ai remarqué que cette condition

Code :

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

1
2
3
4
AND NOT EXISTS (SELECT 1
FROM ps_cnp_cpt_magn
WHERE altacct = a.altacct 
AND TYPE = 'A')

Pouvait être améliorer, en effet ma première condition est la restriction du champ altacct par les données présente dans ma table b

Code :

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

where b.altacct = a.altacct

Ainsi j'ai remplacé la condition par (b.altacct au lieu de a.altacct)

Code :

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

1
2
3
4
AND NOT EXISTS (SELECT 1
FROM ps_cnp_cpt_magn
WHERE altacct = B.altacct 
AND TYPE = 'A')

Ceci doit améliorer les performances je pense.

En ce qui concerne ton point 3, Mohamed, la table c j'en ai besoin juste en dessous

Code :

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

1
2
3
4
5
6
7
8
AND   EXISTS (SELECT NULL
FROM ps_cnp_prdct_nm_vw c
WHERE a.product = c.product(+))
AND  (b.cnp_acct_magn = (SELECT d.cnp_acct_magn 
FROM ps_cnp_norm_magn d
/*Recupération de l'information souhaitée dans ma table C*/ 
WHERE d.cnp_norm = NVL (c.cnp_norm, 'IFRS4')
AND   d.cnp_acct_magn = b.cnp_acct_magn)

Donc je ne pourrais plus la récupérer si je l'enlève de la "from list" je me trompe ?

En ce qui concerne ta remarque orafrance, fonctionnellement c'est un peu compliqué mais disons que la condition

Code :

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

1
2
3
OR NOT EXISTS (SELECT 'X'
 FROM ps_cnp_norm_magn d
 WHERE b.cnp_acct_magn = d.cnp_acct_magn))

Me permet de ramener des lignes de la table b pour lesquels je n'ai aucune occurrence dans ma table d.
En fait ma table A est une table qui va me ramener des montants par compte (champ altacct) et sous compte (cnp_acct_magn).
Pour un altacct je peux avoir 1 ou n cnp_acct_magn. Afin de choisir le bon sous compte je dois vérifier que le sous compte possede la norme lié à un produit (table c). cette transco sous compte norme est disponible grace à ma table d.

Merci mnitu, pour retravailler la requête si malgré ces optimisations je ne gagne pas assez de temps, je pense que je m'orienterais faire votre proposition et là je m'aiderais d'un fonctionnel.

Donc je relance mon traitement et je vous tiens informer

Merci à tous pour vos remarques judicieuses

**Waldar** · 17/06/2011, 16h18

Une indentation et une syntaxe moderne permettent aussi de mieux s'y retrouver :

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
  select 'TRIM_RBEV1'
       , a.cnp_mega_entite 
       , a.fiscal_year
       , a.accounting_period
       , a.altacct
       , b.cnp_acct_magn
       , b.descr
       , -sum(a.amount)
    from ps_cnp_smldg_t28 a
         inner join ps_cnp_cpt_magn b
           on b.altacct = a.altacct
         left outer join ps_cnp_prdct_nm_vw c
           on c.product = a.product
   where b.type = 'P' 
     and (b.cnp_acct_magn = (select d.cnp_acct_magn
                               from ps_cnp_norm_magn d
                              where d.cnp_norm = nvl(c.cnp_norm, 'IFRS4')
                                and d.cnp_acct_magn = b.cnp_acct_magn)
      or  not exists (select null
                        from ps_cnp_norm_magn e
                       where e.cnp_acct_magn = b.cnp_acct_magn)) 
     and  not exists (select null
                        from ps_cnp_cpt_magn f
                       where f.altacct = a.altacct
                         and f.type = 'A')
group by a.cnp_mega_entite,
         a.fiscal_year,
         a.accounting_period,
         a.altacct,
         b.cnp_acct_magn,
         b.descr 
  having sum(a.amount) >= 0;

Comme dit précédemment, vous n'avez pas réellement de filtre sur votre table A. Le full scan n'est donc pas forcément le problème.

Par contre, l'histoire des EXISTS, NOT EXISTS, OR dans votre filtre, ressemble un peu à du bricolage, une jointure externe "pourrait" probablement faire la même chose.

Je partage l'avis de mnitu quant à la ré-écriture de votre requête, en se basant sur le besoin fonctionnel initial.

**pacmann** · 17/06/2011, 16h26

Envoyé par chleuh

Vraiment merci pour vos réponses,

...
Ainsi j'ai remplacé la condition par (b.altacct au lieu de a.altacct)
...
Ceci doit améliorer les performances je pense.
...
Donc je relance mon traitement et je vous tiens informer

...

Euh au lieu d'attendre 3 heures de plus, je t'aurais bien suggéré de relancer un explain plan sur la requête modifiée et vérifier que le plan a bien changé

Je me permets juste de reposer la question :
Une fois que tu as appliqué tous tes filtres sur la table B, ça correspond à combien de lignes de la table A ? (par le critère altacct)

Si ça filtre très fort, il faut orienter le retravail de la requête (voire les hints en dernier recours) pour que le plan aille vers B -> filtre sur B -> NL JOIN sur A...

**mnitu** · 17/06/2011, 16h55

Envoyé par pacmann

...Si ça filtre très fort, il faut orienter le retravail de la requête (voire les hints en dernier recours) pour que le plan aille vers B -> filtre sur B -> NL JOIN sur A...

Il ne peut pas à cause du C. Pour filtrer B il lui faut C mais C est lié à A. Avec une requête "normale" l'optimiseur aurait trouvé seul la réponse.

**pachot** · 17/06/2011, 18h14

Bonjour,

J'appuie la remarque de Mohamed puisque c'est le seul moyen de voir clairement ce qu'il se passe durant l'exécution. Parce que faire des hypothèses et des test empiriques, sur une requête qui prends 3 heures, ça peut durer longtemps...

-> ajouter /*+ gather_plan_statistics */ après le premier select
-> lancer la requête
-> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

ou mieux pour avoir les wait events en plus:
-> exec DBMS_SESSION.SESSION_TRACE_ENABLE()
-> lancer la requête
-> disconnect
-> tkprof sur la trace

Cordialement,
Franck.

**mnitu** · 18/06/2011, 12h47

Envoyé par pachot

...
J'appuie la remarque de Mohamed puisque c'est le seul moyen de voir clairement ce qu'il se passe durant l'exécution. Parce que faire des hypothèses et des test empiriques, sur une requête qui prends 3 heures, ça peut durer longtemps...
...

J'ai un peu du mal à voir que est-ce que vous attendez de la trace étendue dans ce cas. Si l'optimisation des requêtes pourrait être réduite à une simple collecte des statistiques on aurait fait un outil pour traiter ce type de problème. En réalité ça à été fait (Oracle Advisors) mais, en gros, ça marche que pour les cases d'école: The Oracle Advisors from a different perspective: Are you a monkey or an astronaut. Je suis convaincu qu'avec un peu d'effort vous pouvez nous dire quelle sont les événements que vous aller trouver dans la trace étendue.
De plus ce que vous appelez des hypothèses sont en réalité des conclusions. Ces conclusions sont issue de l'analyse de la requête. Et oui le plus dur dans l'analyse est de s'y mettre. C'est exactement ce que nous avons fait: @pacmann, @waldar, @orafrance, moi même en dans une certain mesure aussi @Mohamed.Houri.
Voilà les étapes de cette analyse:

décider de s'y mettre au lieu d'évacuer tout ça derrière la collecte de plus des informations
formater la requête comme le montre @waldar (il n'est pas obligatoire de utiliser les jointures AINSI mais souvent ça donne une meilleur lisibilité entre les clause des jointures et celles des filtres
identifier le cœur de la requête: c'est à dire les tables à partir de quelle les données sont retournées ou qui profilent le résultat (comme le montre @ Mohamed.Houri dommage qu'en suite il abandonne)
identifier les tables auxiliaires: celles qui servent de liaison entre les tables principales (relisez mes remarques sur la table c)
identifier les indexes qui pourrait être utiles
identifier les filtres pour ces tables (comme le montre @pacmann)
se faire une idée de la volumétrie des donnes (relisez le post initiale)
imaginer le plan d'exécution optimale (comme le montre @pacmann)

Je vous recommande chaleureusement de lire le chapitre 5 dans Refactoring SQL Applications par Stéphane Faroult ou même l'excellent article du Jonathan Lewis "Construire des requêtes SQL efficaces: Une approche visuelle, d'après Jonathan Lewis"

**Mohamed.Houri** · 18/06/2011, 20h00

Bonjour,

Il est vrai que connaitre le modèle est primordial dans le tuning des requêtes. J'ai essayé de bien faire dérouler toutes les opérations de 1 jusqu'à 17 en me concentrant sur les deux pavés (8 et 9 -->7) et (13,12,14,11--> 10)

On voit bien que les deux opérations 7 et 10 conduisent les opérations filles (8,9) et (13,12,14,11) respectivement. Ce qui a retenu mon attention ce sont les opérations 13 et 10

Code :

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

1
2
3
 
|  10 |        VIEW PUSHED PREDICATE        | PS_CNP_PRDCT_NM_VW |     1
|* 13 |           INDEX SKIP SCAN           | PS1PRODUCT_TBL     |     1 |

Il y a un index skip scan sur PS1PRODUCT_TBL qui grâce au predicate 13 repris ici

Code :

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

1
2
3
 
13 - access("A"."PRODUCT"="A"."PRODUCT")
       filter("A"."PRODUCT"="A"."PRODUCT")

semble indiquer que la définition de l'index PS1PRODUCT_TBL(TBL pour table!!!) semble être celle que vous avez identifiée par index1 sur la table a (altacct, product). Selon ce que vous avez écrit, il existe pourtant un index commençant par la colonne product. En tous les cas le choix d'un index skip scan n'est pas approprié et un re-design des indexes s'impose.

En passant, je remarque que l'index 3 (altacct) sur la table a est inutile parce qu'il est couvert par l'index 1 (altacct, product).

Comme toujours, il faut bien regarder la partie predicate, elle contient des informations importantes. Par exemple je vois ceci

Code :

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

1
2
3
 
11 - access("A"."CNP_ETAT"="B"."NIF_CD_ETAT"(+) AND 
              "B"."NIF_CD_PROD"(+)=SUBSTR("A"."CNP_PRODUIT",2,5))

Le substr(a.cnp_produit,2,5) pourrait provenir de la définition de la materialized view. Si c'est le cas peut-être qu'un function based index sur cette expression pourrait faire améliorer l'accès à la materialized view (attention si vous optez pour cette option de ne pas forcer le cursor_sharing à FORCE).

Vous avez un index sur les colonnes suivantes
a.cnp_mega_entite,
a.fiscal_year,
a.accounting_period,
a.altacct,

Et vous faites un select des colonnes suivantes

,a.cnp_mega_entite
,a.fiscal_year
,a.accounting_period
,a.altacct
,SUM (a.amount)

Pourquoi ne pas avoir créé l'index suivant (à la place de l'autre)
a.altacct
a.product
a.cnp_mega_entite
a.fiscal_year
a.accounting_period
a.amout

Ainsi, vous pourriez peut-être eviter l'accès à la table ps_cnp_smldg_t28 a

Quant à l'opération 10, elle indique qu'un push predicate a eu lieu sur la matérialized view. Juste pour voir quelle réaction va avoir votre query j'aurai essayé de l'executer avec le hint /*+ no_push_pred(c) */

Une autre remarque aussi sur l'utilisation des naming standards. Vos tables et vos indexes ne respectent pas un naming standard convenable. Pourtant c'est important dans la clarté des explain plans et de ceux qui veulent les lires

Enfin, je vous conseille toujours d'extraire le vrai explain plan en utilisant

Code :

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

1
2
 
select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

Ceci montre surtout la difference entre les estimations faites par le CBO et les calculs réels faits par le "SQL engine" lors de l'execution de la requête

J'espère qu'au moins ceci servira à inculquer une bonne approche de diagnostique des explains plan

Bien à vous

Mohamed Houri

**mnitu** · 19/06/2011, 11h17

Je pense que la requête suivante est équivalente à la requête initiale et bien sûr qu'elle devrait tourner un peu plus vite:

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
 
  SELECT 'TRIM_RBEV1'
       , a.cnp_mega_entite 
       , a.fiscal_year
       , a.accounting_period
       , a.altacct
       , t.cnp_acct_magn
       , t.descr
       , -sum(a.amount)
    FROM ps_cnp_smldg_t28 a
	INNER JOIN
        (
	Select b.altact, b.cnp_acct_magn, b.descr, d.cnp_norm
	  From ps_cnp_cpt_magn b
	       Left Outer Join
	       ps_cnp_norm_magn d
	    On (d.cnp_acct_magn = b.cnp_acct_magn)
	 Where type = 'P'
	   And b.altact Not In (Select altact from ps_cnp_cpt_magn Where type = 'A')
        ) t
    ON t.altacct = a.altacct
       LEFT OUTER JOIN 
       ps_cnp_prdct_nm_vw c
    ON (c.product = a.product)  
Where t.cnp_norm = c.cnp_norm
   Or t.cnp_norm = 'IFRS4'
GROUP BY a.cnp_mega_entite,
         a.fiscal_year,
         a.accounting_period,
         a.altacct,
         t.cnp_acct_magn,
         t.descr 
  HAVING sum(a.amount) >= 0;

**orafrance** · 20/06/2011, 08h32

Houaaaa

Eventuellement, ça vaut le coup de tester le remplacement de :

Code :

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

 AND b.altact NOT IN (SELECT altact FROM ps_cnp_cpt_magn WHERE type = 'A')

Par

Code :

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

 AND NOT EXISTS (SELECT 1 FROM ps_cnp_cpt_magn WHERE b.altact = altact  AND type = 'A')

Selon l'indexation ça peut être plus intéressant.

**chleuh** · 23/06/2011, 09h24

Désolé pour le petit retard mais j'étais malade

Bonne nouvelle, mon problème est résolu. Après les conseils de mnitu, on a revu la requête afin d'avoir des critéres réellement discriminants sur ma table a. De plus j'ai une modification importante sur la vue (table c) et j'ai donc aussi créer une snapshot.

Merci à tous pour vos conseils !

**cutudi** · 01/07/2011, 18h27

Je te proposerais d'utiliser un INTERSECT afin de supprimer les données que tu ne souhaites pas apparaitre dans la requête. Je te proposerais une autres requête mais je ne comprends pas très bien ce que tu souhaites.

J'ai eu une expérience similaire en voulant (cas d'opérateur téléphonique) sélectionner pour un jeu téléphonique tous les numéros (plus de 4millions) sauf ceux des employés. il a fait plus de 2 heures. Un intersect à fait moins de 2 minutes!

Bon je réfléchis à ta requête et je te fais signe.