在extension上没有直方图可能会失效

                                   在extension上没有直方图可能会失效

    经常为了更好的评估cardinality,可能需要在多个栏位上同时收集统计，我们把这种统计
叫作extension统计，主要是为了准确评估那些有相关性的栏位.当收集了extension后，如果
extension上没有histogram可能会没有用

oracle版本:oracle 12C

新建一个表,

frankzou>create table t1
  2  as select mod(rownum,50) as a,mod(rownum,50) as b
  3  from dual
  4  connect by level<=1000;

Table created.

收集统计，可以看出初始栏位是没有直方图的
frankzou>exec dbms_stats.gather_table_stats('c##frank','T1');

PL/SQL procedure successfully completed

frankzou>col column_name format A50;
frankzou>col histogram format a20;
frankzou>select column_name,histogram from user_tab_col_statistics where table_name='T1';

COLUMN_NAME                                        HISTOGRAM
-------------------------------------------------- --------------------
A                                                  NONE
B                                                  NONE

先看看栏位上的值是多少
frankzou>select count(*) from t1 where a=1 and b=1;

  COUNT(*)
----------
        20

 

frankzou>set autotrace traceonly exp;
frankzou>select count(*) from t1 where a=1 and b=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

看一下执行计划,cardinality是1,难道是因为多个栏位没有收集extension的原因吗?接下来看看收集后的效果

frankzou>select dbms_stats.create_extended_stats(ownname=>'c##frank',tabname=>'T1',extension=>'(a,b)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'C##FRANK',TABNAME=>'T1',EXTENSION=>'(
--------------------------------------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X

frankzou>exec dbms_stats.gather_table_stats('c##frank','T1');

PL/SQL procedure successfully completed.

frankzou>select column_name,histogram from user_tab_col_statistics where table_name='T1';

COLUMN_NAME                                        HISTOGRAM
-------------------------------------------------- --------------------
A                                                  FREQUENCY
B                                                  FREQUENCY
SYS_STUNA$6DVXJXTP05EH56DTIR0X                     NONE

 

收集了extension后，cardinality还是返回1,看来extension在这里是完全没有用了，究竟是什么原因呢?
无意中我又再次收集了一下统计，这下extension上也有histogram了.

frankzou>select count(*) from t1 where a=1 and b=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     6 |     3   (0)| 00:00:01 |

frankzou>exec dbms_stats.gather_table_stats('c##frank','T1');

PL/SQL procedure successfully completed.
frankzou>select column_name,histogram from user_tab_col_statistics where table_name='T1';

COLUMN_NAME                                        HISTOGRAM
-------------------------------------------------- --------------------
A                                                  FREQUENCY
B                                                  FREQUENCY
SYS_STUNA$6DVXJXTP05EH56DTIR0X                     FREQUENCY

这下返回的cardinality 是对的了.

frankzou>select count(*) from t1 where a=1 and b=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    20 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

上mos一查，果然有个相关的bug,bug号是Bug 6972291 Column group selectivity is not used when there is a histogram on one column
"_fix_control"="6972291:on" 参数可以控制，不过default是disable的,需要手动在会话或是系统层面改为enable.