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?