Oracle 11g a introduit la notion de statistiques étendues. Il est ainsi possible de stocker des informations de distribution de résultats de fonctions ou de corrélation entre colonnes. L’idée sous-jascente est de fournir au CBO toujours plus d’informations statistiques. De cette manière, il peut les prendre en compte dans son modèle et améliorer ses choix.

Dans cet article, vous trouverez un exemple simple qui illustrer cette fonctionnalité mais également comment Oracle 11.2.0.2 offre une nouvelle fonction pour détecter automatiquement les statistiques étendues qu’il serait pertinent de collecter.

Statistiques étendues

Commençons par regarder comment Oracle estime la cardinalité lorsqu’une fonction est impliquée dans une clause where comme ci-dessous :

create or replace function f(x number) 
   return number deterministic
is begin
    return 1;
end;
/

create table x(col1 number, col2 varchar2(4000));

insert into x
  (select level, rpad(to_char(level),mod(level,1000), 'X') 
     from dual 
    connect by level <= 10000);

commit;

exec dbms_stats.gather_table_stats(user, 'X');

explain plan for 
  select count(*) from x where col1=1;

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

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 989401810

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| X    |     1 |
-------------------------------------------

explain plan for 
  select count(*) from x where f(col1)=1;

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

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 989401810

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| X    |   100 |
-------------------------------------------

Si vous comment la cardinalité est calculée dans la trace 10053, vous verrez que cette-ci est adaptée en fonction d’un coefficient arbitraire de 100 sur une base 11.2.0.2 :

Table: X  Alias: X
    Card: Original: 10000.000000  Rounded: 100  Computed: 100.00  Non Adjusted: 100.00

Seulement voilà, dans notre cas, cette estimation est très loin de la réalité ; Pour corriger cette estimation, on peut désormais créer une statistique étendue comme ci-dessous et collecter les statistques associées :

exec :name := -
    dbms_stats.CREATE_EXTENDED_STATS (user, 'X', '(f(col1))');

exec dbms_stats.gather_table_stats(user, 'X');

set head on
set lines 120
col "COLUMN" format a36

select table_name||'.'||column_name "COLUMN",
       num_distinct
  from user_tab_col_statistics 
 where table_name='X'
   and column_name=:name;
COLUMN                               NUM_DISTINCT
------------------------------------ ------------
X.SYS_STUYW7KRL1AV#$VQN3OO_R_DBC                1

explain plan for 
  select count(*) from x where f(col1)=1;

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

PLAN_TABLE_OUTPUT
--------------------------------------------
Plan hash value: 989401810

-------------------------------------------
| Id  | Operation    | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |   | 1 |
|   1 |  SORT AGGREGATE    |   | 1 |
|   2 |   TABLE ACCESS FULL| X   | 10000 |
-------------------------------------------

Maintenant qu’Oracle connait la distribution des données dans f(col1), l’estimation de la cardinalité ne souffre pas d’erreur; on retrouve l’utilisation de la statistique étendue dans la trace 10053 :

Access path analysis for X
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for X[X]
  No statistics type defined for function F
  No default cost defined for function F
 ***** Virtual column  Adjustment ******
 Column name       SYS_STUYW7KRL1AV#$VQN3OO_R_DBC
 cost_cpu 3050.00
 cost_io  179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
 ***** End virtual column  Adjustment ******
  Column (#3): SYS_STUYW7KRL1AV#$VQN3OO_R_DBC(
    AvgLen: 3 NDV: 1 Nulls: 0 Density: 0.000050 Min: 1 Max: 1
    Histogram: Freq  #Bkts: 1  UncompBkts: 10000  EndPtVals: 1
  Table: X  Alias: X
    Card: Original: 10000.000000  Rounded: 10000  Computed: 9999.50  Non Adjusted: 9999.50

Détection automatique des corrélations

Ce qu’Oracle appelle la détection des corrélations est en réalité une fonction de « monitoring » de l’utilisation de colonnes de manière corrélées qui suggère la collecte des statistiques étendues de type multi-colonnes sur ces colonnes. Mais avant d’utiliser cette fonction, vous allez recréer un nouveau schéma exemple avec des données corrélées :

drop function f;
drop table x purge;

create table x
  (col1 number,
   col2 number,
   col3 varchar(1000));

insert into x
  (select mod(level,10), mod(level,10)+1, rpad ( 'X', mod(level, 1000), 'Y')
     from dual 
    connect by level <= 10000);

commit;

exec dbms_stats.gather_table_stats( -
   user, 'X')

Nous allons maintenant lancer dbms_stats.seed_col_usage afin de monitorer l’activité pendant 2 minutes (120 secondes). Nous allons ensuite exécuter des requêtes utilisant simultanément plusieurs colonnes pour constater qu’Oracle détecte cette utilisation :

exec dbms_stats.seed_col_usage(null,null,120);

select count(*) 
  from X
 where col1=1 and col2=2;

COUNT(*)
--------
    1000

select col1, col2, count(*) 
  from X
 group by col1, col2
 order by 1,2;

COL1       COL2   COUNT(*)
---- ---------- ----------
   0          1       1000
   1          2       1000
   2          3       1000
   3          4       1000
   4          5       1000
   5          6       1000
   6          7       1000
   7          8       1000
   8          9       1000
   9         10       1000

set head off
set long 10000
set longchunksize 10000
select DBMS_STATS.REPORT_COL_USAGE(user, 'X') from dual;

LEGEND:
.......

EQ    : Used in single table EQuality predicate
RANGE    : Used in single table RANGE predicate
LIKE    : Used in single table LIKE predicate
NULL    : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER    : Used in single table FILTER predicate
JOIN    : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................


##################
#############################################################

COLUMN USAGE REPORT FOR SCOTT.X
...............................

1. COL1           : EQ
2. COL2           : EQ
3. (COL1, COL2)          : FILTER GROUP_BY
###############################################################################

set head on

Voilà, vous pouvez simplement créer les statistiques étendues en vous basant sur ce qu’Oracle a détecté grace à la fonction dbms_stats.create_extended_stats comme ci-dessous :

SQL> select dbms_stats.create_extended_stats(user, 'X') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'X')
--------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR SCOTT.X
......................

1. (COL1, COL2)          : SYS_STUFLHATC5RBD6JHJZWT$X2AAH created
###############################################################################

Vous pouvez également visualiser l’utilisation des colonnes avec les tables col_group_usage$ et col_usage$ :

select cols 
  from sys.col_group_usage$ g
 where obj# in (select object_id
                  from user_objects 
                 where object_name='X' 
                   and object_type='TABLE');

COLS
-------------
1,2

select INTCOL#, EQUALITY_PREDS, EQUIJOIN_PREDS, RANGE_PREDS, NULL_PREDS
  from sys.col_usage$
 where obj# in (select object_id
                  from user_objects 
            where object_name='X' 
              and object_type='TABLE');

INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS RANGE_PREDS NULL_PREDS
------- -------------- -------------- ----------- ----------
      1              4             0           0          0
      2              4              0           0          0
      4              2              0           0          0

Collectez ensuite les statistiques et vous constaterez qu’Oracle estime désormais le nombre de lignes retournées avec une bien meilleure précision ce qui est bien l’objectif des statistiques étendues :

exec dbms_stats.gather_table_stats( -
   user, 'X')

explain plan for 
  select /* CORRELATEDC */ count(*) from x 
   where (col1=1 and col2=2);

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

PLAN_TABLE_OUTPUT
--------------------------------------------
Plan hash value: 989401810

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| X    |  1000 |
-------------------------------------------

Pour l’instant, seules les utilisations de colonnes corrélées sont détectées et l’utilisation de fonctions ou d’opérateurs ne sont pas capturés par Oracle. On comprend bien toutefois l’intérêt de cette possibilité pour améliorer la précision des informations à disposition du CBO. Voilà, pour en savoir plus sur le sujet, jetez un oeil sur le blog de l’optimiseur Oracle.