Blog

le 29/01/2016
Laurent Berthier
Oracle, Oracle 12c, Page d'accueil

SQL Adaptive Plan 12c (jointures)

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. Cette optimisation est effectuée en cas de statistiques non représentatives des données, de jointures ou de prédicats complexes, etc …

Concrètement, l’optimiseur Oracle corrige le plan d’exécution si ce dernier constate une différence notable entre les statistiques estimées et les statistiques d’exécution au cours de la première exécution de la requête SQL.
Cette nouvelle fonctionnalité de l’optimiseur n’est activée en effet qu’à la première exécution de la requête SQL, c’est à dire tant que le curseur associé à la requête est absent de la shared pool.
Le SQL Plan Management apparu en 11g permet, quant à lui, d’améliorer le plan d’exécution d’une requête SQL déjà présent dans la shared pool via les SQL plan baseline.
Au cours de cet article, nous allons nous intéresser à l’adaptation d’un plan d’exécution dans le contexte d’une jointure non performante.

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 INTO t1 VALUES (t1_seq.nextval, 'MARTIN','DBA');
SQL> INSERT INTO t1 VALUES (t1_seq.nextval, 'DUPONT','DBA');
SQL> INSERT INTO t1 VALUES (t1_seq.nextval, 'DUVAL','DBA');
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
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(1,3)),
       'DBA'
FROM   dual
CONNECT BY level <= 20;
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

Effectuons une jointure entre les tables t1 et t2 en activant la récupération des statistiques d’exécution via le hint GATHER_PLAN_STATISTICS.

SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
t1.function, t2.function
FROM   t1, t2
WHERE  t1.id = t2.t1_id
  AND  t1.name = 'MARTIN';
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fh9s6rv0702w5, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ t1.function, t2.function FROM
t1, t2 WHERE  t1.id = t2.t1_id   AND  t1.name = 'MARTIN'
Plan hash value: 2169012942
--------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |              |      1 |        |      9 |00:00:00.01 |       6 |
|- *  1 |  HASH JOIN                              |              |      1 |     10 |      9 |00:00:00.01 |       6 |
|     2 |   NESTED LOOPS                          |              |      1 |     10 |      9 |00:00:00.01 |       6 |
|     3 |    NESTED LOOPS                         |              |      1 |     10 |      9 |00:00:00.01 |       4 |
|-    4 |     STATISTICS COLLECTOR                |              |      1 |        |      1 |00:00:00.01 |       2 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |      1 |      1 |00:00:00.01 |       2 |
|  *  6 |       INDEX RANGE SCAN                  | T1_NAME      |      1 |      1 |      1 |00:00:00.01 |       1 |
|  *  7 |     INDEX RANGE SCAN                    | T2_T1_FK_IDX |      1 |     10 |      9 |00:00:00.01 |       2 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2           |      9 |     10 |      9 |00:00:00.01 |       2 |
|-    9 |   TABLE ACCESS STORAGE FULL             | T2           |      0 |     10 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."T1_ID")
   6 - access("T1"."NAME"='MARTIN')
   7 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Prenant en compte les statistiques, l’optimiseur a effectué de manière optimale la jointure entre les tables t1 et t2 via un Nested Loops.
Dans le plan d’exécution, nous notons aussi les actions non effectuées, facilement repérables via le signe négatif (-).
Désormais, afin de mettre en difficulté l’optimiseur, nous allons modifier la distribution des données dans la table t1 en augmentant considérablement le nombre de lignes name=’MARTIN’.

SQL> INSERT /*+ APPEND */ INTO t1
SELECT t1_seq.nextval,
       'MARTIN',
       'DBA'
FROM   dual
CONNECT BY level >= 500000;
SQL> COMMIT;
SQL> INSERT /*+ APPEND */ INTO t2
SELECT t2_seq.nextval,
       TRUNC(DBMS_RANDOM.value(21,500000)),
       'DBA'
FROM   dual
CONNECT BY level <= 500000;
SQL> COMMIT;

Volontairement, nous ne recalculons pas les statistiques pour observer le nouveau comportement de l’optimiseur.

SQL> SELECT table_name, num_rows FROM dba_tables where table_name in ('T1','T2');
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T2                                     20
T1                                      3

ATTENTION : Ne pas oublier d’effectuer un flush de la shared_pool sinon l’optimiseur utilisera le curseur de la shared pool généré lors de la première exécution de la requête SQL

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
t1.function, t2.function
FROM   t1, t2
WHERE  t1.id = t2.t1_id
  AND  t1.name = 'MARTIN';
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fh9s6rv0702w5, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ t1.function, t2.function FROM
t1, t2 WHERE  t1.id = t2.t1_id   AND  t1.name = 'MARTIN'
Plan hash value: 482661387
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |              |      1 |        |    500K|00:00:02.34 |   37846 |   3772 |       |       |          |
|  *  1 |  HASH JOIN                              |              |      1 |     10 |    500K|00:00:02.34 |   37846 |   3772 |    28M|  9674K|   26M (0)|
|-    2 |   NESTED LOOPS                          |              |      1 |     10 |    500K|00:00:01.68 |    2760 |   2581 |       |       |          |
|-    3 |    NESTED LOOPS                         |              |      1 |     10 |    500K|00:00:01.35 |    2760 |   2581 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |              |      1 |        |    500K|00:00:01.01 |    2760 |   2581 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |      1 |    500K|00:00:00.72 |    2760 |   2581 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | T1_NAME      |      1 |      1 |    500K|00:00:00.22 |    1305 |   1128 |  1025K|  1025K|          |
|- *  7 |     INDEX RANGE SCAN                    | T2_T1_FK_IDX |      0 |     10 |      0 |00:00:00.01 |       0 |      0 |  1025K|  1025K|          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | T2           |      0 |     10 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|     9 |   TABLE ACCESS STORAGE FULL             | T2           |      1 |     10 |    500K|00:00:00.32 |   35086 |   1191 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."T1_ID")
   6 - access("T1"."NAME"='MARTIN')
   7 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Vous pouvez noter que cette fois l’optimiseur a modifié le type de jointure effectué entre les 2 tables (HASH JOIN).
L’optimiseur a juste comparé les cardinalités remontées lors de l’exécution de la requête SQL avec les statistiques récupérées lors du hard parse de la requête.