Blog

Oracle optimizer_dynamic_sampling vs statistiques InnoDB

Les statistiques générées par InnoDB utilisent une méthode d’échantillonage comme on peut facilement le mettre en évidence en regardant le nombre de lignes d’une table dont la taille des lignes est mal distribuée… C’est un peu comme si Oracle faisait tout le temps du dynamic_sampling. Cette approche a ses avantages, sinon sur les temps de parsing au moins sur la maintenance de la base de données et, pourquoi pas, sur la pertinence des plans.

La comparaison entre Oracle et InnoDB a ses limites puisque les valeurs de l’échantillonnage sont très différentes comme on peut le voir en observant les valeurs des paramètres innodb_stats_sample_pages dans le cas de MySQL et de optimizer_dynamic_sampling dans le cas d’Oracle. En outre, la méthode d’échantillonnage qu’utilise Oracle est stable alors que celle d’InnoDB est aléatoire comme vous pouvez le voir ci-dessous…

Table exemple

Pour commencer on va créer et alimenter une table similaire sous MySQL et Oracle; le script insère des lignes de plus en plus grandes. En fait, rien ne garantit théoriquement dans ce scripts que les lignes de tailles similaires sont dans les mêmes blocs/pages mais il se trouve, du fait du fonctionnement d’Oracle et de InnoDB que c’est le cas :

for i in {1..10000}; do 
   j=`echo "$i/10" | bc`
   echo "insert into X values ($i, mod($i,128), rpad('X', $j,'X'))" 
       >> load.sql
done

Dans le cas de MySQL, l’ordre de création de la table est le suivant :

create table X
    (col1 int,
     col2 int,
     col3 varchar(1000)) engine=innodb;

source load.sql;

Dans le cas d’Oracle, on crée la table avec le script ci-dessous :

create table X 
    (col1 number,
     col2 number,
     col3 varchar2(1000));

@load
commit;

Statistiques dynamiques de InnoDB

Pour illustrer que les statistiques de InnoDB sont dynamiques, il suffit de les afficher quelques fois avec la commande show table status par exemple ou, comme ci-dessous, en interrogeant la table TABLES :

select table_name, engine, table_rows 
  from information_schema.TABLES 
 where table_name='X' 
   and table_schema='greg'G
*************************** 1. row ***************************
table_name: X
    engine: InnoDB
table_rows: 18325
1 row in set (0.00 sec)

select table_name, engine, table_rows 
  from information_schema.TABLES 
 where table_name='X' 
   and table_schema='greg'G
*************************** 1. row ***************************
table_name: X
    engine: InnoDB
table_rows: 7540
1 row in set (0.00 sec)

C’est toujours le cas, même si vous lancez la commande analyze table X

Oracle Dynamic Sampling

Dans le cas d’Oracle, on peut mettre également en évidence le fait que le calcul des statistiques est dynamique dans un plan en regardant les notes associées

SQL> explain plan for
  select * from X;

SQL> select * from table(dbms_xplan.display(format=>'basic +rows +note'));

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2941724873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      | 11113 |
|   1 |  TABLE ACCESS FULL| X    | 11113 |
------------------------------------------

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

On voit que comme InnoDB, l’erreur n’est pas négligeable (i.e. 11% dans ce cas), mais en plus, si vous regardez la cardinalité d’une autre requête, celle-ci est identique, 11113… Ca ne m’avait jamais étonné jusqu’à présent mais en y repensant :

SQL> explain plan for
  select /* test2 */ * from X;

SQL> select * from table(dbms_xplan.display(format=>'basic +rows +note'));

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2941724873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      | 11113 |
|   1 |  TABLE ACCESS FULL| X    | 11113 |
------------------------------------------

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

12 rows selected.

Evidemment, si on change le niveau d’échantillonnage, c’est une autre histoire :

SQL> alter session set optimizer_dynamic_sampling=6;

SQL> explain plan for
  select /* test3 */ * from X;

SQL> select * from table(dbms_xplan.display(format=>'basic +rows +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------
Plan hash value: 2941724873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      | 10674 |
|   1 |  TABLE ACCESS FULL| X    | 10674 |
------------------------------------------

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

La réponse à pourquoi le nombre de lignes retournées par l’échantillonnage reste identique est dans les traces 10046 ou 10053, c’est à dire dans la requête imbriquée qui calcule l’échantillon. Voici par exemple un extrait de la trace générée par la 10053 :

** 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 /*+ NO_PARALLEL("X") FULL("X") NO_PARALLEL_INDEX("X") */ 
               1 AS C1, 
               1 AS C2
          FROM "SYS"."X" SAMPLE BLOCK (8.467742 , 1) SEED (1) "X") SAMPLESUB

L’explication tient donc dans le mot clé SEED :

select count(*) from X SAMPLE BLOCK (8.467742 , 1) SEED (1);

COUNT(*)
--------
     941

ce qui ramené aux 744 blocs de la table vs 63 utilisés donne effectivement le nombre de lignes estimées de 11113… Et Oracle utilise toujours SEED(1) dans son échantillonnage !

Bug de documentation

Au passage, vous constaterez un bug de documentation (ou du produit ?) puisque, contrairement à ce qui est écrit à propos des niveaux de optimizer_dynamic_sampling sur 11.2.0.2/Linux x86 :

  • 5 prend également un échantillon de 63 blocs (comme 2,3 et 4)
  • 6 prend un échantillon de 127 blocs
  • …

Voci la trace 10053 qui l’illustre :

*** 2011-04-10 08:51:42.564
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 5).
** Dynamic sampling updated table stats.: blocks=744

*** 2011-04-10 08:51:42.564
** Generated dynamic sampling query:
    query text :
[...]
*** 2011-04-10 08:51:42.565
** Executed dynamic sampling query:
    level : 5
    sample pct. : 8.467742
    actual sample size : 941
    filtered sample card. : 941
    orig. card. : 82
    block cnt. table stat. : 744
    block cnt. for sampling: 744
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : -1.00000000
Thomas Bordet
Thomas Bordet
Voir tous ses articles
Articles récents

Mentions légales & Politique de confidentialité