[10gR2] Différence temps traitements même requete

**ZERS** · 13/08/2007, 15h31

Bonjour,

je poste ici en lieu et place de mon DBA, suis pas un pro.
Nous avons une reqûete, qui fait partie d'un body d'un package.

Voici la requete :

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
 
UPDATE  T_WW_TRC_ASSORT_UL_WAU WAU
         SET WAU.WAU_IND_TRT = 1
       WHERE NOT EXISTS
       (SELECT 1
        FROM T_FG_TRC_ASSORT_CDE_TAC TAC,T_WW_TRC_ASSORT_UL_WAU WAU
        WHERE TAC.TAC_COD_MAGASIN = 1625 
WAU.WAU_COD_MAGASIN    
        AND TAC.TAC_COD_FOUR = WAU.WAU_COD_FOUR
        AND TAC.TAC_COD_FILIERE = WAU.WAU_COD_FILIERE
        AND TAC.TAC_COD_RACINE = WAU.WAU_COD_RACINE
        AND TAC.TAC_COD_VL = WAU.WAU_COD_VL
        AND TAC.TAC_COD_UL = WAU.WAU_COD_UL
        AND TAC.TAC_COD_CONTRAT_COM = WAU.WAU_COD_CONTRAT_COM
        AND TAC.TAC_COD_VA = WAU.WAU_COD_VA
        AND WAU.WAU_STA_COMMANDABLE = TAC.TAC_STA_COMMANDABLE
        AND WAU.WAU_COD_RESEAU = TAC.TAC_COD_RESEAU
        AND TAC.TAC_DAT_DEBUT = WAU.WAU_DAT_DEBUT
               )
         AND WAU.WAU_IND_TRT = 0
         AND WAU.WAU_COD_MAGASIN = 1625;

Sous SQLPLUS, lorsque l'on exécute cette requete tandis que la table de travail T_WW_TRC_ASSORT_UL_WAU (table temporaire) est populée avec 2900 enr, ca dure 3s.

Lorsque l'on lance EXACTEMENT cette même requête, mais entre une commande

Code :

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

BEGIN la requete END

sans ajout de code supplémentaire, ca dure 6mns.

Une explication ?

**orafrance** · 13/08/2007, 15h43

on peut voir le code PL/SQL ?

**ZERS** · 13/08/2007, 15h46

Envoyé par orafrance

on peut voir le code PL/SQL ?

Ben c'est celui qu'il y a au dessus.
La seule différence c'est que dans que le paramétre de la fonction est utilisée dans le body, en lieu et place de la valeur 1625 de la requete ci-dessus.

donc :

Code :

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

1
2
3
4
5
declare n_site varchar(200)
BEGIN
la requete ci-dessus
END

Y'a rien de plus

**ZERS** · 13/08/2007, 15h47

mauvaise manip

**orafrance** · 13/08/2007, 15h49

Voila la différence. Dans le code PL/SQL Oracle ne connait pas la valeur au moment du parsing du coup le plan d'exécution doit être différent.

Tu devrais essayer de changer le paramètre cursor_sharing qui doit être à EXACT en SIMILAR. Sinon, t'a plus qu'à jouer avec les hints

**ZERS** · 13/08/2007, 15h57

Envoyé par orafrance

Voila la différence. Dans le code PL/SQL Oracle ne connait pas la valeur au moment du parsing du coup le plan d'exécution doit être différent.

Tu devrais essayer de changer le paramètre cursor_sharing qui doit être à EXACT en SIMILAR. Sinon, t'a plus qu'à jouer avec les hints

C'est bizarre ce que tu me dis, car quand on a modifié le package pour mettre une valeur en dur à la place du paramétre, on a le même soucis.

J'ajoute qu'initialement, on compare des temps de traitements entre une base 9i et 10gR2 après un upgrade de version. Donc nous avions découvert que cette requête était plus longue en 10g par rapport à la 9i.

En creusant, on s'aperçoit qu'elle est plus longue en 10g quand elle est entre BEGIN..END, et ce que ce soit avec un paramétre ou une valeur en dur.
A noter que le paramétre CURSOR_SHARING est à EXACT des deux côtés (en 9i et 10gR2).

**orafrance** · 13/08/2007, 16h04

Tu mets la valeur en dur ou tu valorises la variable en dur ?

**ZERS** · 13/08/2007, 16h07

Envoyé par orafrance

Tu mets la valeur en dur ou tu valorises la variable en dur ?

on a fait les deux. Aucune différence, toujours aussi long.
Les plans d'exécutions entre la 9i et la 10g pour les deux tables, notamment la table de travail (temporaire) sont rigoureusement identiques (d'ailleurs calcul des stats locké pour la table temporaire ce qui est une préco Oracle pour ce genre d'objets).

**orafrance** · 13/08/2007, 16h09

Résumons :

- cardinalité identique (notamment la table de travail)
- plan d'indexation identique
- statistiques identiques et gelés sur la table de travail
- plans d'exécution identiques
- perf dégradées en 10g

C'est bien ça ? T'as fait un tkprof ou au moins regardé v$session_wait pour savoir d'où ça pouvait venir ? T'as essayé de recalculer les stats systèmes ?

**ZERS** · 13/08/2007, 16h21

Envoyé par orafrance

Résumons :

- cardinalité identique (notamment la table de travail)
- plan d'indexation identique
- statistiques identiques et gelés sur la table de travail
- plans d'exécution identiques
- perf dégradées en 10g

Oui c'est cela.

voici un copier-coller d'un TKPROF. Encore une fois je suis pas dba, donc je peux pas tout comprendre à tes questions (DBA en vacances )

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
UPDATE T_WW_TRC_ASSORT_UL_WAU WAU SET WAU.WAU_IND_TRT = 1
WHERE
 NOT EXISTS (SELECT 1 FROM T_FG_TRC_ASSORT_CDE_TAC TAC WHERE
  TAC.TAC_COD_MAGASIN = :B1 AND TAC.TAC_COD_FOUR = WAU.WAU_COD_FOUR AND
  TAC.TAC_COD_FILIERE = WAU.WAU_COD_FILIERE AND TAC.TAC_COD_RACINE =
  WAU.WAU_COD_RACINE AND TAC.TAC_COD_VL = WAU.WAU_COD_VL AND TAC.TAC_COD_UL =
  WAU.WAU_COD_UL AND TAC.TAC_COD_CONTRAT_COM = WAU.WAU_COD_CONTRAT_COM AND
  TAC.TAC_COD_VA = WAU.WAU_COD_VA AND WAU.WAU_STA_COMMANDABLE =
  TAC.TAC_STA_COMMANDABLE AND WAU.WAU_COD_RESEAU = TAC.TAC_COD_RESEAU AND
  TAC.TAC_DAT_DEBUT = WAU.WAU_DAT_DEBUT ) AND WAU.WAU_IND_TRT = 0 AND
  WAU.WAU_COD_MAGASIN = :B1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    118.54     173.84        350    2278409       1641        1082
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    118.54     173.84        350    2278409       1641        1082
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25  (PR01)   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
total        2    118.54     173.84        350    2278409       1641        1082
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25  (PR01)   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  T_WW_TRC_ASSORT_UL_WAU (cr=2278409 pr=350 pw=0 time=173827787 us)
   1082   NESTED LOOPS ANTI (cr=2278319 pr=350 pw=0 time=136067805 us)
   3015    TABLE ACCESS BY INDEX ROWID T_WW_TRC_ASSORT_UL_WAU (cr=515 pr=0 pw=0 time=27217 us)
   3015     INDEX RANGE SCAN WAU_06_MB_B (cr=82 pr=0 pw=0 time=3089 us)(object id 251539)
   1933    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2277804 pr=350 pw=0 time=173624920 us)
   1933     TABLE ACCESS BY LOCAL INDEX ROWID T_FG_TRC_ASSORT_CDE_TAC PARTITION: KEY KEY (cr=2277804
 pr=350 pw=0 time=173587629 us)
88881189      INDEX RANGE SCAN ORA_TEST3 PARTITION: KEY KEY (cr=246329 pr=118 pw=0 time=45582 us)(ob
ject id 3163303)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  UPDATE STATEMENT   MODE: ALL_ROWS
      0   UPDATE OF 'T_WW_TRC_ASSORT_UL_WAU'
   1082    NESTED LOOPS (ANTI)
   3015     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                'T_WW_TRC_ASSORT_UL_WAU' (TABLE (TEMP))
   3015      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'WAU_06_MB_B'
                 (INDEX)
   1933     PARTITION LIST (SINGLE) PARTITION:KEYKEY
   1933      INDEX   MODE: ANALYZED (RANGE SCAN) OF
                 'T_FG_TRC_ASSORT_CDE__IDX_001' (INDEX) PARTITION:KEYKEY
 
********************************************************************************

**orafrance** · 13/08/2007, 16h23

mais c'est pas du tout les mêmes plans

ça correspond à quoi ces 2 statments ?

**ZERS** · 13/08/2007, 16h27

Envoyé par orafrance

mais c'est pas du tout les mêmes plans

ça correspond à quoi ces 2 statments ?

Argh je viens de m'apercevoir que j'ai copié un peu trop de trucs.
Je viens de remettre à jour les infos dans mon post précédent

**orafrance** · 13/08/2007, 16h34

Ce serait bien que tu donnes le tkprof des 2 situations : SQL vs PL/SQL

**ZERS** · 13/08/2007, 16h41

Envoyé par orafrance

Ce serait bien que tu donnes le tkprof des 2 situations : SQL vs PL/SQL

Ah ben ca j'ai pas. Mais on a l'impression que les deux plans ne sont pas les mêmes.

Y'a un paramétre qui permet de forcer l'exécution du plan de telle sorte que le PL/SQL utilise le même que SQL ?

**orafrance** · 13/08/2007, 16h42

je ne vois qu'un seul plan d'exécution moi

une trace en level 8 serait idéale

http://orafrance.developpez.com/dbahelp/#L3.1

**ZERS** · 13/08/2007, 16h53

Envoyé par orafrance

je ne vois qu'un seul plan d'exécution moi

une trace en level 8 serait idéale

http://orafrance.developpez.com/dbahelp/#L3.1

OK.
On va programmer cela pour cette nuit car nous travaillons en // run, I.E. les traitements sur la certif (en 10gR2) tournent en // de la prod (en 9i) et bien sur la nuit

**ZERS** · 13/08/2007, 20h02

Bon, en faisant des recherches voici exactement où se situe le soucis. C'est bien plus sioux que cela

Voici la requete qui va très vite en 9i :

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
 
UPDATE  T_WW_TRC_ASSORT_UL_WAU WAU
         SET WAU.WAU_IND_TRT = 1
       WHERE NOT EXISTS
       (SELECT 1
        FROM T_FG_TRC_ASSORT_CDE_TAC TAC
        WHERE TAC.TAC_COD_MAGASIN = 1625 
        AND TAC.TAC_COD_FOUR = WAU.WAU_COD_FOUR
        AND TAC.TAC_COD_FILIERE = WAU.WAU_COD_FILIERE
        AND TAC.TAC_COD_RACINE = WAU.WAU_COD_RACINE
        AND TAC.TAC_COD_VL = WAU.WAU_COD_VL
        AND TAC.TAC_COD_UL = WAU.WAU_COD_UL
        AND TAC.TAC_COD_CONTRAT_COM = WAU.WAU_COD_CONTRAT_COM
        AND TAC.TAC_COD_VA = WAU.WAU_COD_VA
        AND WAU.WAU_STA_COMMANDABLE = TAC.TAC_STA_COMMANDABLE
        AND WAU.WAU_COD_RESEAU = TAC.TAC_COD_RESEAU
        AND TAC.TAC_DAT_DEBUT = WAU.WAU_DAT_DEBUT
               )
         AND WAU.WAU_IND_TRT = 0
         AND WAU.WAU_COD_MAGASIN = 1625;

Pour rappel, celui-ci dure 3s en 9i, 6mns à volumétrie équivalente en 10gR2.

En modifiant légérement le code dans la sous-requete, en 10gR2, même perfs que la 9i (cf ajout en vert) :

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
UPDATE  T_WW_TRC_ASSORT_UL_WAU WAU
         SET WAU.WAU_IND_TRT = 1
       WHERE NOT EXISTS
       (SELECT 1
        FROM T_FG_TRC_ASSORT_CDE_TAC TAC,T_WW_TRC_ASSORT_UL_WAU WAU
        WHERE TAC.TAC_COD_MAGASIN = 1625  
        AND TAC.TAC_COD_FOUR = WAU.WAU_COD_FOUR
        AND TAC.TAC_COD_FILIERE = WAU.WAU_COD_FILIERE
        AND TAC.TAC_COD_RACINE = WAU.WAU_COD_RACINE
        AND TAC.TAC_COD_VL = WAU.WAU_COD_VL
        AND TAC.TAC_COD_UL = WAU.WAU_COD_UL
        AND TAC.TAC_COD_CONTRAT_COM = WAU.WAU_COD_CONTRAT_COM
        AND TAC.TAC_COD_VA = WAU.WAU_COD_VA
        AND WAU.WAU_STA_COMMANDABLE = TAC.TAC_STA_COMMANDABLE
        AND WAU.WAU_COD_RESEAU = TAC.TAC_COD_RESEAU
        AND TAC.TAC_DAT_DEBUT = WAU.WAU_DAT_DEBUT
               )
         AND WAU.WAU_IND_TRT = 0
         AND WAU.WAU_COD_MAGASIN = 1625;

Bref, en rappelant le nom de la table dans la clause FROM, ca booste. Très inquiétant car vu les millions de lignes de code de notre progiciel, il est certain que d'autres packages ont le même type de requêtes

Une idée de ce changement d'interpretation du code entre la 9i et la 10gR2 ?

Cordialement

**orafrance** · 14/08/2007, 09h32

faut voir les plans d'exécution avec ajout et sans ajout dans la clause FROM

**ZERS** · 14/08/2007, 16h15

Envoyé par orafrance

faut voir les plans d'exécution avec ajout et sans ajout dans la clause FROM

Re,

en fait ca devient plus sioux que cela, un DBA vient de nous rejoindre.
Le plan d'exécution montre qu'il n'utilise pas le bon index, ou plutôt qu'il existe un index qui rendrait la requete telle qu'elle est plus performante mais que le plan se refuse d'utiliser.
Malgré le lancement de la requete avec un HINT, NADA, il s'obstine à utiliser un plan qui n'est pas le meilleur. Notre DBA s'arrache les cheveux

**orafrance** · 14/08/2007, 16h29

essaye de passer l'optimiseur en FIRST_ROWS dans la session. Enfin, si le DBA s'arrache les cheveux... euh... tu peux essayer d'en trouver un autre plus compétent peut-être

[10gR2] Différence temps traitements même requete

SQL Oracle

Discussions similaires

Partager

Partager