Tuning du paramètre hash_area_size - Oracle 8i,évènement de diagnostic 10104

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 :

`text{hash_area_size} = 2 xx text{sort_area_size}`

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.000000

Plusieurs é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…