Préambule:
Il y a (au moins) un gros contre-sens dans cet article. Merci à Chris Antognini pour ses corrections. Je propose néanmoins de laisser cet article tel quel. Lisez bien les commentaires pour comprendre de quoi il parle. Cela me rappellera que je raconte souvent des c… et ça vous invitera à garder un esprit critique sur ce que j’écris parfois.

Si vous ne l’utilisez pas en précisant une table, le hint dynamic_sampling, comme le paramètre optimizer_dynamic_sampling ont 2 effets : d’abord l’aggressivité, c’est à dire le nombre de blocs explorés, avec laquelle l’échantillonnage dynamique des statistiques est réalisé lors du hard parse; ensuite, selon le niveau, les tables/prédicats sur lesquels l’échantillonnage est réalisé. Pour savoir avec précision et de manière directe les requêtes imbriquées lancées par l’optimiseur, activez la trace 10053.

Concernant les tables/prédicats évalués dans le cadre de l’échantillonnage dynamique, si les spécificités des niveaux 1 et 2 (les tables sans statistiques) et du niveau 4 (les tables dont 2 colonnes ou plus sont impliquées dans les prédicats de la requête) sont explicites, le niveau 3 est plus obscur… La documentation dit en effet, dans un anglais péremptoire : « All tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate ». Soyons honnête, cette phrase, n’est pas des plus limpide et pourtant je parle anglais.

Je vous propose d’illustrer à partir de quelques exemples ce que cela signifie en réalité. Cela dit, même si je comprends bien l’idée exprimée, écrire une phase en français qui ait un sens est un défi que je ne relèverai pas.

Le principe fondateur

Le principe de départ est assez simple et d’ailleurs très bien décrit dans cet article de Tom Kyte. Il s’agit de constater que dans certains cas, la cardinalité d’une étape du plan de la requête ne peut pas être déduit des statistiques collectées sur les colonnes en jeu; Oracle, dans ce cas, joue aux dés. L’exemple qui vient immédiatement à l’esprit est l’utilisation d’une fonction. En effet Oracle n’est pas capable (je sens la déception monter !), ayant les statistiques de distribution d’une colonne X d’estimer les statistiques de f(X)… Et cela, même si elle a le code de la fonction f ou que la fonction est built-in. Elle ne peut donc que « deviner » la sélectivité. En bien le niveau 3 permet justement de changer le comportement de l’optimiseur et de transformer une divination en estimation au moyen d’un échantillonnage dynamique. Je vais illustrer cet effet.

Un exemple

Pour illustrer le fonctionnement du niveau 3 du paramètre optimizer_dynamic_sampling, nous allons commencer par créer une table exemple dans le schéma SCOTT :

drop table scott.t purge;

create table scott.T(id varchar2(20),
                   num number,
                   text varchar2(1000)) tablespace users;

insert into SCOTT.T(id, num, text)
select 'Z'||'A'||dbms_random.string(null, 8),
       rownum,
       rpad('Z',1000,'Z')
  from dual connect by level <=100000;  commit;  exec dbms_stats.gather_table_stats('SCOTT', 'T', method_opt=>'for all columns size 254')

Dans un premier temps, regardons le plan et la cardinalité retournée par un plan avec une clause WHERE simple :

set pages 1000 lines 180

select count(text)
from scott.T
where num=1;

COUNT(TEXT)
-----------
        1

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ddt4d158qutbm, child number 0
-------------------------------------
select count(text)   from scott.T  where num=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |  1006 |  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter("NUM"=1)

Comme vous pouvez vous en rendre compte, l’estimation dans ce cas est cohérente avec la réalité puisque l’optimiseur estime qu’une seule ligne est retournée… mais ce cas est assez simple; essayez avec une fonction dans la clause WHERE :

select count(text)
from scott.T
where mod(num,2)=1;

COUNT(TEXT)
-----------
    50000

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5kyjptmkcv6g4, child number 0
-------------------------------------
select count(text)   from scott.T  where mod(num,2)=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1000 |   982K|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=1)

Comme vous vous en rendez compte, dans ce cas, la cardinalité retournée est très loin de la réalité. Si vous utilisez le hint dynamic_sampling, en revanche la cardinalité est estimée avec beaucoup plus de précision lors de la phase de hard parse :

select /*+dynamic_sampling(3)*/ count(text)
from scott.T
where mod(num,2)=1;

COUNT(TEXT)
-----------
    50000

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ac5kg2brvt5xh, child number 0
-------------------------------------
select /*+dynamic_sampling(3)*/ count(text)   from scott.T  where
mod(num,2)=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 49770 |    47M|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=1)

Note
-----
 - dynamic sampling used for this statement (level=3)

Tout semble logique…

Et maintenant, surprise !

Vous croyez avoir tout compris ? Essayez de comprendre ce qui suit :

select /*+dynamic_sampling(3)*/ count(text)
from scott.T
where
 mod(num,2)=3;

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bs94bgtcbvjvu, child number 0
-------------------------------------
select /*+dynamic_sampling(3)*/ count(text)   from scott.T  where
mod(num,2)=3

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1000 |   982K|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=3)

Et oui, la note relative à l’échantillonnage dynamique n’apparait pas ;-). La réponse est dans la 10053, comme vous le verrez ci-dessous :

alter session set events '10053 trace name context forever, level 1';

select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where mod(num,2)=3;

alter session set events '10053 trace name context off';

col tracefile format a100 new_value tracefile

select tracefile
from v$process p, v$session s
where p.addr=s.paddr
 and s.sid=sys_context('USERENV', 'SID');

TRACEFILE
---------------------------------------------------------------
/u01/app/oracle/diag/rdbms/black/BLACK/trace/BLACK_ora_2678.trc

!vi &&tracefile

*** 2010-04-13 20:26:10.964
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 3).

*** 2010-04-13 20:26:10.964
** Generated dynamic sampling query:
  query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN MOD("T"."NUM",2)=3 THEN 1 ELSE 0 END AS C2 FROM "SCOTT"."T" SAMPLE BLOCK (0.203988 , 1) SEED (1) "T") SAMPLESUB

*** 2010-04-13 20:26:10.965
** Executed dynamic sampling query:
  level : 3
  sample pct. : 0.203988
  actual sample size : 217
  filtered sample card. : 0
  orig. card. : 100000
  block cnt. table stat. : 15197
  block cnt. for sampling: 15197
  max. sample block cnt. : 32
  sample block cnt. : 31
  min. sel. est. : 0.01000000
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=34ctndsfp77ss) -----
select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where mod(num,2)=3
Table: T  Alias: T
  Card: Original: 100000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
Access Path: TableScan
  Cost:  4122.64  Resp: 4122.64  Degree: 0
    Cost_io: 4118.00  Cost_cpu: 150244524
    Resp_io: 4118.00  Resp_cpu: 150244524
Best:: AccessPath: TableScan
       Cost: 4122.64  Degree: 1  Resp: 4122.64  Card: 1000.00  Bytes: 0

***************************************

Quelques explication de ce qui précède :

• « actual sample size : 217 » indique que les 31 blocs échantillonnées représentent 217 lignes de la table
• « filtered sample card. : 0 » indique que des 217 lignes testée, 0 sont retournées par la condition « MOD(« T ». »NUM »,2)=3″ soit une sélectivité de 0/217=0
• « min. sel. est. : 0.01 » indique que pour être retenue, la sélectivité doit être de 1% des lignes soit, la table contenant 100,000 lignes, 1000 lignes
• « DS Failed » indique que l’évaluation du dynamic sampling ne peut pas être retenu; la sélectivité renvoyée étant inférieure à 1%, c’est la valeur « divinatoire » de 1% qui sera retenue soit 1000 lignes pour la cadinalité

On constate donc que dans ce cas, même si l’optimiseur utilise un échantillonnage dynamique, il ne considère pas le résultat associé. La raison apparaît clairement dans la trace : la sélectivité est inférieure à la sélectivité minimale choisie par l’optimiseur de 1%. Ce fonctionnement est un garde fou contre les erreurs de plans du à une sous-estimation par l’échantillonnage. Celui-ci vise, en effet, à favoriser la détection d’une sélectivité faible et pas l’inverse. C’est d’ailleurs pour cela que la note « dynamic sampling » n’apparait pas dans le plan. Même si celui-ci est calculé, n’est pas considéré. Vous noterez donc qu’on retrouve dans ce cas la même valeur de cardinalité que celle retournée par le plan sans utilisation de dynamic_sampling de notre premère requête avec « MOD(« T ». »NUM »,2)=1″.

Un autre cas…

Les cas où l’optimiseur est réduit à deviner la sélectivité ne se restreint pas à l’utilisation de fontions; commençons par un exemple avec une clause « id like ‘Z%' ». Vous constaterez que l’utilisation du dynamic_sampling n’a aucun effet. Vous pourrez le vérifier ci-dessous ou avec une trace 10053 (par vous même) :

set pages 1000 lines 130

select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where id like 'Z%';

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a98fydtxy86qn, child number 0
-------------------------------------
select /*+dynamic_sampling(3) test2*/ count(text)   from scott.T  where
id like 'Z%'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1012 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100K|    96M|  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter("ID" LIKE 'Z%')

En revanche dans le cas de la clause « id like ‘_A%' » qui ne peut pas être estimé, l’optimiseur utilisera bien le dynamic sampling :

set pages 1000 lines 130
select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where id like '_A%';

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gycbg9xcu4w1q, child number 0
-------------------------------------
select /*+dynamic_sampling(3) test2*/ count(text)   from scott.T  where
id like '_A%'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1012 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100K|    96M|  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
---------

 2 - filter(("ID" LIKE '_A%' AND "ID" IS NOT NULL))

Note
-----
 - dynamic sampling used for this statement (level=3)

Conclusion

Et voilà qui éclaire, je l’espère, une phrase un peu mystérieuse de la documentation. Mais au fait, si vous utilisez cette fonctionnalité, évitez si possible de la positionner au niveau de l’instance et surtout sur les environnements OLTP. Enfin, ce que j’en dis, c’est surtout si vous voulez favoriser la stabilité de vos applications. Si vous aimez pouvoir faire appel à des consultants qui vous raconte des choses qui ne vous serviront plus jamais… faites-le