Discussion :

[chleuh]

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]

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]

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]

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]

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]

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.

[chleuh]

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

[Mohamed.Houri]

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]

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

[pachot]

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]

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

1. décider de s'y mettre au lieu d'évacuer tout ça derrière la collecte de plus des informations
2. 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
3. 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)
4. identifier les tables auxiliaires: celles qui servent de liaison entre les tables principales (relisez mes remarques sur la table c)
5. identifier les indexes qui pourrait être utiles
6. identifier les filtres pour ces tables (comme le montre @pacmann)
7. se faire une idée de la volumétrie des donnes (relisez le post initiale)
8. 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"

[pachot]

J'ai un peu du mal à voir que est-ce que vous attendez de la trace étendue dans ce cas.

La trace étendue permet de voir où sont passées les 3 heures: CPU, I/O, etc.
Et la trace montre le nombre de lignes réellement (et non estimées) renvoyés par chaque ligne du plan d'exécution. Ca permet directement de pointer sur le problème: un accés à beaucoup de lignes qui vont être éliminées plus tard par exemple, ou un nested loop exécuté un très grand nombre de fois.