Discussion :

[Débéa]

Oracle se plante complètement. Il fait une hash join dont le résultat fera 2 Giga. Evidement il n'aura pas la place de la caser en mémoire. Donc il utilise la sort_area_size et le temporary tablespace. Resultat la requête dure 25 minutes. Si je fais un ALTER SESSION SET HASH_JOIN_ENABLED=FALSE; la requête met 10 secondes.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 6 1130
HASH JOIN 37 M 2G 890
TABLE ACCESS FULL XXXXX 1 K 51 K 12
TABLE ACCESS FULL YYYYYY 43 K 1 M 877
Existe-t-il un paramètre pour eviter à l'optimizer de faire du HASH JOIN quand il en a pas les moyens ?
Existe-t-il un HINT pour eviter le l'optimizer de faire du HASH JOIN ?

Mes paramètres init.ora sont :
SORT_AREA_SIZE = 10 Mo
HASH_JOIN_AREA_SIZE = 20 Mo
HASH_JOIN_ENABLED=TRUE

[aline]

1.Existe-t-il un paramètre pour eviter à l'optimizer de faire du HASH JOIN quand il en a pas les moyens ?
2.Existe-t-il un HINT pour eviter le l'optimizer de faire du HASH JOIN ?

1. Il existe pleins. de paramètres permettant d'agir sur ton lplan d'execution

en vrac:

optimizer_index_caching
optimizer_index_cost_adj
db_file_multibloc_read_count

2. Ama (tu ne nous donne pas d'informations très claires), Oracle fait un hash join à la place d'une nested loop.



dans ta requete,

Code :

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

1
2
3
4
5
6
 
select  
                /*+use_nl(a,b)*/  
                aaaaaaaaaa,bbbbbbbbbbbb
from matable1 a,  matable2 b
where mes_conditions;

[Débéa]

Merci aline pour ta réponse. Il y a eu un reboot du serveur ce matin et le plan d'execution est redevenu normal.

A ton avis pourquoi l'optimiser decide de faire une hash_join alors qu'il en a pas les moyens ? N'aurais je pas un mauvais paramètrage mémoire ?

[aline]

Je ne sait pas.

Tu ne nous donne pas le bon plan d'execution!
donc, il est dur de comprendre ce qui se passe maintenant.

Petite question:
toutes tes tables et tes indexes ont ils étés analysés récement?

[Débéa]

Comme je suis encore emmerdé, je reviens à la charge.

Voici la requête et le plan d'execution foireux (environ 10 minutes):

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
SQL> EXPLAIN PLAN FOR
  2  SELECT
  3         DISTINCT b.im_iasclcus, a.im_iclean, c.artlibl, (SELECT parlibc
  4              FROM pardgene
  5              WHERE parctab = 806
  6                AND parcent = c.artuuvc
  7                AND parnmag = b.im_iascla), a.im_iclcnuf, a.im_icldept, d.foulibl,
  8         a.im_iclsitli, a.im_iclqtec, TO_CHAR(a.im_icldliv, 'DD/MM/YY'),
  9         b.im_iasccli, (SELECT parlibc
 10              FROM pardgene
 11              WHERE parctab = 452
 12                AND parcent = a.im_iclstat
 13                AND parnmag = 10), NULL, '' || a.ROWID
 14      FROM im_intcliv a, im_intasai b, artdgene c, foudgene d
 15      WHERE a.im_iclseq = b.im_iasid
 16        AND a.im_iclean = c.artean13
 17        AND a.im_iclcnuf = d.foucnuf
 18        AND a.im_icldept = d.foudept
 19        AND b.im_iassoc = d.founsoc
 20        AND b.im_iascla = d.founmag
 21        AND 1 = 1
      AND 1 = 1
 22   23        AND 1 = 1
 24        AND 1 = 1
 25        AND 1 = 1
 26        AND 1 = 1
 27        AND a.im_iclsitli = :1
 28        AND b.im_iassite IN (SELECT grsnmag
 29                 FROM grslig
 30                 WHERE grsngr = :2)
 31        AND a.im_iclstat = 1
 32      ORDER BY 1
 33  ;

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
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |    11 |    1K|   1304 |       |       |
|  SORT UNIQUE              |          |    11 |    1K|   1281 |       |       |
|   NESTED LOOPS            |          |    11 |    1K|   1258 |       |       |
|    HASH JOIN              |          |    11 |    1K|   1214 |       |       |
|     TABLE ACCESS FULL     |IM_INTCLI |   552 |   25K|    306 |       |       |
|     NESTED LOOPS          |          |     5M|  401M|    890 |       |       |
|      HASH JOIN            |          |    47M|    2G|    890 |       |       |
|       TABLE ACCESS FULL   |FOUDGENE  |     1K|   56K|     12 |       |       |
|       TABLE ACCESS FULL   |IM_INTASA |    50K|    1M|    877 |       |       |
|      INDEX UNIQUE SCAN    |GRSLIG_1  |    20 |  180 |        |       |       |
|    TABLE ACCESS BY INDEX R|ARTDGENE  |    30K|    1M|      4 |       |       |
|     INDEX RANGE SCAN      |ARTDGENE_ |    30K|      |      2 |       |       |
--------------------------------------------------------------------------------

C'est la hash join entre FOUDGENE et IM_INTASA qui se fait dans le temp qui fou la merde

si j'ajoute /*+ USE_NL(b,grslig_1)*/ (moins d'1 seconde):

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
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |    11 |    1K|   1304 |       |       |
|  SORT UNIQUE              |          |    11 |    1K|   1281 |       |       |
|   NESTED LOOPS            |          |    11 |    1K|   1258 |       |       |
|    HASH JOIN              |          |    11 |    1K|   1214 |       |       |
|     TABLE ACCESS FULL     |IM_INTCLI |   552 |   25K|    306 |       |       |
|     HASH JOIN             |          |     5M|  401M|    890 |       |       |
|      TABLE ACCESS FULL    |FOUDGENE  |     1K|   56K|     12 |       |       |
|      NESTED LOOPS         |          |     6K|  254K|    877 |       |       |
|       TABLE ACCESS FULL   |IM_INTASA |    50K|    1M|    877 |       |       |
|       INDEX UNIQUE SCAN   |GRSLIG_1  |    20 |  180 |        |       |       |
|    TABLE ACCESS BY INDEX R|ARTDGENE  |    30K|    1M|      4 |       |       |
|     INDEX RANGE SCAN      |ARTDGENE_ |    30K|      |      2 |       |       |
--------------------------------------------------------------------------------

Pourquoi l'optimiseur se plante à ce point ?

[Jaouad]

Bonjour ,

Comme je suis encore emmerdé,

Ensuite l'opitmiseur oracle est assez opaque en mode choose .
La jointure Hash join est une jointure optimise lorsqu'elle va liée deux tables A et B, A étant une grosse table et B une petite et lorsque il y a un nombre important de lignes à ramener.

Cette jointure est effectivement trés bien lorsqu'elle est dans ce cas mais peut être désastreuses.

Oracle analyse les tables et en fonction des stat calculés détermine ce plan. Il existe différentes maniéres de forcer le nested loops :

- Hint /*+ use_nl */
- Paramétre Init.ora
- alter session set HASH_JOIN_ENABLED=FALSE
- ou alors de coller des stats à la main pour influencer l'optimiseur Oracle .