Sur un Hash Join en parallel query, lorsque la table hachée est petite, il est préférable parfois de l’envoyer entière à chacun des process parallèle qui effectuent la jointure.
En 12c il y a le Hybrid Hash qui va décider du broadcast au moment de l’exécution si la table petite.
Mais si le plan force un broadcast, il y a une nouvelle distribution possible en 12c: PQ_REPLICATE
Voici un plan d’exécution en 11g avec un DOP 8 avec les outlines suivants:
------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 4 (100)| | | | 14 |00:00:00.13 | 8 | | | | | 1 | PX COORDINATOR | | 1 | | | | | | 14 |00:00:00.13 | 8 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 14 | 4 (0)| Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | | |* 3 | HASH JOIN | | 8 | 14 | 4 (0)| Q1,01 | PCWP | | 14 |00:00:00.08 | 56 | 684K| 684K| 8/0/0| | 4 | PX RECEIVE | | 8 | 4 | 2 (0)| Q1,01 | PCWP | | 32 |00:00:00.06 | 0 | | | | | 5 | PX SEND BROADCAST | :TQ10000 | 0 | 4 | 2 (0)| Q1,00 | P->P | BROADCAST | 0 |00:00:00.01 | 0 | | | | | 6 | PX BLOCK ITERATOR | | 8 | 4 | 2 (0)| Q1,00 | PCWC | | 4 |00:00:00.01 | 12 | | | | |* 7 | TABLE ACCESS FULL| DEPT | 4 | 4 | 2 (0)| Q1,00 | PCWP | | 4 |00:00:00.01 | 12 | | | | | 8 | PX BLOCK ITERATOR | | 8 | 14 | 2 (0)| Q1,01 | PCWC | | 14 |00:00:00.02 | 56 | | | | |* 9 | TABLE ACCESS FULL | EMP | 14 | 14 | 2 (0)| Q1,01 | PCWP | | 14 |00:00:00.01 | 56 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ici DEPT (4 lignes) est lue en parallèle par les 8 process parallèles du set 1 (Q1,00) et toutes les lignes sont envoyées au set 2 (Q1,01) pour la jointure. On le voit aux 4×8=32 lignes reçues dans A-Rows.
En 12c, ‘PQ replicate small tables’ va économiser un set de serveurs parallèles en faisant lire l’ensemble de la table DEPT par chacun des process qui fait la jointure:
----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 4 (100)| | | | 14 |00:00:00.11 | 5 | | | | | 1 | PX COORDINATOR | | 1 | | | | | | 14 |00:00:00.11 | 5 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 14 | 4 (0)| Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | | |* 3 | HASH JOIN | | 8 | 14 | 4 (0)| Q1,00 | PCWP | | 14 |00:00:00.02 | 104 | 684K| 684K| 8/0/0| | 4 | TABLE ACCESS FULL | DEPT | 8 | 4 | 2 (0)| Q1,00 | PCWP | | 32 |00:00:00.01 | 48 | | | | | 5 | PX BLOCK ITERATOR | | 8 | 14 | 2 (0)| Q1,00 | PCWC | | 14 |00:00:00.01 | 56 | | | | |* 6 | TABLE ACCESS FULL| EMP | 14 | 14 | 2 (0)| Q1,00 | PCWP | | 14 |00:00:00.01 | 56 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Maintenant, DEPT est lue en entier (pas de block iterator) par chacun des process. On a un seul set de process parallèle (Q1,00) qui fait tout.
Ca fait un peu plus de blocs à lire, mais une économie de process et de messages parallel query. Efficace lorsque on sait que la table est petite. Et c’est en principe le cas si on choisit une distribution broadcast.
La demo montre aussi un plan possible en 11g où DEPT n’est pas lue par les process parallèle, mais est broadcastée quand même.
Le hint pour contrôler cette fonctionnalité: PQ_REPLICATE. A noter que l’on n’a plus de Adaptive Parallel Query Distribution (HYBRID HASH) dans ce cas.
Une précision: l’exemple porte sur un Hash Join, mais le PQ Replicate s’applique partout où il y a in PQ Broadcast, et c’est aussi le cas des autres méthodes de jointure.