Si vous avez lu la documentation de la base de données Oracle 11.1, vous connaissez la fonctionnalité dite « Intelligent » ou « Adaptive » Cursor Sharing. Selon cette documentation, Oracle peut désormais adapter son comportement et recalculer un plan d’exécution lorsqu’il le juge nécessaire. Cela permet d’éviter en partie les effets négatifs du bind peeking avec des plans de requêtes utilisant des variables bind. Vous trouverez, à ce sujet, un article très intéressant intitulé « Update on Adaptive Cursor Sharing » sur le blog consacré au CBO.

Quoiqu’il en soit et comme souvent avec la base de données, cette fonctionnalité est plus subtile qu’il n’y parait au premier abord. J’ai découvert un cas que vous devriez pourvoir reproduire où la requête est reparsée bien qu’elle soit strictement identique, dans le même environnement et n’utilise aucune variable bind.

Mon schéma de démonstration

Le fonctionnement de cette requête est peut-être lié aux caractériques de mon ordinateur portable; il n’est pas garanti que vous arriviez effectivement à le reproduire. J’utilise une base de données Oracle 11.2.0.1 Enterprise Edition sur Linux 32bits. Les paramètres à positionner en particulier sont les suivants:

• sga_target=260M
• pga_aggregate_target=180M
• filesystemio_options=setall

J’utilise un schéma DEMO ainsi qu’un ensemble de tables, données, statistiques et indices. Vous pouvez télécharger le script pour créer tout l’environnement depuis l’URL suivante.

La requête

Exécutez la requête ci-dessous; la première fois, vous devriez découvrir que le plan a une valeur de hash qui est la 1851413986:

-- Pour changer le comportement de cet exemple, positionnez ce paramètre à "none":
-- alter session set "_optimizer_extended_cursor_sharing_rel"=none;
-- Sinon (pour reproduire mon cas d'utilisation), laissez le paramètre à "simple" :
-- alter session set "_optimizer_extended_cursor_sharing_rel"=simple;

set timing on
select /* GG */ t.year_id, sum(f.metric1)
 from fact f, time t, dim2 d2, dim3 d3, dim4 d4
where f.time_id=t.time_id
  and f.dim2_id=d2.dim2_id
  and f.dim3_id1=d3.dim3_id1
  and f.dim3_id2=d3.dim3_id2
  and f.dim4_id=d4.dim4_id
    and d2.dim2_lib='Value 5'
    and d3.dim3_lib='Value (2,2)'
    and d4.dim4_l2='L2.1'
    and attr2='ZZ4'
    and t.time_id=trunc(t.time_id,'W')
  group by t.year_id
  order by t.year_id;

  YEAR_ID   SUM(F.METRIC1) 
--------- --------------
 01-JAN-09       38490

Elapsed: 00:00:06.10

select *
    from table(dbms_xplan.display_cursor(format=>'basic note'));

PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
select /* GG_2 */ t.year_id, sum(f.metric1)   from fact f, time t, dim2
d2, dim3 d3, dim4 d4  where f.time_id=t.time_id    and
f.dim2_id=d2.dim2_id and f.dim3_id1=d3.dim3_id1    and
f.dim3_id2=d3.dim3_id2   and f.dim4_id=d4.dim4_id    and
d2.dim2_lib='Value 5'  and d3.dim3_lib='Value (2,2)'   and
d4.dim4_l2='L2.1'    and attr2='ZZ4' and
t.time_id=trunc(t.time_id,'W') group by t.year_id  order by t.year_id

Plan hash value: 1851413986

-----------------------------------------------------------------
| Id  | Operation                               | Name          |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |
|   1 |  SORT GROUP BY                          |               |
|   2 |   NESTED LOOPS                          |               |
|   3 |    NESTED LOOPS                         |               |
|   4 |     HASH JOIN                           |               |
|   5 |      PART JOIN FILTER CREATE            | :BF0000       |
|   6 |       NESTED LOOPS                      |               |
|   7 |        NESTED LOOPS                     |               |
|   8 |         MERGE JOIN CARTESIAN            |               |
|   9 |          PARTITION RANGE ALL            |               |
|  10 |           TABLE ACCESS FULL             | TIME          |
|  11 |          BUFFER SORT                    |               |
|  12 |           TABLE ACCESS FULL             | DIM3          |
|  13 |         PARTITION RANGE ITERATOR        |               |
|  14 |          PARTITION HASH ALL             |               |
|  15 |           BITMAP CONVERSION TO ROWIDS   |               |
|  16 |            BITMAP AND                   |               |
|  17 |             BITMAP INDEX SINGLE VALUE   | FACT_TIME_IDX |
|  18 |             BITMAP INDEX SINGLE VALUE   | FACT_DIM3_IDX |
|  19 |        TABLE ACCESS BY LOCAL INDEX ROWID| FACT          |
|  20 |      PARTITION HASH JOIN-FILTER         |               |
|  21 |       TABLE ACCESS FULL                 | DIM2          |
|  22 |     INDEX UNIQUE SCAN                   | DIM4_PK       |
|  23 |    TABLE ACCESS BY INDEX ROWID          | DIM4          |
-----------------------------------------------------------------

Après avoir exécuté la requête une première fois, exécutez la une seconde fois avec exactement le même texte et dans la même session. Dans mon cas, le plan change et prend désormais la valeur de hash 1094455219. Bien que le plan soit meilleur dans ce second cas, je m’attendais a priori a ce que le premier plan soit réutilisé :

set timing on
select /* GG */ t.year_id, sum(f.metric1)
 from fact f, time t, dim2 d2, dim3 d3, dim4 d4
where f.time_id=t.time_id
  and f.dim2_id=d2.dim2_id
  and f.dim3_id1=d3.dim3_id1
  and f.dim3_id2=d3.dim3_id2
  and f.dim4_id=d4.dim4_id
    and d2.dim2_lib='Value 5'
    and d3.dim3_lib='Value (2,2)'
    and d4.dim4_l2='L2.1'
    and attr2='ZZ4'
    and t.time_id=trunc(t.time_id,'W')
  group by t.year_id
  order by t.year_id;

  YEAR_ID   SUM(F.METRIC1) 
--------- --------------
 01-JAN-09       38490

Elapsed: 00:00:00.18

select *
    from table(dbms_xplan.display_cursor(format=>'basic note'));

PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
select /* GG_2 */ t.year_id, sum(f.metric1)   from fact f, time t, dim2
d2, dim3 d3, dim4 d4  where f.time_id=t.time_id    and
f.dim2_id=d2.dim2_id and f.dim3_id1=d3.dim3_id1    and
f.dim3_id2=d3.dim3_id2   and f.dim4_id=d4.dim4_id    and
d2.dim2_lib='Value 5'  and d3.dim3_lib='Value (2,2)'   and
d4.dim4_l2='L2.1'    and attr2='ZZ4' and
t.time_id=trunc(t.time_id,'W') group by t.year_id  order by t.year_id

Plan hash value: 1094455219

--------------------------------------------------------------
| Id  | Operation                            | Name          |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |
|   1 |  SORT GROUP BY                       |               |
|   2 |   HASH JOIN                          |               |
|   3 |    NESTED LOOPS                      |               |
|   4 |     NESTED LOOPS                     |               |
|   5 |      MERGE JOIN CARTESIAN            |               |
|   6 |       MERGE JOIN CARTESIAN           |               |
|   7 |        PARTITION HASH ALL            |               |
|   8 |         TABLE ACCESS FULL            | DIM2          |
|   9 |        BUFFER SORT                   |               |
|  10 |         TABLE ACCESS FULL            | DIM3
  |
|  11 |       BUFFER SORT                    |               |
|  12 |        PARTITION RANGE ALL           |               |
|  13 |         TABLE ACCESS FULL            | TIME          |
|  14 |      PARTITION RANGE ITERATOR        |               |
|  15 |       PARTITION HASH ITERATOR        |               |
|  16 |        BITMAP CONVERSION TO ROWIDS   |               |
|  17 |         BITMAP AND                   |               |
|  18 |          BITMAP INDEX SINGLE VALUE   | FACT_TIME_IDX |
|  19 |          BITMAP INDEX SINGLE VALUE   | FACT_DIM3_IDX |
|  20 |          BITMAP INDEX SINGLE VALUE   | FACT_DIM2_IDX |
|  21 |     TABLE ACCESS BY LOCAL INDEX ROWID| FACT          |
|  22 |    TABLE ACCESS FULL                 | DIM4          |
--------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

Comme vous le découvrez à travers la note du plan, le calcul est désormais impacté par des informations d’exécution. Je n’ai rien trouvé sur cette note sur le site du support ou les forums ; En outre, si vous collectez la trace 10053, vous découvrirez, non seulement que le curseur est reparsé mais également que des hints opt_estimate sont utilisés pour corriger les estimations de cardinalité du plan; intéressant ?

Conclusion

D’après mes tests, la valeur du paramètre caché _optimizer_extended_cursor_sharing_rel impacte ce fonctionnement. Toutefois, les informations sur lesquelles s’appuie l’optimiseur pour décider qu’il est plus intelligent de changer le plan ne semblent pas évidentes à trouver. En plus, la colonne IS_SHAREABLE de la vue V$SQL suggère a priori que le plan pourrait être réutilisé. Si vous interrogez la vue V$SQL_SHARED_CURSOR lorsque les 2 curseurs enfants sont dans la shared pool, vous ne verrez aucune différence ! Je suis vraiment curieux de découvrir pourquoi et comment… Quelqu’un a-t-il une idée ?