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 ?
2 réflexions sur “Oracle 11gR2 et un cas non documenté d'"Adaptive Cursor Sharing"”
La fonctionnalité Cardinality Feedback a été introduite en 11G et permt d’affiner les cardinalités suite à une première exécution
Elle est DISABLED si le curseur est marqué BIND AWARE
Elle peut être désactivée avec _optimizer_use_feedback = FALSE
Cool. Ca voudrais dire qu’ils ont pris conscience que le modèle de cout n’est pas forcément facile à plaquer dans la vrai vie et qu’une analyse des cardinalités postérieure à la requête peut apporter des infos intéressantes.
Les commentaires sont fermés.