Oracle Database Discussions

Hi everyone,

I saw a note today on ML, which says that new 11g's feature - [Extended statistics|http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm#sthref1177] - is partially available in 10.2.0.4. See [Bug #5040753 Optimal index is not picked on simple query / Column group statistics|https://metalink2.oracle.com/metalink/plsql/f?p=130:14:3330964537507892972::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,5040753.8,1,0,1,helvetica] for details. The note suggests to turn on this feature using fixcontrol, but it does not say how actually to employ it. Because dbms_stats.create_extended_stats function is not available in 10.2.0.4, I'm curious how it is actually supposed to work in 10.2.0.4? I wrote a simple test:

drop table t cascade constraints purge;
create table t as select rownum id, mod(rownum, 100) x, mod(rownum, 100) y from dual connect by level <= 100000;
exec dbms_stats.gather_table_stats(user, 't');
explain plan for select * from t where x = :1 and y = :2;
select * from table(dbms_xplan.display);

disc
conn tim/t

drop table t cascade constraints purge;
create table t as select rownum id, mod(rownum, 100) x, mod(rownum, 100) y from dual connect by level <= 100000;
exec dbms_stats.gather_table_stats(user, 't');

alter session set "_fix_control"='5765456:7';

explain plan for select * from t where x = :1 and y = :2;
select * from table(dbms_xplan.display);

disc
conn tim/t

drop table t cascade constraints purge;
create table t as select rownum id, mod(rownum, 100) x, mod(rownum, 100) y from dual connect by level <= 100000;

alter session set "_fix_control"='5765456:7';
exec dbms_stats.gather_table_stats(user, 't');

explain plan for select * from t where x = :1 and y = :2;
select * from table(dbms_xplan.display);

In alll cases cardinality estimate was 10, as usually without extended statistics:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   100 |    53   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    10 |   100 |    53   (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("X"=TO_NUMBER(:1) AND "Y"=TO_NUMBER(:2))

10053 trace confirmed that fix is enabled and considered by CBO:

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  optimizer_secure_view_merging       = false
  _optimizer_connect_by_cost_based    = false
  _fix_control_key                    = -113
  *********************************
  Bug Fix Control Environment
  ***************************
...
  fix  5765456 = 7        *
...

But calculations were typical:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
    #Rows: 100000  #Blks:  220  AvgRowLen:  10.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): X(NUMBER)
    AvgLen: 3.00 NDV: 101 Nulls: 0 Density: 0.009901 Min: 0 Max: 99
  Column (#3): Y(NUMBER)
    AvgLen: 3.00 NDV: 101 Nulls: 0 Density: 0.009901 Min: 0 Max: 99
  Table:  T  Alias: T     
    Card: Original: 100000  Rounded: 10  Computed: 9.80  Non Adjusted: 9.80
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  53.19  Resp: 53.19  Degree: 0
      Cost_io: 50.00  Cost_cpu: 35715232
      Resp_io: 50.00  Resp_cpu: 35715232
  Best:: AccessPath: TableScan
         Cost: 53.19  Degree: 1  Resp: 53.19  Card: 9.80  Bytes: 0

Any thoughts?