Pourquoi Oracle ne prend pas en compte mon HINT ?

[StringBuilder]

* Bonjour, *

J'ai la vue suivante :

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
 
CREATE OR REPLACE FORCE VIEW WV_QIFM30_BIS (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20)
AS
  SELECT /*+ index(tie TIE_IDX1) */ cde.codsoc,
    cde.achvte,
    cde.typeve,
    DECODE( UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16), '000'
    ||SUBSTR(tie.sigtie,2,5)),
    cde.codeta,
    tie.codett,
    SUBSTR(DECODE(NVL(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, DECODE(SUBSTR(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8),
    /*liv.datliv*/
    (SELECT MAX(liv.datliv)
    FROM eve liv
    WHERE liv.codsoc = cde.codsoc
    AND liv.achvto   = cde.achvte
    AND liv.typevo   = cde.typeve
    AND liv.numevo   = cde.numeve
    ),
    cdp.codpro,
    ' ',
    pro.codzn10,
    cde.codctg,
    'XXX',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    /*liv.datliv*/
    (
    SELECT MAX(liv.datliv)
    FROM eve liv
    WHERE liv.codsoc = cde.codsoc
    AND liv.achvto   = cde.achvte
    AND liv.typevo   = cde.typeve
    AND liv.numevo   = cde.numeve
    ) ,
    ' ',
    ' ',
    ' ',
    ' ',
    cde.numeve,
    NVL(to_number(trim(evt.codzn5)), cdp.qtecde),
    0,0,0,
    0,0,0,0,0,
    pru.coefuv,
    0,0,0,0,
    0,0,0,0,0,
    0,0,0,0,0,
    0,0,0,0,0
  FROM eve cde
  INNER JOIN tie
  ON tie.codsoc  = cde.codsoc
  AND tie.typtie = 'CLI'
  AND tie.sigtie = cde.sigtie
  INNER JOIN evp cdp
  ON cdp.codsoc  = cde.codsoc
  AND cdp.achvte = cde.achvte
  AND cdp.typeve = cde.typeve
  AND cdp.numeve = cde.numeve
  INNER JOIN pro
  ON pro.codsoc  = cdp.codsoc
  AND pro.codpro = cdp.codpro
  INNER JOIN prl
  ON prl.codsoc  = pro.codsoc
  AND prl.codpro = pro.codpro
  AND prl.typtie = ' '
  AND prl.sigtie = ' '
  AND prl.codva1 = '00'
  AND prl.codva2 = SUBSTR(pro.codpro,8,2)
  AND prl.cntcod = ' '
  INNER JOIN pru
  ON pru.codsoc  = prl.codsoc
  AND pru.codpro = prl.codpro
  AND pru.coduni = prl.coduni1
  INNER JOIN evt
  ON evt.codsoc  = cdp.codsoc
  AND evt.achvte = cdp.achvte
  AND evt.typeve = cdp.typeve
  AND evt.numeve = cdp.numeve
  AND evt.numpos = cdp.numpos
  AND evt.numlig = 0
  AND evt.numspo = 0
  AND evt.numblo = 0
  LEFT OUTER JOIN prc
  ON prc.codsoc  = cde.codsoc
  AND prc.typtie = 'CLI'
  AND prc.sigfou = tie.sigtie
  AND prc.codpro = pro.codpro
  LEFT OUTER JOIN pro p2
  ON p2.codsoc          = prc.codsoc
  AND p2.codpro         = prc.codzn2
  WHERE cde.codctg NOT IN ('DE','GR','EC');

J'ai mis un HINT sur la table TIE, afin de forcer l'utilisation de l'index unique (CODSOC, TYPTIE, SIGTIE)

Logique, puisque c'est ça que j'utilise pour faire la jointure avec la table EVE...

Seulement, lorsque j'exécute la requête suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT lib1 ,lib2 ,lib3 ,lib4 ,lib5 ,lib6 ,lib7 ,lib8 ,lib9 ,lib10 ,lib11 ,lib12 ,lib13 ,lib14 ,lib15 ,lib16 ,lib17 ,lib18 ,lib19 ,lib20 ,dat1 ,dat2 ,dat3 ,dat4 ,dat5 ,num01 ,num02 ,num03 ,num04 ,num05 ,num06 ,num07 ,num08 ,num09 ,num10 ,val01 ,val02 ,val03 ,val04 ,val05 ,val06 ,val07 ,val08 ,val09 ,val10 ,val11 ,val12 ,val13 ,val14 ,val15 ,val16 ,val17 ,val18 ,val19 ,val20 ,codsoc
FROM wv_qifm30_bis
WHERE codsoc = 100
AND lib1 = 'V'
AND lib2 = 'CDE'
AND dat1 BETWEEN '20111221' AND '20120103'
AND lib8 LIKE '2%'
AND (lib4 = 'V' OR lib4 = 'S')
AND (lib5 = 'ME1' OR lib5 = 'ME2')
ORDER BY lib6 ASC ,lib3 ASC ,dat1 ASC
;

Si je demande le plan d'exécution à SQL Developper (F10) alors je vois qu'il désire utilise l'index TIE_IDX6 (CODSOC, P_TIEINTGR, TYPTIE) alors que je n'utilise même pas P_TIEINTGR).

Du coup, cette requête déjà particulièrement barbare dure plus de 30 minutes... Alors je désirerais qu'il utilise au moins les bons index, même si c'est loin d'être à cet endroit qu'il passe le plus de temps, ce serait toujours ça de pris...

* Merci *

[Waldar]

J'ai un doute sur l'hint placé directement dans la vue, c'est pas impossible du tout qu'Oracle l'ignore totalement.

Est-ce que l'index est utilisé avec cette syntaxe ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  SELECT /*+ index(wv_qifm30_bi.tie TIE_IDX1) */
         lib1 , lib2 , lib3 , lib4 , lib5 , lib6 , lib7 , lib8 , lib9 , lib10
       , lib11, lib12, lib13, lib14, lib15, lib16, lib17, lib18, lib19, lib20
       , dat1 , dat2 , dat3 , dat4 , dat5 
       , num01, num02, num03, num04, num05, num06, num07, num08, num09, num10
       , val01, val02, val03, val04, val05, val06, val07, val08, val09, val10
       , val11, val12, val13, val14, val15, val16, val17, val18, val19, val20
       , codsoc
    FROM wv_qifm30_bis
   WHERE codsoc = 100
     AND lib1 = 'V'
     AND lib2 = 'CDE'
     AND dat1 BETWEEN date '2011-12-21' AND date '2012-01-03'
     AND lib8 LIKE '2%'
     AND lib4 IN ('S', 'V')
     AND lib5 IN ('ME1', 'ME2')
ORDER BY lib6 ASC ,lib3 ASC ,dat1 ASC;

Cela dit si Oracle ne l'utilise pas, c'est qu'il le juge moins pertinent que l'autre index.

[skuatamad]

16.3 Using Hints with Views

[StringBuilder]

Hmpf

C'est mort, la requête est générée par un ERP, je ne peux pas aller mettre un HINT dans la requête, je n'ai accès qu'à la vue...

Vivement qu'on change de version, on aura une version plus récente d'Oracle, avec peut-être moins de bugs dans l'optimiseur...

Parce que là, le coup du TIE_IDX6, faudra m'expliquer quelques années avant que je comprenne...

[StringBuilder]

Envoyé par Waldar

Cela dit si Oracle ne l'utilise pas, c'est qu'il le juge moins pertinent que l'autre index.

La méthode utilisée par Oracle pour choisir un index, c'est Random() non ?

Parce que là, sur les 10 index présents sur TIE, il a clairement choisi LE SEUL qui n'a aucune chance d'être intéressant.

En effet, je n'utilise pas P_TIEINTGR dans la vue.
Alors que l'ensemble des autres indexes portent sur un plus grand nombre de champs utilisés dans la vue.

A noter que le IDX1, qui est la clé, est de toute façon mon critère de jointure :

Code :

1
2
3
4
INNER JOIN tie
  ON tie.codsoc  = cde.codsoc
  AND tie.typtie = 'CLI'
  AND tie.sigtie = cde.sigtie

Vu que ces champs qui composent la clé primaire sont :
- UNIQUE
- Organisés en cluster
- Immuables

Les stats pour de l'ensemble des index sont recalculées en même temps.

Il n'y a donc aucune chance qu'un autre index soit plus pertinent... A part si Oracle utilise Random() pour choisir...

[skuatamad]

Quel est l'intérêt d'avoir 2 fois :

Code :

1
2
3
4
5
6
7
8
/*liv.datliv*/
    (SELECT MAX(liv.datliv)
    FROM eve liv
    WHERE liv.codsoc = cde.codsoc
    AND liv.achvto   = cde.achvte
    AND liv.typevo   = cde.typeve
    AND liv.numevo   = cde.numeve
    )

N'est il pas possible de remplacer la sous requête par un max analytique ?

Code :

max(cde.datliv) over (partition BY cde.codsoc,cde.achvte,cde.typeve,cde.numeve) AS max_datliv

[Waldar]

Envoyé par StringBuilder

La méthode utilisée par Oracle pour choisir un index, c'est Random() non ?

Parce que là, sur les 10 index présents sur TIE, il a clairement choisi LE SEUL qui n'a aucune chance d'être intéressant.

Oracle s'appuie sur les statistiques récoltées pour définir ses méthodes d'accès.
Si vous êtes en 10g+, le CBO est excellent : les requêtes à aiguiller avec un HINT sont rares.

Ce n'est pas parce que vous avez un index qu'il va être utilisé, un exemple simple :

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
CREATE TABLE t1 (c1  int PRIMARY KEY, c2 int NOT NULL);
-- Table created.
 
INSERT /*+ append */ INTO t1 (c1, c2)
SELECT level, 1
  FROM dual
connect BY level <= 1e5;
-- 100000 rows created.
 
commit;
-- Commit complete.
 
CREATE INDEX i_t1 ON t1 (c2);
-- Index created.
 
begin
  dbms_stats.gather_table_stats
  ( ownname          => user
  , tabname          => 'T1'
  , estimate_percent => 100
  , cascade          => true);
end;
/
-- PL/SQL procedure successfully completed.

Voila pour les données.
Une première requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT /*+ gather_plan_statistics */ count(*)
  FROM t1
 WHERE c2 = 1;
 
SET linesize 125;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
-------------------------------------------------------------------------------------                                        
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                        
-------------------------------------------------------------------------------------                                        
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      94 |                                        
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      94 |                                        
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |      94 |                                        
-------------------------------------------------------------------------------------                                        
 
Predicate Information (IDENTIFIED BY operation id):                                                                          
---------------------------------------------------                                                                          
 
   2 - filter("C2"=1)

La requête n'utilise pas l'index alors que pourtant je filtre pile sur la bonne colonne.

Avec une autre valeur :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT /*+ gather_plan_statistics */ count(*)
  FROM t1
 WHERE c2 = 2;
 
SET linesize 125;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
------------------------------------------------------------------------------------                                         
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                         
------------------------------------------------------------------------------------                                         
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |                                         
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       2 |                                         
|*  2 |   INDEX RANGE SCAN| I_T1 |      1 |      1 |      0 |00:00:00.01 |       2 |                                         
------------------------------------------------------------------------------------                                         
 
Predicate Information (IDENTIFIED BY operation id):                                                                          
---------------------------------------------------                                                                          
 
   2 - access("C2"=2)

[pacmann]

Salut,

Envoyé par StringBuilder

La méthode utilisée par Oracle pour choisir un index, c'est Random() non ?

Non, ce sont les statistiques qu'il a sur tes données, ainsi que ces heuristiques de calcul des coûts...

Envoyé par StringBuilder

Parce que là, sur les 10 index présents sur TIE, il a clairement choisi LE SEUL qui n'a aucune chance d'être intéressant.

... qui donc ne sont peut-être pas pertinentes dans ton cas.

Et ça, ça peut s'expliquer de plein de manières.
Il faut regarder le plan et les cardinalités qu'il estime, les comparer à ce qu'il y a en vrai, comprendre pourquoi il y a un écart...

[StringBuilder]

Envoyé par skuatamad

Quel est l'intérêt d'avoir 2 fois la même sous-requête ?

Aucun, c'est juste que le format de la vue est imposé par l'ERP, et qu'il a besoin d'avoir cette information dans deux champs différents.
Eventuellement, un sous-select global aurait permi d'utiliser un unique sous-select, et reprendre le même résultat dans deux alias. Ceci dit, je pense que quand Oracle voit deux fois le même sous-select, il ne l'exécute qu'une seule fois (j'espère pour lui tout du moins)

Envoyé par skuatamad

N'est il pas possible de remplacer la sous requête par un max analytique ?

Code :

max(cde.datliv) over (partition BY cde.codsoc,cde.achvte,cde.typeve,cde.numeve) AS max_datliv

Nope, si tu fais attention, lorsque je recherche le MAX, je ne cherche pas sur la même ligne, je fais une auto-jointure de sur codsoc, achvte, typeve, numeve vers codsoc, achvto, typevo, numevo
=> J'avais tenté de faire une jointure dans le FROM et un group by, mais je me suis heurté à d'autres problèmes qui m'ont obligés à utiliser une sous-requête.

[StringBuilder]

Envoyé par Waldar

Ce n'est pas parce que vous avez un index qu'il va être utilisé, un exemple simple

Je suis d'accord sur le principe, mais dans ce cas précis (plus de 400 000 lignes retournées par la vue) un index est bel et bien nécessaire.

Aussi, si je simplifie la requête (par exemple) :

Code :

1
2
3
4
5
6
7
 
SELECT *
FROM eve
INNER JOIN tie ON tie.codsoc = eve.codsoc AND tie.typtie = 'CLI' AND tie.sigtie = eve.sigtie
WHERE eve.codsoc = 100
AND eve.achvte = 'V'
AND eve.typeve = 'LIV'

J'ai bien mon index TIE_IDX1 qui est utilisé.
Je ne vois absolument pas, dans la vue, ce qui peut le pousser à tenter d'utiliser un autre index, surtout sur index qui porte sur des champs absents de la requête !

Enfin bon, de toute façon, le problème est clos, dans la mesure où je n'ai pas de moyen pour le forcer à utiliser un autre index (et que, comme je l'espère, on va migrer d'ici quelques mois, et donc laisser tomber le serveur actuel qui me semble particulièrement étrangement configuré -jamais vu des requêtes aussi lentes qu'ici, ni de plans d'exécution aussi tordus-)

[Mohamed.Houri]

Il peut y avoir plusieurs raisons qui font en sorte que l'index ne soit pas utilisé malgré l'avoir mis explicitement dans le hint. Au lieu de m'étaler la dessus, je vous invite à lire le lien suivant (ainsi que tous les liens qui y sont référencés)

http://hoopercharles.wordpress.com/2...an-index-hint/