[9i][Optimisation] Optimiser une requete

**Yorglaa** · 23/02/2006, 19h46

je suis d'accord qu'un Nested Loop entre 2 tables à fort volume de lignes extraites c'est pas terrible...

Essaye ceci :
le Select avec le WITH et Select de la clause with (FIRSTSTEP) tu lui met un hint, comme ceci

Code :

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

1
2
3
4
 With 
     FIRSTSTEP as ( 
 SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID 
...

EDIT : oui il faut impérativement recalculer les stats...

**nuke_y** · 24/02/2006, 11h24

> Yorglaa : les hash ne donnent rien de mieux, j'ai fait ça :

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
SELECT COUNT(1) FROM
(
With
    FIRSTSTEP as (
SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID , ...
FROM DWH."TRACK_EVENT" G_T0, 
DWH."SHIPMENT" G_T1
WHERE
...)
SELECT 
        FIRSTSTEP.PARTNER_ID,...
FROM
        FIRSTSTEP
        , DWH.TRACK_EVENT_TYPE 
WHERE
...

et ça :

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
SELECT COUNT(1) FROM
(
With
    FIRSTSTEP as (
SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID , ...
FROM DWH."TRACK_EVENT" G_T0, 
DWH."SHIPMENT" G_T1
WHERE
...)
SELECT /*+ use_hash (G_T1 G_T0) */
        FIRSTSTEP.PARTNER_ID,...
FROM
        FIRSTSTEP
        , DWH.TRACK_EVENT_TYPE 
WHERE
...

et ça ne donne rien de mieux puisqu'on passe à 1h au lieu de 10mn.

Je vais essayer ça :

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
SELECT COUNT(1) FROM
(
With
    FIRSTSTEP as (
SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID , ...
FROM DWH."TRACK_EVENT" G_T0, 
DWH."SHIPMENT" G_T1
WHERE
...)
SELECT /*+ use_hash (FIRSTSTEP DWH.TRACK_EVENT_TYPE) */
        FIRSTSTEP.PARTNER_ID,...
FROM
        FIRSTSTEP
        , DWH.TRACK_EVENT_TYPE 
WHERE
...

**Yorglaa** · 24/02/2006, 11h36

ok,
et qu'en est-il des stats ?
tu as pu les recalculer ?

et es-tu certain de que le(s) DISTINCT soi(en)t impératifs ? parce que ça, ça coûte pas mal...

**nuke_y** · 24/02/2006, 11h38

Malheureusement non, car je n'ai pas la main sur le serveur et je n'ai pas l'autorisation de lancer un calcul des statistiques

J'ai ouvert une demande pour qu'elles soient recalculées. Je crois qu'ils ont 5 jours pour y répondre

**Yorglaa** · 24/02/2006, 13h45

et pour le distinct ? tu n'as pas le bon résultat sans ça ?

et quel est maintenant ton plan d'exécution (avec les hints qui vont bien) ?

**nuke_y** · 24/02/2006, 15h43

Euh j'ai pas bien compris l'histoire du distinct là...

Sinon avec les hints ça donne ça :

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
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
SQL> set timing on
SQL> set autot trace exp stat
SQL>
SQL> SELECT COUNT(1) FROM
  2  (
  3  With
  4      FIRSTSTEP as (
  5  SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID ,
  6  G_T1.TRANSPORT_NO ,
  7  G_T1.SHIPMENT_NO ,
  8  G_T1.CD_EVENT_DELAY ,
  9  G_T1.CD_FAULT_DELAY ,
 10  G_T0.CD_EVENT
 11  FROM DWH."TRACK_EVENT" G_T0,
 12  DWH."SHIPMENT" G_T1
 13  WHERE
 14  ( G_T1.PARTNER_ID = G_T0.PARTNER_ID AND G_T1.TRANSPORT_NO = G_T0.TRANSPORT_NO  )
 15  AND G_T1.PARTNER_ID IN (130, 290)
 16  AND G_T1.INBOUND_REF_DATE >= '15/02/2006'
 17  AND G_T1.INBOUND_REF_DATE <= '23/02/2006'
 18  AND G_T1.OUTBOUND_REF_DATE >= '15/02/2006'
 19  AND G_T1.OUTBOUND_REF_DATE <= '23/02/2006'
 20  AND G_T0.DT_MVT >= '15/02/2006'
 21  AND G_T0.DT_MVT <= '23/02/2006'
 22  AND G_T1.CANCELED = 0
 23  AND G_T1.DELIVERED = 'EC'
 24  AND G_T1.CD_FAULT_DELAY IS NULL
 25  AND G_T1.CD_EVENT_DELAY IS NULL
 26  )
 27  SELECT
 28          FIRSTSTEP.PARTNER_ID
 29          , FIRSTSTEP.TRANSPORT_NO
 30          , FIRSTSTEP.SHIPMENT_NO
 31          , FIRSTSTEP.CD_EVENT_DELAY
 32          , FIRSTSTEP.CD_FAULT_DELAY
 33          , FIRSTSTEP.CD_EVENT
 34          , TRACK_EVENT_TYPE.T_CLOCK_STOPPER
 35  FROM
 36          FIRSTSTEP
 37          , DWH.TRACK_EVENT_TYPE
 38  WHERE
 39                  FIRSTSTEP.PARTNER_ID = DWH.TRACK_EVENT_TYPE.PARTNER_ID
 40          AND     FIRSTSTEP.CD_EVENT = DWH.TRACK_EVENT_TYPE.CD_TE
 41          AND     DWH.TRACK_EVENT_TYPE.RESPONSABILITE = 'C'
 42  );
 
EcoulÚ : 01 :04 :44.09
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=92 Card=1 Bytes=14)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=92 Card=1 Bytes=14)
   3    2       VIEW (Cost=92 Card=1 Bytes=103)
   4    3         SORT (UNIQUE) (Cost=92 Card=1 Bytes=129)
   5    4           HASH JOIN (Cost=85 Card=1 Bytes=129)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EVENT_TY
          PE' (Cost=2 Card=1 Bytes=40)
 
   7    6               NESTED LOOPS (Cost=3 Card=1 Bytes=102)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'SHIPMENT'
          (Cost=2 Card=1 Bytes=62)
 
   9    8                   INDEX (RANGE SCAN) OF 'I_SHIPMENT_DELIVERE
          D_OUTBOUND' (NON-UNIQUE) (Cost=4 Card=4)
 
  10    7                 INLIST ITERATOR
  11   10                   INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_TYPE_
          CD_TE' (UNIQUE) (Cost=1 Card=2)
 
  12    5             INLIST ITERATOR
  13   12               TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EVENT'
           (Cost=82 Card=63808 Bytes=1722816)
 
  14   13                 INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_CD_EVEN
          T' (NON-UNIQUE) (Cost=13 Card=1)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    4814478  consistent gets
    1769851  physical reads
      79136  redo size
        199  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

et

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
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
SQL> SELECT COUNT(1) FROM
  2  (
  3  With
  4      FIRSTSTEP as (
  5  SELECT /*+ use_hash (G_T1 G_T0) */ DISTINCT G_T1.PARTNER_ID ,
  6  G_T1.TRANSPORT_NO ,
  7  G_T1.SHIPMENT_NO ,
  8  G_T1.CD_EVENT_DELAY ,
  9  G_T1.CD_FAULT_DELAY ,
 10  G_T0.CD_EVENT
 11  FROM DWH."TRACK_EVENT" G_T0,
 12  DWH."SHIPMENT" G_T1
 13  WHERE
 14  ( G_T1.PARTNER_ID = G_T0.PARTNER_ID AND G_T1.TRANSPORT_NO = G_T0.TRANSPORT_NO  )
 15  AND G_T1.PARTNER_ID IN (130, 290)
 16  AND G_T1.INBOUND_REF_DATE >= '15/02/2006'
 17  AND G_T1.INBOUND_REF_DATE <= '23/02/2006'
 18  AND G_T1.OUTBOUND_REF_DATE >= '15/02/2006'
 19  AND G_T1.OUTBOUND_REF_DATE <= '23/02/2006'
 20  AND G_T0.DT_MVT >= '15/02/2006'
 21  AND G_T0.DT_MVT <= '23/02/2006'
 22  AND G_T1.CANCELED = 0
 23  AND G_T1.DELIVERED = 'EC'
 24  AND G_T1.CD_FAULT_DELAY IS NULL
 25  AND G_T1.CD_EVENT_DELAY IS NULL
 26  )
 27  SELECT /*+ use_hash (G_T1 G_T0) */
 28          FIRSTSTEP.PARTNER_ID
 29          , FIRSTSTEP.TRANSPORT_NO
 30          , FIRSTSTEP.SHIPMENT_NO
 31          , FIRSTSTEP.CD_EVENT_DELAY
 32          , FIRSTSTEP.CD_FAULT_DELAY
 33          , FIRSTSTEP.CD_EVENT
 34          , TRACK_EVENT_TYPE.T_CLOCK_STOPPER
 35  FROM
 36          FIRSTSTEP
 37          , DWH.TRACK_EVENT_TYPE
 38  WHERE
 39                  FIRSTSTEP.PARTNER_ID = DWH.TRACK_EVENT_TYPE.PARTNER_ID
 40          AND     FIRSTSTEP.CD_EVENT = DWH.TRACK_EVENT_TYPE.CD_TE
 41          AND     DWH.TRACK_EVENT_TYPE.RESPONSABILITE = 'C'
 42  );
 
EcoulÚ : 01 :12 :50.02
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=92 Card=1 Bytes=14)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=92 Card=1 Bytes=14)
   3    2       VIEW (Cost=92 Card=1 Bytes=103)
   4    3         SORT (UNIQUE) (Cost=92 Card=1 Bytes=129)
   5    4           HASH JOIN (Cost=85 Card=1 Bytes=129)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EVENT_TY
          PE' (Cost=2 Card=1 Bytes=40)
 
   7    6               NESTED LOOPS (Cost=3 Card=1 Bytes=102)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'SHIPMENT'
          (Cost=2 Card=1 Bytes=62)
 
   9    8                   INDEX (RANGE SCAN) OF 'I_SHIPMENT_DELIVERE
          D_OUTBOUND' (NON-UNIQUE) (Cost=4 Card=4)
 
  10    7                 INLIST ITERATOR
  11   10                   INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_TYPE_
          CD_TE' (UNIQUE) (Cost=1 Card=2)
 
  12    5             INLIST ITERATOR
  13   12               TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EVENT'
           (Cost=82 Card=63808 Bytes=1722816)
 
  14   13                 INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_CD_EVEN
          T' (NON-UNIQUE) (Cost=13 Card=1)
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    5189642  consistent gets
    1793565  physical reads
          0  redo size
        214  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Par contre je suis sur une autre solution qui a l'air de porter ses fruits. Je fais la jointure entre Grosse_Table et Enorme_Table (entre Shipment et Track_Event) en me basant non plus sur PARTNER_ID et TRANSPORT_NO mais sur une concaténation des 2, sur laquelle j'ai construit un index. Ca a l'air de carburer mais il faut que je teste en situation.

**aline** · 24/02/2006, 17h14

Bon, je pense qu'on est pas loin quand même.

On a diminué le nombre de blocs mémoires mais augmenté le nombre de blocs disques.

A mon avis, le meilleur plan est de selectionner la table SHIPMENT par les dates.
donc utiliser un index sur les dates
A priori ok avec I_SHIPMENT_DELIVERED_OUTBOUND.
Il serait quand même bien de vérifier si un index sur indbound,outbound ne serait pas plus efficace que outbound tout seul.

Ensuite acceder à ton énorme table avec un index sur G_T0.PARTNER_ID, G_T0.TRANSPORT_NO et ce (désolée Yorglaa, je ne suis pas d'accord avec toi!) avec un nested loop.

tu utilise dans tes requetes les indexs I_TRACK_EVENT_CD_EVENT et I_TRACK_EVENT_EVENTS. Utilisent-ils ces olonnes?
sinon(et c'est la que l'on doit gagner), indexes avec ces colonnes ou hinte dessus.

Et pour finir un join hash avec le petit TRACK_EVENT_TYPE (Par contre, j'imagine mal le sens idéal). Pour cela, j'essaierai ce hint (mais après la vérification sur ta table TRACK_EVENT). /*+use_hash(G_T2)*/.

et voilou!

**nuke_y** · 24/02/2006, 17h32

Ok Aline, je vais voir en test si un index Inbound, Outbound sur les Shipments améliore les choses.

Pour Track Event si je fais le lien entre Shipment et Track Event sur ID_SHIPMENT (indexé) ça devrait suffire non ?

**aline** · 24/02/2006, 17h48

Envoyé par nuke_y

Ok Aline, je vais voir en test si un index Inbound, Outbound sur les Shipments améliore les choses.

Pour Track Event si je fais le lien entre Shipment et Track Event sur ID_SHIPMENT (indexé) ça devrait suffire non ?

Il vaux mieux prendre le plus discriminant et c'est TRANSPORT_NO.
en plus, il apparait dans tees conditions de jointures. C'est pour cela qu'il faut ama impérativenment utiliser un index sur cette colonne. PARTNER_ID est lui optionnel.

**nuke_y** · 24/02/2006, 18h24

Certes mais ID_SHIPMENT = PARTNER_ID||'_'||TRANSPORT_NO alors il est encore plus discriminant, non ?

**aline** · 24/02/2006, 18h44

Envoyé par nuke_y

Certes mais ID_SHIPMENT = PARTNER_ID||'_'||TRANSPORT_NO alors il est encore plus discriminant, non ?

Alors déjà, Id_SHIPMENT a été mal choisi.

Je préfère et de loin

Code :

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

 ID_SHIPMENT = TRANSPORT_NO ||'_'||PARTNER_ID

sinon on parle presque de la même chose. ton index te sert surement comme clé primaire "ou presque" mais surement pas pour ta requête.
Oracle ne peux pas "voir" l'équivalence.
donc construit un index sur TRANSPORT_NO ou encore peux être mieux(a tester) sur TRANSPORT_NO,PARTNER_ID car c'est eux qui apparaissent dans ta clause where contrairement à ID_SHIPMENT.

**nuke_y** · 24/02/2006, 18h58

Euh non, je voulais dire que le champ ID_SHIPMENT contient PARTNER_ID||'_'||TRANSPORT_NO comme valeur et il existe dans SHIPMENT et dans TRACK_EVENT.

Donc faire la jointure entre SHIPMENT et TRACK_EVENT sur SHIPMENT.ID_SHIPMENT=TRACK_EVENT.ID_SHIPMENT me semble judicieux, non ?

Sinon je suis en train de voir pour utiliser INBOUND_REF_DATE et OUTBOUND_REF_DATE comme discriminant.

**aline** · 27/02/2006, 11h15

Envoyé par nuke_y

Euh non, je voulais dire que le champ ID_SHIPMENT contient PARTNER_ID||'_'||TRANSPORT_NO comme valeur et il existe dans SHIPMENT et dans TRACK_EVENT.

Donc faire la jointure entre SHIPMENT et TRACK_EVENT sur SHIPMENT.ID_SHIPMENT=TRACK_EVENT.ID_SHIPMENT me semble judicieux, non ?

.

alors, je ne comprend plus rien à ta modélisation..
Mais oui, en effet, si cette colonne existe sur les deux tables et est indexée, alors utilise cette condition de jointure! Tu devrais récupérer des temps assez bon.

**nuke_y** · 27/02/2006, 11h34

Il n'y a pas de modélisation. C'est un DWH, et en plus il évolue un peu dans tous les sens. Donc il y a redondance d'information dans les tables, et il y a réguliérement des colonnes qui contiennent 4 fois la même donnée mais présentée différement, ou une concaténation de 3 colonnes, etc.

Bon on a eu une GROSSE merde ce WE alors je ne peux pas continuer mon travail. Je laisse ça en stand by jusqu'à ce soir je pense. Merci à tous.

[9i][Optimisation] Optimiser une requete

Oracle

Discussions similaires

Partager

Partager