SQL Adaptive Plan 12c (parallélisme)

Depuis la version 12.1.0.1, l’optimiseur Oracle est désormais capable de corriger le plan d’une requête SQL pendant son exécution.
Nous avions étudié dans un précédent article le comportement de l’optimiseur Oracle 12c lors d’une jointure dans un contexte de statistiques non représentatives des données.
Désormais, nous allons nous intéresser à son comportement lors de l’exécution d’une requête SQL activant le parallélisme.

Lorsque le parallélisme est activé, certaines opérations utiles à l’exécution de la requête SQL (tri, agrégation, jointure) nécessitent une distribution des données auprès des différents processus esclaves.
La méthode de distribution des données est définie selon le type d’opération demandée, le nombre de processus esclaves et bien entendu par le nombre de lignes à traiter.
Si l’optimiseur Oracle a une vision erronée des données, la méthode de distribution des données sera probablement non optimale ce qui influera inévitablement sur la charge des processus esclaves.
Certains processus esclaves seront certainement sous-utilisés alors que d’autres seront bien au contraire sur-chargés.
Oracle 12c a introduit une nouvelle méthode de distribution nommée Hybrid Hash.
Avec cette fonctionnalité, l’optimiseur Oracle peut différer la distribution des données au moment de l’exécution de l’opération (tri, jointures, etc), une fois qu’il dispose de suffisamment d’informations sur le nombre de lignes concernées par l’opération en question.
Une collecte de statistiques (statistics collector) est effectuée avant chaque opération réalisée par un processus esclave producteur.
Contrairement à l’adaptive join, cette nouvelle fonctionnalité de l’optimiseur est activée à chaque exécution de la requête SQL.
Désormais, penchons-nous sur une démonstration.

SQL> CONNECT TEST/TEST
SQL> CREATE TABLE t1 (
  id    NUMBER,
  name  VARCHAR2(10),
  function VARCHAR2(10),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);
SQL> CREATE INDEX t1_name ON t1(name);
SQL> CREATE SEQUENCE t1_seq;
SQL> INSERT /*+ APPEND */ INTO t1
SELECT t1_seq.nextval,
       'MARTIN',
       'DBA'
FROM   dual
CONNECT BY level <= 500000;
SQL> COMMIT;
SQL> CREATE TABLE t2 (
  id       NUMBER,
  t1_id  NUMBER,
  function VARCHAR2(10),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1(id)
);
SQL> CREATE INDEX t2_t1_fk_idx ON t2(t1_id);
SQL> CREATE SEQUENCE t2_seq;
SQL> INSERT /*+ APPEND */ INTO t2
SELECT t2_seq.nextval,
       TRUNC(DBMS_RANDOM.value(21,500000)),
       'DBA'
FROM   dual
CONNECT BY level <= 500000;
SQL> COMMIT;

Calculer les statistiques sur les 2 tables T1 et T2 :

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T2');
SQL> CONNECT / AS SYSDBA
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> CONNECT TEST/TEST
SQL> SELECT /*+ GATHER_PLAN_STATISTICS
           PARALLEL(16) */
t1.function, t2.function
FROM   t1, t2
WHERE  t1.id = t2.t1_id
  AND  t1.name = 'MARTIN';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  63g2u4jnduy9q, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS            PARALLEL(16) */
t1.function, t2.function FROM   t1, t2 WHERE  t1.id = t2.t1_id   AND
t1.name = 'MARTIN'
Plan hash value: 435755347
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |      1 |        |    500K|00:00:01.18 |      35 |       |       |          |         |
|   1 |  PX COORDINATOR                  |          |      1 |        |    500K|00:00:01.18 |      35 |       |       |          |         |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|*  3 |    HASH JOIN BUFFERED            |          |      0 |    500K|      0 |00:00:00.01 |       0 |    29M|    11M| 4628K (0)|         |
|   4 |     PX RECEIVE                   |          |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|   5 |      PX SEND HYBRID HASH         | :TQ10000 |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|   6 |       STATISTICS COLLECTOR       |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |         |
|   7 |        PX BLOCK ITERATOR         |          |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|*  8 |         TABLE ACCESS STORAGE FULL| T2       |      0 |    500K|      0 |00:00:00.01 |       0 |  1025K|  1025K|          |         |
|   9 |     PX RECEIVE                   |          |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|  10 |      PX SEND HYBRID HASH         | :TQ10001 |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|  11 |       PX BLOCK ITERATOR          |          |      0 |    500K|      0 |00:00:00.01 |       0 |       |       |          |         |
|* 12 |        TABLE ACCESS STORAGE FULL | T1       |      0 |    500K|      0 |00:00:00.01 |       0 |  1025K|  1025K| 1029K (0)|         |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"="T2"."T1_ID")
   8 - storage(:Z>=:Z AND :Z<=:Z)
  12 - storage(:Z>=:Z AND :Z<=:Z AND "T1"."NAME"='MARTIN')
       filter("T1"."NAME"='MARTIN')
Note
-----
   - Degree of Parallelism is 16 because of hint

En activant le parallélisme avec le hint PARALLEL, nous observons la nouvelle méthode de distribution des données via l'opération PX SEND HYBRID HASH ainsi que la collecte des statistiques réalisée par le processus esclave STATISTICS COLLECTOR.