Introduction
Il est parfois difficile d’obtenir les statistiques à propos des jointures par hachage (hash joins) avec Oracle. Toutefois pour investiguer une requête en particulier invoquant une jointure par hachage, l’évènement 10104 donne des informations précieuses.
Dans le contexte de cette documentation technique, l’instance Oracle est une version 8i CBO (Cost Based Optimizer ou mode Choose) et il ne s’agit pas d’une instance MTS (Multi threaded Server).
Le paramètre hash_area_size
Généralités sur le paramètre hash_area_size
Le paramètre hash_area_size exprimée en octets est une zone de mémoire
maximale en RAM qu’un process peut allouer pour opérer une jointure par
hachage.
Pour connaître la valeur courante du paramètre hash_area_size :
select name, value from v$parameter where name='hash_area_size';-------------- ------------- hash_area_size 2097152
Dans le cas ci-dessus : le paramètre hash_area_size est fixé à environ 2Mb.
Ce qui signifie qu’un process pourra allouer au maximum 2Mb de RAM pour
réaliser une jointure par hachage. Cette zone de mémoire est seulement allouée
en cas de besoin et relaxée à l’issue de la jointure par hachage.
Il s’agit d’un paramètre qui peut être indiqué dans le fichier d’initialisation de l’instance Oracle :
initOEMD1ORA.ora
#hash_area_size = 8388608 #SMALL (8M)
#hash_area_size = 15728640 #MEDIUM (15M)
#hash_area_size = 26214400 #LARGE (25M)Lorsque le paramètre hash_area_size est trop faible, la réalisation de la
jointure par hachage est réalisée avec l’écriture de blocs dans le tablespace
temporaire et non en mémoire, cette écriture dans le tablespace temporaire
dégrade les performances.
Valeur par défaut du paramètre hash_area_size
Par défaut, lorsque le paramètre hash_area_size n’est pas explicitement
donné dans le fichier d’initialisation de l’instance Oracle, la valeur est
fixée à deux fois la valeur du paramètre sort_area_size.
Paramètre hash_area_size non donné dans le fichier d’initialisation :
Exemple :
select name, value from v$parameter where name='sort_area_size' or name='hash_area_size';sort_area_size 1048576 hash_area_size 2097152
Activation des jointures par hachage
Les jointures par hachage sont mises en œuvre si le paramètre d’initialisation hash_join_enabled est à true.
select name, value from v$parameter where name='hash_join_enabled';----------------- ---- hash_join_enabled true
initOEMD1ORA.ora
...
hash_join_enabled = true
...Le paramètre hash_area_size et l’optimiseur en mode choose
Le paramètre hash_area_size est un paramètre qui gouverne l’optimiseur
statistique sur le décision de favoriser des jointures par hachage plutôt que
des jointures ''nested loops'' et ''sort merge table''. Les jointures par
hachage sont de manière générale meilleures que les jointures ''sort
merge''.
Pour les petites jointures par hachage, le paramètre hash_area_size suffit
largement pour que les jointures par hachage soient réalisées en mémoire.
Pour que les jointures par hachage soient possibles, la plus petite ligne
source doit avoir une taille inférieure à 75% de la zone mémoire hash_area_size
parce qu’il ne peut y avoir plus de 75% de la mémoire hash_area_size pouvant
être utilisée pour mettre en buffer les données de la ligne.
L’évènement 10104 : cas pratique
L’évènement 10104 peut être utilisé pour vérifier si le paramètre
hash_area_size est assez large pour une jointure par hachage particulière. Cet
évènement génère des statistiques détaillées sur une jointure par hachage dans
un fichier de trace localisé dans le répertoire donné par le paramètre de
configuration user_dump_dest.
Pour retrouver le répertoire où est généré le fichier de trace :
select name, value from v$parameter where name='user_dump_dest'/Software/oracle/Instances/OEMD1ORA/udump
Pour activer l’évènement 10104 au niveau de la session :
alter session set events '10104 trace name context forever';Dans le cas pratique une jointure par hachage est mise en œuvre (jointure par hachage confirmée par la trace du plan d’exécution).
alter session set events '10104 trace name context forever'; set autotrace on; select * from scott.emp, scott.dept where scott.dept.deptno=scott.emp.deptno;Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=700) 1 0 HASH JOIN (Cost=3 Card=14 Bytes=700) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=72) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448)
Le fichier de trace généré donne les informations ci-dessous.
Original memory: 2097152
Calculated length of build rows: 36
Memory after hash table overhead: 1379281
Calculated overhead for partitions and row/slot managers: 1180
Number of partitions: 8
Number of slots: 12
Cluster (slot) size: 106496
Block size: 8192
Minimum number of bytes per block: 8160
Multiblock IO: 13
Bit vector memory allocation: 104857
Per partition bit vector length: 8192
Maximum possible row length: 161
Estimated Cardinality: 2
Estimated Row Length (includes overhead): 36
Estimated Input Size: 72
# Immutable Flags:
*** HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of partitions: 8
Number of partitions that fit in memory: 8
Total number of rows in in-memory partitions: 4
(used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 79560
### Partition Distribution ###
Partition 0 rows: 1 clusters: 1 in-memory slots 1
Partition 1 rows: 0 clusters: 0 in-memory slots 0
Partition 2 rows: 1 clusters: 1 in-memory slots 1
Partition 3 rows: 0 clusters: 0 in-memory slots 0
Partition 4 rows: 0 clusters: 0 in-memory slots 0
Partition 5 rows: 1 clusters: 1 in-memory slots 1
Partition 6 rows: 0 clusters: 0 in-memory slots 0
Partition 7 rows: 1 clusters: 1 in-memory slots 1
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Revised number of hash buckets (after flushing): 4
Allocating new hash table.
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Requested size of hash table: 64
Actual size of hash table: 64
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of rows (may have changed): 4
Number of in-memory partitions (may have changed): 8
Final number of hash buckets: 8
Size of hash table: 64
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
# than the true number.
Number of buckets with 0 rows: 4
Number of buckets with 1 rows: 4
Number of buckets with 2 rows: 0
Number of buckets with 3 rows: 0
Number of buckets with 4 rows: 0
Number of buckets with 5 rows: 0
Number of buckets with 6 rows: 0
Number of buckets with 7 rows: 0
Number of buckets with 8 rows: 0
Number of buckets with 9 rows: 0
Number of buckets with between 10 and 19 rows: 0
Number of buckets with between 20 and 29 rows: 0
Number of buckets with between 30 and 39 rows: 0
Number of buckets with between 40 and 49 rows: 0
Number of buckets with between 50 and 59 rows: 0
Number of buckets with between 60 and 69 rows: 0
Number of buckets with between 70 and 79 rows: 0
Number of buckets with between 80 and 89 rows: 0
Number of buckets with between 90 and 99 rows: 0
Number of buckets with 100 or more rows: 0
### Hash table overall statistics ###
Total buckets: 8 Empty buckets: 4 Non-empty buckets: 4
Total number of rows: 4
Maximum number of rows in a bucket: 1
Average number of rows in non-empty buckets: 1.000000Plusieurs éléments du diagnostic 10104 indiquent si la valeur du paramètre
hash_area_size est trop faible, parmi ceux-ci :
- lorsque la valeur du paramètre "Estimated Input Size" est supérieure à la valeur du paramètre "Memory after hash table overhead".
- lorsque la valeur du paramètre "Number of partitions that fit in memory" est plus petite que la valeur du paramètre "Total number of partitions".
- lorsqu’il existe plus d’une phase dans le diagnostic : PHASE 1, PHASE 2, etc.