USE_HINT vs USE_MERGE vs USE_NL

Version imprimable

12/12/2012, 22h38
tropiko

USE_HINT vs USE_MERGE vs USE_NL

Dans quel chacun de ces précieux hint est recommandé ?

La combinaison FULL + USE_HASH est elle la plus puissante dans la majorité des cas ?

Merci les hinteurs pour les directives ;)
merci
13/12/2012, 09h24
mnitu

Parfois NL est plus performant, parfois c’est Merge Join parfois c’est Hash Join. N’utilisez pas des hints que pour tester. De toute façon si vous ne comprenez pas les différences entre ces méthodes des jointures utiliser ces Hints ne vous apporte rien.
13/12/2012, 19h28
FSiebert

C'est évidemment parce qu'une méthode est meilleure que les autres qu'Oracle a mis au point un optimiseur très complexe pour déterminer la méthode à utiliser :aie:
13/12/2012, 22h20
tropiko

oui mais dans quel cas utiliser l'une ou l'autre pour optimiser une requête ?MERGE si predicat de type > ou < ?
Hash si équi-jointure ? càd = ?
13/12/2012, 23h06
FSiebert

Comme déjà dit, il n'y a pas de règle, ça dépend de plein de paramètres.
L'utilisation des hints n'est pas conseillé si vous ne maîtrisez pas le sujet, et, sans vouloir vous vexez, ça ne semble pas être le cas.

Il y a de grandes chances que l'optimiseur Oracle arrive à un meilleur résultat que vous (avec des statistiques à jour) ; et si problème de performance il y a, cherchez plutôt à optimiser votre requête qu'à influencer son plan d'exécution.
Si vous avez besoin de conseils plus approfondis sur un point précis, postez votre requête et son plan d'exécution.
17/12/2012, 20h40
pachot

Bonjour,

A noter que utiliser FULL et/ou USE_HASH sans LEADING et éventuellement SWAP_JOIN_INPUTS n'a souvent aucun sens. Car forcer un Hash Join sans dire dans quel sens on fait la jointure est plutôt aléatoire !

La bonne optimisation, c'est de fournir les bonnes stats à l'optimiseur et alors il choisira la bonne méthode. Ce n'est pas une question d'equi-join ou semi-join mais une question de nombre de lignes.

Cordialement,
Franck.
18/12/2012, 02h22
tropiko

MERGE JOIN CARTESIEN

Bonjour ,

Mon plan d’exécution 11g a changé par rapport à la 10g.

Un MERGE JOIN CARTESIEN est apparu et la requête a subi une régression.
Il s’agit SELECT avec jointure entre table T1,T2 et T3.

La table T2 lie les 2 tablesT1 et T3 mais Oracle commence par faire un MERGE JOIN CARTESIEN entre T1 et T3 !

La solution était de forcer l’ordre de jointure
SELECT /*+ ORDERED */ …
FROM T1,T2,T3
…
Et je retrouve en fin mon plan 10g (sans MERGE JOIN CARTESIEN).;)
Votre avis ? Puis-je faire autrement ?
18/12/2012, 09h00
mnitu

Oui, virez d’abord le hint de la requête. Assurez-vous que les statistiques sont correctement calculées. Investiguez pour comprendre la raison du changement du plan. Fournissez un jeu d’essai pour se faire aider.
18/12/2012, 14h13
pachot

Bonjour,

Citation:

Envoyé par tropiko

Un MERGE JOIN CARTESIEN est apparu et la requête a subi une régression

le Merge Join Cartesien est choisi lorsque les 2 tables on très peu de lignes. Il faudrait vérifier si c'est le cas, et sinon voir pourquoi l'estimation est mauvaise.
Cordialement,
Franck.

Pas forcement.
C’est plutôt de disposer des sources des données déjà triées ou facile à trier. Et pour les non-equi joins comme le hash join ne fonctionne pas ...

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
 
Connecté à :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> set linesize 132
SQL> show parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      all_rows
optimizer_secure_view_merging        boolean     TRUE
SQL> set autotrace traceonly explain
SQL> select *
  2    from big a
  3         Join
  4         big b
  5      On a.id = b.id
  6   Order By a.Id
  7  /
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2325133430
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  1606K|   278M|       | 62162   (1)| 00:12:26 |
|   1 |  MERGE JOIN                  |        |  1606K|   278M|       | 62162   (1)| 00:12:26 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG    |  1588K|   137M|       | 24042   (1)| 00:04:49 |
|   3 |    INDEX FULL SCAN           | PK_BIG |  1588K|       |       |  3422   (1)| 00:00:42 |
|*  4 |   SORT JOIN                  |        |  1588K|   137M|   381M| 38120   (1)| 00:07:38 |
|   5 |    TABLE ACCESS FULL         | BIG    |  1588K|   137M|       |  4674   (2)| 00:00:57 |
-----------------------------------------------------------------------------------------------

Bonjour,

Citation:

Envoyé par mnitu

C’est plutôt de disposer des sources des données déjà triées ou facile à trier.

Non, je ne parlais pas du 'sort-merge join' mais du 'merge join cartesian' dont parlais tropiko.

Exemple:

Code:

1
2
3
4
5
6
7
8
9
------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      2 |00:00:00.01 |       7 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN|      |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|   2 |   TABLE ACCESS FULL  | T2   |      1 |      2 |      2 |00:00:00.01 |       4 |       |       |          |
|   3 |   BUFFER SORT        |      |      2 |      1 |      2 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|*  4 |    TABLE ACCESS FULL | T1   |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------

T1 doit être petite car elle est bufferisée. T2 doit avoir peu de lignes car on va voir le buffer pour chaque ligne. C'est intéressant lorsque la jointure ramène presque toutes les lignes de 2 petites tables.

Cordialement,
Franck

Citation:

Envoyé par pachot

Code:

1
2
3
4
5
6
7
8
9
------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      2 |00:00:00.01 |       7 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN|      |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|   2 |   TABLE ACCESS FULL  | T2   |      1 |      2 |      2 |00:00:00.01 |       4 |       |       |          |
|   3 |   BUFFER SORT        |      |      2 |      1 |      2 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|*  4 |    TABLE ACCESS FULL | T1   |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------

Franck ,
Pourriez-vous nous communiquer la requête permettant de produire votre plan avec MERGE JOIN CARTESIAN ainsi que le nombre de lignes de chaque table T1 et T2 pour analyser.

Citation:

T1 doit être petite car elle est bufferisée. T2 doit avoir peu de lignes car on va voir le buffer pour chaque ligne.

Pourriez-vous expliquer davantage ce point sur la bufférisation ?

19/12/2012, 09h50
mnitu

Citation:

Envoyé par pachot

...
Non, je ne parlais pas du 'sort-merge join' mais du 'merge join cartesian' dont parlais tropiko.

Au temps pour moi!
19/12/2012, 09h57
mnitu

Cartesian Merge Join
19/12/2012, 10h25
pachot

Bonjour,
Dans cet exemple, T1 a 2 lignes et T2 a 1 ligne, et la requête est:

Code:

select * from T1 join T2 using(n)

J'avais commençé à tout mettre et expliquer le fonctionnement du merge join cartesian, mais mon explication ne collait pas avec le plan d'exécution affiché 8O.
Le prédicat de jointure n'est pas au bon endroit: pour moi il devrait être sur la ligne 3 et non 4. J'ai fait un autre test et il y a clairement un bug qui peut donner un mauvais résultat lorsque le merge join cartesian est utilisé. Je vais ouvrir un SR auprès du support.

Bref, le fonctionnement normal est:
1. FULL SCAN de T1
2. FULL SCAN de T2 et toutes les lignes vont dans un buffer (comme une table temporaire en mémoire)
3. Pour chaque ligne retournée par le FULL SCAN de T1 on lit tout le buffer pour trouver les lignes qui correspondent au prédicat de jointure.

Par rapport au HASH JOIN on évite la conso CPU de calcul de la fonction de hachage. Par rapport au MERGE JOIN on évite le tri. Par rapport au NESTED LOOP on évite d'aller voir plusieurs fois la table.
Mais ce n'est interessant que quand ce buffer est petit et qu'il est rapide à parcourir en entier, et que la jointure ramène la plupart des lignes (peu selective). Sinon il vaut mieux une methode d'accès qui accède directement aux lignes dont on a besoin (hash, sort ou index).

Cordialement,
Franck.