J’ai essayé de reproduire un problème qu’on rencontre avec 10g avec 11g il y a quelques jours. Il s’agit du type du problème que Riyaj décrit dans son post Multi-Column Correlation and Extended Stats in Oracle 11g. Surprise! Je n’ai pas réussi à reproduire le problème et pourtant, juste changer optimizer_features_enable='10.2.0.4' le fait apparaitre de nouveau; c’était comme si Oracle 11g pouvait détecter les colonnes corrélées sans aucune statistique étendue. Comment est-ce possible ?

Il suffit d’un simple exemple pour illustrer ce changement; créez juste une table comme ci-dessous:

create table x (
         a number, 
         b number, 
         c number);

begin
  for i in 1..1000 loop
    for j in 1..10 loop
      insert into x values (j,j,j);
    end loop;
  end loop;
end;
/

commit;

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

Regardez alors le plan d’une requête contenant une clause WHERE avec 2 colonnes corrélées:

explain plan for select c 
                   from x 
                  where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X  |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

L’optimiseur estime que je nombre de lignes retournées par la requête est de 100 ce qui revient à dire que les 2 conditions de la clause WHERE de la requête retournent 1000 lignes chacune et ne sont pas corrélées; c’est évidemment faux puisque dans notre table a=b. Alors quel est le changement avec 10g? Si vous créez un index multi-colonnes sur (A,B), l’estimation d’Oracle 11g change:

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

create index xidx on x(a,b);

explain plan for select /*+ no_index */ c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |  1000 |  9000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X  |  1000 |  9000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

Vous pouvez voir dans la trace 10053 que comme dans le cas de l’utilisation de statistiques étendues, il y a un facteur de correction qui corrige l’estimation du nombre de lignes retournées:

Access path analysis for X
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for X[X]
  ColGroup (#1, Index) XIDX
    Col#: 1 2    CorStregth: 10.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  Table: X  Alias: X
    Card: Original: 10000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  Access Path: TableScan
    Cost:  7.16  Resp: 7.16  Degree: 0
      Cost_io: 7.00  Cost_cpu: 2412429
      Resp_io: 7.00  Resp_cpu: 2412429
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  Access Path: index (AllEqRange)
    Index: XIDX
    resc_io: 23.00  resc_cpu: 554643
    ix_sel: 0.100000  ix_sel_with_filters: 0.100000
    Cost: 23.04  Resp: 23.04  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.16  Degree: 1  Resp: 7.16  Card: 1000.00  Bytes: 0

Ce n’est évidemment pas le cas avec 10g:

alter session set optimizer_features_enable='10.2.0.4';

explain plan for select /*+ no_index 10204 */ c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X  |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

alter session set optimizer_features_enable='11.1.0.7';

Évidemment, chaque changement à son coté obscur:

explain plan for select /*+ no_index */ c from x where a=1 and b=2;

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |  1000 |  9000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X  |  1000 |  9000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select count(*) 
  2    from dba_stat_extensions
  3   where table_name='X';

  COUNT(*)
----------
  0

dans le cas qui m’intéressait le changement pour 11g corrige mon problème et sans autre changement que la mise à jour en 11g. Quoiqu’il en soit, voila qui illustre que vos migrations vers 11g pourront également avoir certains effets de bord.