Oracle 11.2.0.2 et la détection automatique de colonnes corrélées

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.

1 réflexion sur “Oracle 11.2.0.2 et la détection automatique de colonnes corrélées”

  1. Je viens de vérifier dans le livre de Jonathan LEWIS et en fait lorsqu’on a un prédicat de type function(colx)=’SMITH’ alors le CBO applique toujours une séléctivité de 1%.
    Dans ton exemple de départ on a bien 10000*0.01=100

    Si la clause est de type not function(colx) = ‘SMITH’ alors la séléctivité appliquée est de 5%.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *