[8i] [HP UNIX] HASH_JOIN

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

Citation:

---

Envoyé par Débéa

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:

---

1 2 3 4 5 6

select /*+use_nl(a,b)*/ aaaaaaaaaa,bbbbbbbbbbbb from matable1 a, matable2 b where mes_conditions;

---

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 ?

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?

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

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

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

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:

---

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

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

Code:

---

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 ?

Bonjour ,

Citation:

---

Comme je suis encore emmerdé,

---

:calim2:

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 .